JSON parsing error: How to Load JSON into BigQuery Successfully using NDJSON

Multiple types of errors you could encounter while loading JSON file into BigQuery. For example,

  • JSON parsing error in row starting at position 0: Expected key
  • Unexpected end of string; Unexpected end of string; Expected key

How to load JSON into BigQuery successfully without the pain of debugging those kinds of errors? The trick is to use Newline delimited JSON (ndjson) instead of standard json with the steps below.

The following example shows a newline delimited file converted from standard json format.

Newline delimited JSON
standard JSON

NDJSON is a convenient format for storing or streaming structured data that may be processed one record at a time.

  • Each line is a valid JSON value
  • Line separator is ‘\n’

1. Convert JSON to NDJSON?

cat test.json | jq -c '.[]' > testNDJSON.json

With this simple line of code, you can convert and save files in NDJSON format.

2. Load the NDJSON file from BigQuery UI

Successfully loaded. Preview of the table shows it is a nested json file.

To conclude, in order to load JSON file into BigQuery successfully each time, you can follow these steps:

  1. convert the file into NDJSON
  2. load it from BigQuery UI

There is one more thing you want to pay attention to: the original JSON need to have “[]” at the outermost layer as:

[
{
...
},
{
...
},
{
...
}
]

Enjoy your “load”.