Query key-value pairs from json in bigquery into a key and value columns

Question

I have a JSON column in bigquery as such

SELECT PARSE_JSON('{"a": [1,2,3], "b":[4,5,6]}') AS key_values;

Is it possible to write a query such that I can get the following?

I can have any number of keys so I cannot specify the key name in my query.

Seems like this should be really simple but I cannot figure it out :(

EDIT: Apart from the answer below, here are a couple native bq functions that I THINK were added recently.

Couple of new functions that I THINK were introduced recently.

bqutil.fn.json_extract_values

bqutil.fn.json_extract_keys

Unless the set of a,b keys is fixed (where Barbaros' answer suffices) you will probably need to laterally unnest map keys and then array elements. This is AFAIK not possible using only built-in JSON functions in BigQuery, you can utilize custom Javascript functions, see [here](https://stackoverflow.com/a/59490036/653539) and [here](https://stackoverflow.com/a/57120586/653539). — Tomáš Záluský, Mar 20 '23 at 07:26

score 2 · Accepted Answer · answered Mar 20 '23 at 07:27

I cannot specify the key name in my query.

you can consider below.

CREATE TEMP FUNCTION json_keys(input STRING) RETURNS Array<String>
LANGUAGE js AS """
  return Object.keys(JSON.parse(input));
""";

CREATE TEMP FUNCTION json_values(input STRING) RETURNS Array<String>
LANGUAGE js AS """
  return Object.values(JSON.parse(input));
""";

WITH sample_data AS (
  SELECT '{"a": [1,2,3], "b":[4,5,6]}' AS key_values
)
SELECT k, v
  FROM sample_data,
UNNEST (json_keys(key_values)) k WITH offset JOIN
UNNEST (json_values(key_values)) v0 WITH offset USING(offset),
UNNEST (SPLIT(v0, ',')) v;

Query results

score 1 · Answer 2 · answered Mar 20 '23 at 07:21

You can use UNNEST operator along with CROSS JOIN s to have flattened result such as

SELECT 'a' AS key, value
  FROM t,
       UNNEST(JSON_QUERY_ARRAY(json.a)) AS value
UNION ALL
SELECT 'b', value
  FROM t,
       UNNEST(JSON_QUERY_ARRAY(json.b)) AS value

Demo

Query key-value pairs from json in bigquery into a key and value columns

2 Answers2