Datawise
BigQuery JSON

Extracting keys from JSON in BigQuery

Constantin LunguUpdated 1 min read

Photo by Aaron Burden on Unsplash

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.

DECLARE json_data  DEFAULT JSON  """
      {"name": "John Doe",
      "city": "New York",
      "sports": [{"name": "football", "since": 2020, "club": "Liberty FC"},
                 {"name": "snooker", "since": 2019, "club": "Snooker Champions"},
                 {"name": "tennis", "since": 2015, "club":"Tennis Stars"}
                ]
      }
  """;

SELECT JSON_KEYS(json_data, mode => 'lax') AS json_keys

BigQuery results: json_keys returns one row holding six keys: city, name, sports, sports.club, sports.name and sports.since.

Enjoyed this? Here are some related articles you might find useful: