BigQuery - JSON Data Type

JSON is the newest data type the BigQuery supports. Unlike the STRUCT and ARRAY types, JSON is relatively easy to recognize.

For developers who have worked with data in a scripting language or those who have parsed API responses, JSON data will be familiar.

JSON data is indicated by curly brackets: { }, like a Python dictionary.

Note − Prior to BigQuery introducing support for the JSON type, JSON objects would need to be represented as a STRING with a NULLABLE mode.

Developers can specify JSON in both the UI and in text-based schema definitions −

Not storing JSON data as a JSON type won't necessarily result in a failed load, because BigQuery can support the STRING type for JSON data.

However, not storing JSON data properly means that developers lose access to powerful JSON-specific functions.

Powerful JSON Functions

Thanks to built-in functions, developers working with JSON data in BigQuery don't need to write scripts to flatten JSON data. Instead, they can use JSON_EXTRACT to extract the contents of a JSON object, which then allows for the handling and manipulation of the resulting data.

Other powerful JSON functions include −

• JSON_EXTRACT_ARRAY()
• PARSE_JSON()
• TO_JSON()

Being able to accurately and intuitively query JSON data within BigQuery saves developers from needing to use complex CASE logic or write custom functions to extract valuable data.