BigQuery: Load JSON object as a string

Question

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

Answer 1

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.