BigQuery: Load JSON object as a string

Admin 1
Viewed 1012

I have a new line delimited json with 3 fields: 2 of them are strings, the 3rd is a json object. Here is an example: 

{"val1":"stringvalue1","val2":"stringvalue2","val3":{"i1":789,"i2":60.0,"i3":8}}

I would like to load the file directly with all 3 fields as STRING.

The 3rd field would be "{"i1":789,"i2":60.0,"i3":8}" here.

I don't know how many fields the JSON object in val3 will contain: it's different between each lines. Is there a way to just directly convert the JSON object to a string ? I tried specifying the column as string in the schema, but I got a parsing error while loading.

Thank you

google-bigquery json
2 Answers

  1. Load as single column CSV.

    a. Make sure to set the field separator character to a rare one, or just null (\0).

  2. That will load the whole row as a single column.

  3. Parse the text row inside BigQuery. Either with the native JSON functions, or with Javascript. In this case, the native BigQuery JSON functions should be enough.

user_1324380

As you can see this format "{"i1":789,"i2":60.0,"i3":8}" makes it incorrect (look at opening and closing ".

You have to convert it into string so it looks like this "{\"i1\":789,\"i2\":60.0,\"i3\":8}".

I used jq python library to do it. Something like

jq.compile("tostring").input(your_input_json).text()

And then when I loaded this json to bigquery (via google cloud storage in my case) it was breeze,

The Google Storage file looked like this

"{\"i1\":789,\"i2\":60.0,\"i3\":8}"

and bigquery had columns with value

{"i1":789,"i2":60.0,"i3":8}
user_22967280
Related