Docs/Writing data/Loading JSON
Writing data

Loading JSON into BigQuery

BigQuery loads newline-delimited JSON, one object per line. A JSON array or pretty-printed file fails. Autodetect turns "02134" into the integer 2134, an epoch in milliseconds fails a TIMESTAMP column, and a field that varies between rows is easiest to load into a JSON column.

BigQuery loads JSON only as newline-delimited JSON (NDJSON, also called JSON Lines): one complete JSON object per line, with no surrounding array. Load it with bq load --source_format=NEWLINE_DELIMITED_JSON, with LOAD DATA ... FROM FILES (format = 'JSON') in SQL, or from the console. A file that is a JSON array ([{...}, {...}]), or a single object spread over several lines, fails to load. Convert it first with jq -c '.[]' in.json > out.ndjson. Batch loads are free; you pay only to store the table.

Everything below was tested with real load jobs. The error messages are the ones BigQuery returned.

The file must be one object per line

This loads:

{"id": 1, "name": "Ada"}
{"id": 2, "name": "Linus"}

These don’t:

FileError
[{"id": 1}, {"id": 2}], with autodetectFailed to parse JSON: No object found when new array is started.; BeginArray returned false
the same array, with a schemaJSON parsing error in row starting at position 0: Start of array encountered without start of object.
one object over several linesFailed to parse JSON: Unexpected end of string; Unexpected end of string; Expected key
an empty line between two objectsJSON parsing error in row starting at position 10: Unexpected token
a UTF-8 byte order mark at the startJSON parsing error in row starting at position 0: Parser terminated before end of string
the same key twice in one objectMultiple definition of field: id.

Windows line endings (\r\n) load fine. An empty line, often the last line of a file, does not: the whole job fails, and nothing is written.

To turn a JSON array into NDJSON:

jq -c '.[]' orders.json > orders.ndjson

-c writes each element on one line. For a file that holds one object per file, jq -c . order.json does the same.

Querylab.io’s Import tab takes a .json file in either form, a JSON array or NDJSON, and converts it to NDJSON for the load job. It shows the detected schema before anything is written. The BigQuery console’s Create table dialog takes NDJSON only, and local uploads there are limited to 100 MB.

Loading the file

From Cloud Storage or a local file with bq:

bq load \
  --source_format=NEWLINE_DELIMITED_JSON \
  mydataset.orders \
  'gs://my-bucket/orders/2026-03-*.json' \
  ./orders_schema.json

The same from SQL. LOAD DATA reads from Cloud Storage, not from your machine, and calls the format 'JSON':

LOAD DATA INTO mydataset.orders (
  order_id STRING,
  customer STRUCT<id INT64, email STRING>,
  items ARRAY<STRUCT<sku STRING, qty INT64>>
)
FROM FILES (
  format = 'JSON',
  uris = ['gs://my-bucket/orders/2026-03-*.json']
);

Neither example is run here; both need a bucket and a dataset. A load appends by default; --replace or LOAD DATA OVERWRITE replaces the table. Wildcards work for Cloud Storage only; local files load one per command.

What autodetect guesses

--autodetect reads a sample from the start of the file and picks a type per field. Google’s docs say 500 rows; our CSV tests saw it read far more, but not always the whole file (Loading CSV). JSON has types of its own, so you might expect a quoted "02134" to stay a string. It doesn’t. We loaded this file with autodetect:

{
  "user-id": 1,
  "zip": "02134",
  "code": "007",
  "flag": "true",
  "amt": "12.50",
  "created": "2026-03-01T10:00:00Z",
  "meta": { "k": 1 }
}
FieldIn the fileDetected typeStored as
user-id1INTEGER1; dashes are allowed in names
zip"02134"INTEGER2134, leading zero gone
code"007"INTEGER7
flag"true"BOOLEANtrue
amt"12.50"FLOAT12.5, floating point
created"2026-03-01T10:00:00Z"TIMESTAMPfine
meta{"k": 1}RECORDa STRUCT with k INTEGER

Autodetect looks at what a string contains, not at its quotes. Other things we saw:

  • A field that is null in every sampled row became STRING.
  • A field that is {} in every row fails the load: Unsupported empty struct type for field 'meta'.
  • A key like @type fails the load: Invalid field name "@type". Fields must contain the allowed characters.
  • Name in one row and name in another fails: Duplicate(Case Insensitive) field names: name and Name. Column names in BigQuery are case-insensitive.
  • The table’s columns came out in a different order than the keys in the file.

For a one-off look at a file, autodetect is fine. For anything that loads again, give a schema.

What converts with an explicit schema

With a schema, BigQuery converts JSON values to the column type where it can. One value it can’t convert fails the whole load. Each line below was a separate load:

Column typeValue in the fileResult
INTEGER"7"7
INTEGER7.0fails: Could not convert value 'number_value: "7.0"' to integer
INTEGER9223372036854775808 (2^63)fails: same error
INTEGER"abc"fails: Could not convert value 'string_value: "abc"' to integer
BOOLEAN"true", true or 1true
NUMERIC"12.50" or 12.512.5
STRING2134"2134"
STRING{"a": 1}fails: JSON object specified for non-record field: meta
DATE"2026/03/03"fails: Invalid date: '2026/03/03'. Use YYYY-MM-DD, or --date_format.
TIMESTAMP"2026-03-01 10:00:00.123+02:00"converted to UTC: 2026-03-01 08:00:00.123
TIMESTAMP1772359200 or "1772359200"2026-03-01 10:00:00 UTC: a number is read as seconds since the epoch
TIMESTAMP1772359200123 (milliseconds)fails: Cannot return an invalid timestamp value of 1772359200123000000 microseconds

The last row is a common one: JavaScript’s Date.now() and most event trackers write milliseconds, and BigQuery reads the number as seconds. Load such a field as INTEGER and convert it in SQL with TIMESTAMP_MILLIS(). Querylab.io’s Import tab offers this when a number looks like an epoch: its type menu has an Epoch Conversion choice for seconds or milliseconds.

A schema file is a JSON array of fields. Nested objects are RECORD, arrays are REPEATED:

[
  { "name": "order_id", "type": "STRING" },
  {
    "name": "customer",
    "type": "RECORD",
    "fields": [
      { "name": "id", "type": "INTEGER" },
      { "name": "email", "type": "STRING" }
    ]
  },
  {
    "name": "items",
    "type": "RECORD",
    "mode": "REPEATED",
    "fields": [
      { "name": "sku", "type": "STRING" },
      { "name": "qty", "type": "INTEGER" }
    ]
  }
]

bq show --schema --format=prettyjson mydataset.orders prints an existing table’s schema in this form, which is the quickest way to get one: load a sample with autodetect, print the schema, fix the types, and use the file from then on.

Nulls, arrays and missing fields

In the fileResult
a key missing from the linethe column is NULL
a key missing, column is REQUIREDfails: Missing required field: name.
"tags": null into a REPEATED columnan empty array []
"tags": ["a", null, "b"]fails: Only optional fields can be set to NULL. Field: tags; Value: NULL
"tags": [["a"], ["b"]]fails: Nested arrays not allowed.
a key that isn’t in the schemafails: No such field: extra.

A BigQuery array can’t hold NULL and can’t directly hold another array, so JSON that does either can’t go into a REPEATED column. Load that field into a JSON column (next section). See Arrays and Nested and repeated data.

--ignore_unknown_values drops keys that aren’t in the schema instead of failing. Use it when producers add fields over time. Those fields are dropped silently.

One bad line fails the load

A single line that doesn’t parse or convert fails the whole job, and nothing is written. --max_bad_records=N skips up to N such lines. We loaded four lines, one empty and one cut off ({"id": 2), with --max_bad_records=5. The job succeeded with two warnings and the table had 2 rows. If you allow bad records, compare the row count with the source. The message of a job that fails is explained in CSV or JSON table encountered too many errors.

Loading into a JSON column

When fields vary between rows, such as event payloads, webhooks or API responses, give the varying part the JSON type. BigQuery keeps it as parsed JSON and you pick fields out in SQL. Nothing in it can fail a type conversion.

bq load --source_format=NEWLINE_DELIMITED_JSON \
  mydataset.events ./events.ndjson event_id:STRING,payload:JSON

With this, {"user": {"id": 42}} and {"user": {"id": "43"}} both load into payload, as they are.

To load each whole line into one JSON column, load the file as CSV with a delimiter and quote character that never appear in it:

bq load --source_format=CSV --field_delimiter=$'\x01' --quote='' \
  mydataset.raw_events ./events.ndjson raw:JSON

This loads any valid JSON line, whatever its keys. When a line is bad, the error gives its line number, which the NDJSON reader doesn’t:

syntax error while parsing object - unexpected end of input; expected '}'; line_number: 3 byte_offset_to_start_of_line: 11 column_index: 0 column_name: "raw" column_type: JSON value: "{\"id\": 2"

Then turn it into typed columns with a query. LAX_INT64 accepts a number or a numeric string, so both user IDs convert:

raw JSON to typed columnsOpen in Querylab.io
123456789101112
WITH raw_events AS (
  SELECT JSON '{"event_id": "e1", "user": {"id": 42}, "amount": "12.50", "ts": "2026-03-01T10:00:00Z"}' AS raw
  UNION ALL
  SELECT JSON '{"event_id": "e2", "user": {"id": "43"}, "amount": 7, "ts": "2026-03-01 11:30:00+02:00"}'
)
SELECT
  STRING(raw.event_id) AS event_id,
  LAX_INT64(raw.user.id) AS user_id,
  CAST(LAX_STRING(raw.amount) AS NUMERIC) AS amount,
  TIMESTAMP(STRING(raw.ts)) AS ts
FROM raw_events
ORDER BY event_id
Result in BigQuery
Rowevent_iduser_idamountts
1e14212.52026-03-01 10:00:00 UTC
2e24372026-03-01 09:30:00 UTC
2 rows

The timestamp with +02:00 became 09:30 UTC. The strict functions (INT64, STRING, BOOL, FLOAT64) fail when the JSON type is wrong. A user ID that arrived as a string stops the whole query:

INT64 on a JSON string failsBigQuery rejects this query
123
WITH raw_events AS (SELECT JSON '{"user": {"id": "43"}}' AS raw)
SELECT INT64(raw.user.id) AS user_id
FROM raw_events
Error in BigQuery

The provided JSON input is not an integer; error in INT64 expression

A JSON null fails them too, which catches people after a load where some rows have "coupon": null:

STRING on a JSON null failsBigQuery rejects this query
123
WITH raw_events AS (SELECT JSON '{"coupon": null}' AS raw)
SELECT STRING(raw.coupon) AS coupon
FROM raw_events
Error in BigQuery

The provided JSON input is not a string; error in STRING expression

LAX_STRING returns SQL NULL for both a JSON null and a missing key. JSON_TYPE tells them apart:

null and missing keys with LAX_STRINGOpen in Querylab.io
1234567891011
WITH raw_events AS (
  SELECT 'e1' AS event_id, JSON '{"coupon": "SPRING"}' AS raw UNION ALL
  SELECT 'e2', JSON '{"coupon": null}' UNION ALL
  SELECT 'e3', JSON '{}'
)
SELECT
  event_id,
  LAX_STRING(raw.coupon) AS coupon,
  JSON_TYPE(raw.coupon) AS json_type
FROM raw_events
ORDER BY event_id
Result in BigQuery
Rowevent_idcouponjson_type
1e1SPRINGstring
2e2nullnull
3e3nullnull
3 rows

More on reading JSON columns is in JSON in BigQuery.

Numbers: an integer within the INT64 range keeps its exact value, even past 2^53 where JavaScript loses precision (9007199254740993 stayed exact). A larger one is rounded to a float on load without an error: 123456789012345678901234567890 was stored as 1.2345678901234568e+29. If IDs in your data are that long, put them in quotes before loading.

Finding the bad lines

To see which lines are broken without failing the load, load them as STRING with the same CSV trick (raw:STRING). Then SAFE.PARSE_JSON returns NULL for every line that isn’t valid JSON. The empty line came in as NULL:

find lines that aren't valid JSONOpen in Querylab.io
12345678
WITH raw_lines AS (
  SELECT '{"id": 1}' AS line UNION ALL
  SELECT NULL UNION ALL
  SELECT '{"id": 2' UNION ALL
  SELECT '{"id": 3}'
)
SELECT line, SAFE.PARSE_JSON(line) IS NULL AS is_bad
FROM raw_lines
Result in BigQuery
Rowlineis_bad
1{"id": 1}false
2nulltrue
3{"id": 2true
4{"id": 3}false
4 rows

A JSON array file as one row

If the file is a JSON array on a single line, the CSV trick loads it as one row with one JSON value. UNNEST(JSON_QUERY_ARRAY(...)) then gives one row per element:

unnest a whole-file JSON arrayOpen in Querylab.io
123456
WITH upload AS (
  SELECT JSON '[{"id": 1, "name": "Ada"}, {"id": 2, "name": "Linus"}]' AS doc
)
SELECT INT64(r.id) AS id, STRING(r.name) AS name
FROM upload, UNNEST(JSON_QUERY_ARRAY(doc)) AS r
ORDER BY id
Result in BigQuery
Rowidname
11Ada
22Linus
2 rows

This works up to the 100 MB row limit. For larger files, convert to NDJSON with jq, which also lets BigQuery read the file in parallel.

Compression and limits

LimitValue
Row (one line)100 MB
File, gzip-compressed4 GB
File, uncompressed5 TB
Local file through the console100 MB
Load jobs per table per day1,500, failed ones included

JSON files can be gzip-compressed (.json.gz). BigQuery can’t read a gzip file in parallel, so a load of one big compressed file is much slower than the same data uncompressed. Uncompressed files cost more Cloud Storage and bandwidth while they wait in the bucket. One load job can’t mix compressed and uncompressed files. For data you control, Parquet or Avro load faster and carry their own schema; see Loading data into BigQuery for formats, the daily load limit, and partition loads.

What it costs

A batch load on the shared slot pool is free. You pay for storing the table. Loading the same rows with INSERT statements is billed as queries, and streaming is billed per GiB. See Streaming and Storage pricing.

Open the playground. It is the real app on public datasets and needs no sign-in.