Storing JSON in BigQuery

Question

I have various highly nested json objects. I am wondering whether to store these as STRUCTs in BigQuery or as a STRING. If storing it as a string, then I can use JSON_EXTRACT where necessary to get what I need. I have a few questions on using the following approach:

• Would it be a bad idea storing json data as a string instead of record?
• Would there be a big performance hit whenever using that json field if it's stored as a string?
• What additional advantages would storing the json as a STRUCT instead of a string give?

Finally, I wasn't able to find any place in the documentation that gives examples of how to query STRUCTs. The only place I could find was https://cloud.google.com/bigquery/docs/nested-repeated. Are there examples in the documentation (or elsewhere) on querying nested fields? Additionally, why is the term RECORD and STRUCT used interchangeably on this page?

Note that the json will not be repeated at the root level, i.e., it will look like {...} and not [{...},{...}].

As a reference, in Redshift you would (as of this question) store json as a string and use the json-functions to manipulate it: https://stackoverflow.com/a/32731374/651174.

Answer 1

I usually do both:

• Store JSON objects as STRINGs for posterity and re-factorings.
• Materialize easy-to-query tables from your JSON objects - to get you and your team a better experience when querying.

My 3 steps:

1. Store everything as JSON strings. Then you won't lose data in case of schema changes, for example.
2. Create a VIEW that JSON_EXTRACTs data into easy to query columns.
3. Materialize those views into tables for the best performance and ease.

Then, in case of schema change:

1. Everything you have stored, stays the same.
2. You can modify the views to suit the new schema.
3. You can re-materialize tables into the new schema.