Google launched JSON Keys and Paths for BigQuery

News Functions when working with JSON in BigQuery SQL

Photo by Dan Meyers on Unsplash

Google just launched two very useful JSON functions for BigQuery — the JSON_KEYS function and JSONPaths.

JSON is an often used data format when working with large and unstructured datasets. Google launched JSON support for BigQuery a while ago and now added two new features.

How to work with JSON in BigQuery

Firstly the JSON_KEYS function, which extracts unique JSON keys from a JSON expression. Extracts unique JSON keys from a JSON expression with the following arguments[1][2]:

• json_expr: JSON. For example:JSON '{"class": {"students": [{"name": "Jane"}]}}'
• max_depth: An INT64 value that represents the maximum depth of nested fields to search in json_expr.
• mode: A named argument with a STRING value that can be one of the following:
• strict (default): Ignore any key that appears in an array.
• lax: Also include keys contained in non-consecutively nested arrays.