Loading JSONL into BigQuery

Destination guide · updated 28 July 2026 · overview · spec · format comparison · examples · best practices · troubleshooting · FAQ · glossary

BigQuery reads newline-delimited JSON natively — it is the only JSON shape it accepts for loading, which is why its docs say NEWLINE_DELIMITED_JSON everywhere. This covers the load command, when to let it autodetect a schema, how nested data maps, and the handful of errors that actually stop a load.

The load command

# From a local file, with schema autodetection
bq load \
  --source_format=NEWLINE_DELIMITED_JSON \
  --autodetect \
  mydataset.mytable \
  ./data.jsonl

# From Cloud Storage, with an explicit schema (recommended for production)
bq load \
  --source_format=NEWLINE_DELIMITED_JSON \
  mydataset.mytable \
  gs://my-bucket/data/*.jsonl.gz \
  ./schema.json

# Append rather than replace, and tolerate a few bad rows
bq load \
  --source_format=NEWLINE_DELIMITED_JSON \
  --autodetect \
  --max_bad_records=100 \
  mydataset.mytable \
  gs://my-bucket/data/*.jsonl

Gzipped files work directly — no decompression step. The wildcard loads every matching object, which is why sharding a large dataset with the splitter and loading the shards in parallel is faster than one enormous file.

Autodetect or explicit schema?

--autodetect samples up to 500 rows and infers types. Convenient for exploration, risky in a pipeline, for three specific reasons:

• It samples. A field absent from the first 500 rows does not make it into the schema, and rows containing it later fail.
• It infers types from what it sees. A column that is integer in the sample and fractional later fails on load.
• It is not stable. Two loads of different files produce different schemas, so your table shape depends on load order.

For anything recurring, write the schema down. The fastest route: infer a JSON Schema from the whole file with our schema inferrer — it reads every record, not a sample — then translate it. Check the result against field coverage, which shows exactly which fields are not present in every record and which hold more than one type.

A BigQuery schema file looks like this:

[
  {"name": "id",         "type": "INT64",     "mode": "REQUIRED"},
  {"name": "created_at", "type": "TIMESTAMP", "mode": "NULLABLE"},
  {"name": "tags",       "type": "STRING",    "mode": "REPEATED"},
  {"name": "user",       "type": "RECORD",    "mode": "NULLABLE",
   "fields": [
     {"name": "name",  "type": "STRING"},
     {"name": "email", "type": "STRING"}
   ]}
]

Nested and repeated fields

This is where BigQuery is genuinely good and unlike most warehouses: you do not have to flatten. A nested JSON object becomes a RECORD (a STRUCT in SQL), and an array becomes REPEATED. Query them with dots and UNNEST:

-- Nested object: just use a dot
SELECT id, user.email FROM mydataset.mytable;

-- Repeated field: cross join with UNNEST
SELECT id, tag
FROM mydataset.mytable, UNNEST(tags) AS tag;

-- Array of objects
SELECT id, item.sku, item.qty
FROM mydataset.mytable, UNNEST(items) AS item;

So resist flattening on the way in. Keeping the structure is cheaper to store, faster to query, and preserves information that flattening loses. Flattening is for targets that cannot do this — plain CSV, or a database without struct support.

The exception is genuinely unpredictable structure — user-supplied metadata with arbitrary keys. For that, load the field as a JSON column (BigQuery has a native JSON type) and query it with JSON_VALUE, rather than trying to describe every possible key in a schema.

Errors that actually stop a load

Preparing a file that will load first time

1. Validate — every line must be parseable JSON.
2. Fix the encoding — a BOM breaks line 1, and BigQuery expects UTF-8.
3. Normalise key names to snake_case, so no column name is rejected.
4. Check coverage and types — mixed-type fields are the main cause of a load failing part-way.
5. Infer a schema from the whole file rather than relying on a 500-row sample.
6. Make a 100-record sample and load that into a scratch table first. A ten-second test beats a forty-minute failure.
7. Compress before uploading to Cloud Storage. BigQuery reads .gz directly.

Other warehouses, briefly

• Snowflake — COPY INTO … FILE_FORMAT = (TYPE = JSON). Its usual pattern is loading each record into a single VARIANT column and shaping it in SQL afterwards, which is more forgiving of schema drift than BigQuery's up-front schema.
• Redshift — COPY … FORMAT AS JSON 'auto', or a JSONPaths file for explicit mapping. Nested support is weaker; flattening first is usually easier.
• ClickHouse — INSERT INTO t FORMAT JSONEachRow, which is ClickHouse's name for exactly this format.
• DuckDB — read_json_auto('file.jsonl'), no load step at all. The quickest way to sanity-check a file before committing it to a warehouse, and what our SQL playground runs.

Frequently asked questions

Tools mentioned on this page

• Schema inferrerInfer from the whole file, not a 500-row sample.
• Field coverageFind the mixed-type fields that break loads.
• Key case convertersnake_case names BigQuery will accept.
• SQL playgroundTest your queries on the file before loading it.

— S., [email protected]