Extracting keys from JSON in BigQuery

A couple of months ago, I've posted about dynamically extracting key-value pairs from JSON in BigQuery SQL which leveraged regex (check comments).

Shortly after that post, we've gotten a new built-in function to dynamically extract the keys occurring in a JSON. It allows us to retrieve all the keys occurring in a JSON value, with a few controls on how this is done.

The function is JSON_KEYS. Apart from the json input, we can tweak:
- max_depth: for many levels of nesting we should go through to extract keys
- mode: strict/lax/lax recursive - controls if we extract keys from arrays.

The usual note - still in preview.

Found it useful? Subscribe to my Analytics newsletter at https://www.notjustsql.com.