How to resolve Failed to parse JSON error when loading JSON files into Google BigQuery

Problem 🔍

Bigquery only allows Newline delimited JSON files and not JSON Arrays. So while loading a JSON object you might get one of the below error messages:

When loading one JSON object

Error while reading data, error message: Failed to parse JSON: 
Unexpected end of string; Unexpected end of string; Expected key

And the below error when you try to load a JSON array

Error while reading data, error message: Failed to parse JSON: 
No object found when new array is started.; BeginArray returned false; 
Parser terminated before end of string

Solution 💡

#1 convert the standard JSON format to Newline delimited JSON (ndjson)

BigQuery only accepts new-line delimited JSON, which means one complete JSON object per line

Example-1

{
    "current_speed": "19.09",
    "_wind": "-87.654561"
}

Needs to be converted to 

{"current_speed": "19.09","_wind": "-87.654561"}

Example-2: Multiline JSON Arrays needs to be converted to Newline delimited JSON like shown below

[{
    "current_speed": "19.09",
    "_wind": "-87.654561"
},{
    "current_speed": "20.09",
    "_wind": "-87.654561"
}]

convert to

{"current_speed": "19.09","_wind": "-87.654561"}
{"current_speed": "20.09","_wind": "-87.654561"}

#2 Easy Command line method to convert the JSON file to NDJSON

Run the below command to convert JSON file to NDJSON easily

cat oldfile.json | jq -c '.[]' > newNDJSON.json

This should fix the issue !

Mike-Barn

posted on 12 Feb 19