Extracting keys from JSON in BigQuery
Senior Data Engineer • Contractor / Freelancer • GCP & AWS Certified
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.
Enjoyed this? Here are some related articles you might find useful:
