Fix GCP BigQuery Load Json Errors

When working with GCP BigQuery, you may encounter a configuration error that prevents your data pipeline or messaging system from working. This guide explains the most common mistake with load json and shows the exact fix.

A Common Mistake

Loading newline-delimited JSON with incorrectly formatted records (pretty-print or array format), causing load failures.

The incorrect command:

bq load --source_format=NEWLINE_DELIMITED_JSON my_project:my_dataset.my_table data.json
# data.json contains:
# [{ "id": 1, "name": "Alice" }, "#  { "id": 2", "name": "Bob" }]

Error output:

Error: Could not parse JSON: The JSON object must be on a single line. Newline-delimited JSON requires each record on its own line. Array format and pretty-printed JSON are not supported.

The Correct Approach

The right way to configure load json in GCP BigQuery:

bq load --source_format=NEWLINE_DELIMITED_JSON my_project:my_dataset.my_table data.json
# data.json contains:
# {"id":1,"name":"Alice"}
# {"id":2,"name":"Bob"}

Successful result:

Loaded 2 rows.
Each line is a valid JSON object. The newline-delimited JSON format is parsed correctly. One JSON object per line, no trailing commas, no outer array.

How to Prevent This

Use one JSON object per line. Do not wrap in an array. No trailing commas. Each line must be valid JSON. Use jq -c . input.json > output.json to convert pretty-print JSON to newline-delimited. Use --max_bad_records to tolerate some parse errors. Validate with head -5 file.json | python -m json.tool per line.

FAQ

Built by the developers of Doda Browser, DodaZIP, and Durga Antivirus Pro. Secure your cloud with DodaTech.