Google launched JSON KEYS for BigQuery

Using the JSON KEYS Function to extract unique JSON Keys

Photo by Yousef Espanioly on Unsplash

Google just annouced that you can use the JSON_KEYS function to extract unique JSON keys from a JSON expression[1].

When working with unstructured or semi structured data within BigQuery JSON can be a a good choice to work with this kind of data. Google therefore introduced the JSON format a time ago.

How to work with JSON in BigQuery

Now they added JSON Keys and that you can specify a mode for some JSON functions that take a JSONPath to allow more flexibility in how the path matches the JSON structure[1][2].

The function work like that[2]:

SELECT JSON_KEYS(JSON ‘{“name”:”Alice”,”age”:30,”city”:”Paris”}’) AS keys;

• Where the json_expr is just our JSON we defined
• max_depth: An INT64 value that represents the maximum depth of nested fields to search in json_expr. If not set, the function searches the entire JSON document.
• mode: A named argument with a STRING value that can be one of…