Big Query

How to debug “Error while reading data, error message: Failed to parse JSON: Unexpected end of string; Unexpected end of string; Expected key” ?

Omid

I read this post, and there is a suggested solution, this should explain the error:

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

and read this article about new line JSON: What is JSON new line format?

regular JSON:

[
{"key":"value1"},
{"key":"value2"},
]

new line JSON format :

{"key":"value1"}
{"key":"value2"}

If using the above post to is not enough to load to BigQuery, using JQ is not enough. I suggest some debugging options:

  1. Ensure JSON is valid , search on google or click on the url: “json online viewer”
  2. you may have an invalid JSON key. remove keys until you get it right – on load to big query
  3. use regex : remove all spaces/new lines/special characters until you get some like “{key=value}” with reductant spaces
sed 's/ //g' example.json > example_output.json

or 

cat example.json | tr '\n' ' ' > example_output.json

After you apply the above transformation, try to load the JSON to BQ and will either succeed or find the next problem in the JSON.

common json parsing error for big query in my case it was that

  1. in bigQuery data type nested arrays (array of arrays) is not supported, although it is a valid JSON.

2. that json {} contained inside each line/value a newline , simply replace it with space.


——————————————————————————————————————————

I put a lot of thoughts into these blogs, so I could share the information in a clear and useful way. If you have any comments, thoughts, questions, or you need someone to consult with, feel free to contact me:

https://www.linkedin.com/in/omid-vahdaty/

Leave a Reply