12 Feb 19 · Mike-Barn · #Bigquery ·   Bookmark   ×

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

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds