# 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. Source: https://docs.querylab.io/bigquery/loading-json/ Updated: 2026-11-03 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: ```json {"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: ```sh 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](https://docs.querylab.io/data-management/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`: ```sh 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'`: ```sql LOAD DATA INTO mydataset.orders ( order_id STRING, customer STRUCT, items ARRAY> ) 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](https://docs.querylab.io/bigquery/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: ```json { "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 `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 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`: ```json [ { "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](https://docs.querylab.io/bigquery/arrays/) and [Nested and repeated data](https://docs.querylab.io/bigquery/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](https://docs.querylab.io/bigquery-errors/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. ```sh 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: ```sh 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: ```text 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 columns** ```sql 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 (2 rows): | event_id | user_id | amount | ts | | --- | --- | --- | --- | | e1 | 42 | 12.5 | 2026-03-01 10:00:00 UTC | | 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: **INT64 on a JSON string fails** ```sql WITH raw_events AS (SELECT JSON '{"user": {"id": "43"}}' AS raw) SELECT INT64(raw.user.id) AS user_id FROM raw_events ``` BigQuery rejects this query: ```text 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 fails** ```sql WITH raw_events AS (SELECT JSON '{"coupon": null}' AS raw) SELECT STRING(raw.coupon) AS coupon FROM raw_events ``` BigQuery rejects this query: ```text 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_STRING** ```sql 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 (3 rows): | event_id | coupon | json_type | | --- | --- | --- | | e1 | SPRING | string | | e2 | NULL | null | | e3 | NULL | NULL | More on reading JSON columns is in [JSON in BigQuery](https://docs.querylab.io/bigquery/json/). 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 JSON** ```sql 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 (4 rows): | line | is_bad | | --- | --- | | {"id": 1} | false | | NULL | true | | {"id": 2 | true | | {"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: **unnest a whole-file JSON array** ```sql 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 (2 rows): | id | name | | --- | --- | | 1 | Ada | | 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](https://docs.querylab.io/bigquery/loading-data/) 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](https://docs.querylab.io/bigquery/streaming/) and [Storage pricing](https://docs.querylab.io/bigquery/storage-pricing/). Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in. ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [Loading JSON data from Cloud Storage](https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-json) - [Batch loading data](https://cloud.google.com/bigquery/docs/batch-loading-data) - [Using schema auto-detection](https://cloud.google.com/bigquery/docs/schema-detect) - [Quotas and limits, load jobs](https://cloud.google.com/bigquery/quotas#load_jobs)