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.
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:
- convert the file into NDJSON
- 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”.