How to extract all the keys in a JSON object with BigQuery

json google-bigquery
Felipe Hoffa picture Felipe Hoffa · Jan 20, 2016 · Viewed 17k times · Source

BigQuery has facilities to parse JSON in real-time interactive queries: Just store the JSON encoded object as a string, and query in real time, with functions like JSON_EXTRACT_SCALAR.

However, I can't find a way to discover all the keys (properties) in these objects.

Can I use a UDF for this?

Answer

Seamus Abshere picture Seamus Abshere · Dec 26, 2019

Here's something that uses Standard SQL:

CREATE TEMP FUNCTION jsonObjectKeys(input STRING)
RETURNS Array<String>
LANGUAGE js AS """
  return Object.keys(JSON.parse(input));
""";
WITH keys AS (
  SELECT
    jsonObjectKeys(myColumn) AS keys
  FROM
    myProject.myTable
  WHERE myColumn IS NOT NULL
)
SELECT
  DISTINCT k
FROM keys
CROSS JOIN UNNEST(keys.keys) AS k
ORDER BY k

Related questions

Is there a way to export a BigQuery table's schema as JSON?

A BigQuery table has schema which can be viewed in the web UI, updated, or used to load data with the bq tool as a JSON file. However, I can't find a way to dump this schema from an existing …

Read More
Converting JSON into newline delimited JSON in Python

My goal is to convert JSON file into a format that can uploaded from Cloud Storage into BigQuery (as described here) with Python. I have tried using newlineJSON package for the conversion but receives the following error. JSONDecodeError: Expecting value …

Read More
Bigquery - json_extract all elements from an array

i'm trying to extract two key from every json in an arry of jsons(using sql legacy) currently i am using json extract function : json_extract(json_column , '$[1].X') AS X, json_extract(json_column , '$[1].Y') AS Y, …

Read More