The JSON datatype in BigQuery. This topic has been sitting in my Notion list of post ideas for some time.

So in our beloved BQ it is a native, standalone data type, not just another STRING 😁, although strings can of course hold json-like strings.

As data engineers we typically consume them in our pipelines, but let's first understand how to create them.

There are a couple of ways to express a JSON value:
- using the JSON literal
- by parsing a json-like STRING with JSON_PARSE()
- from SQL objects (including an entire row) with TO_JSON()
- creating a json_object from key-value pairs with JSON_OBJECT()
- creating a JSON_ARRAY() from BQ ARRAY

Note that, for some of the above options, since JSON also have quotes, use multi-line strings """ """ or escape quotes with \.

Defining our JSON objects as such will allow us to use JSON functions with them and, of course, store heterogeneous data in the same column.

Stay tuned for the next posts on this topic.

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.