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

Alina Zhang
Jan 20, 2019 · 2 min read

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?

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”.

Data Driven Investor

from confusion to clarity not insanity

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store