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:
| File | Error |
|---|---|
[{"id": 1}, {"id": 2}], with autodetect | Failed to parse JSON: No object found when new array is started.; BeginArray returned false |
| the same array, with a schema | JSON parsing error in row starting at position 0: Start of array encountered without start of object. |
| one object over several lines | Failed to parse JSON: Unexpected end of string; Unexpected end of string; Expected key |
| an empty line between two objects | JSON parsing error in row starting at position 10: Unexpected token |
| a UTF-8 byte order mark at the start | JSON parsing error in row starting at position 0: Parser terminated before end of string |
| the same key twice in one object | Multiple 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 }
}
| Field | In the file | Detected type | Stored as |
|---|---|---|---|
user-id | 1 | INTEGER | 1; dashes are allowed in names |
zip | "02134" | INTEGER | 2134, leading zero gone |
code | "007" | INTEGER | 7 |
flag | "true" | BOOLEAN | true |
amt | "12.50" | FLOAT | 12.5, floating point |
created | "2026-03-01T10:00:00Z" | TIMESTAMP | fine |
meta | {"k": 1} | RECORD | a STRUCT with k INTEGER |
Autodetect looks at what a string contains, not at its quotes. Other things we saw:
- A field that is
nullin every sampled row becameSTRING. - A field that is
{}in every row fails the load:Unsupported empty struct type for field 'meta'. - A key like
@typefails the load:Invalid field name "@type". Fields must contain the allowed characters. Namein one row andnamein 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 type | Value in the file | Result |
|---|---|---|
INTEGER | "7" | 7 |
INTEGER | 7.0 | fails: Could not convert value 'number_value: "7.0"' to integer |
INTEGER | 9223372036854775808 (2^63) | fails: same error |
INTEGER | "abc" | fails: Could not convert value 'string_value: "abc"' to integer |
BOOLEAN | "true", true or 1 | true |
NUMERIC | "12.50" or 12.5 | 12.5 |
STRING | 2134 | "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 |
TIMESTAMP | 1772359200 or "1772359200" | 2026-03-01 10:00:00 UTC: a number is read as seconds since the epoch |
TIMESTAMP | 1772359200123 (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 file | Result |
|---|---|
| a key missing from the line | the column is NULL |
a key missing, column is REQUIRED | fails: Missing required field: name. |
"tags": null into a REPEATED column | an 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 schema | fails: 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:
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
| Row | event_id | user_id | amount | ts |
|---|---|---|---|---|
| 1 | e1 | 42 | 12.5 | 2026-03-01 10:00:00 UTC |
| 2 | e2 | 43 | 7 | 2026-03-01 09:30:00 UTC |
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:
WITH raw_events AS (SELECT JSON '{"user": {"id": "43"}}' AS raw) SELECT INT64(raw.user.id) AS user_id FROM raw_events
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:
WITH raw_events AS (SELECT JSON '{"coupon": null}' AS raw) SELECT STRING(raw.coupon) AS coupon FROM raw_events
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:
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
| Row | event_id | coupon | json_type |
|---|---|---|---|
| 1 | e1 | SPRING | string |
| 2 | e2 | null | null |
| 3 | e3 | null | null |
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:
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
| Row | line | is_bad |
|---|---|---|
| 1 | {"id": 1} | false |
| 2 | null | true |
| 3 | {"id": 2 | true |
| 4 | {"id": 3} | false |
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:
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
| Row | id | name |
|---|---|---|
| 1 | 1 | Ada |
| 2 | 2 | Linus |
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
| Limit | Value |
|---|---|
| Row (one line) | 100 MB |
| File, gzip-compressed | 4 GB |
| File, uncompressed | 5 TB |
| Local file through the console | 100 MB |
| Load jobs per table per day | 1,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.