如何在BigQuery中将JSON键值对拆分为键和值列？

针对需要将动态JSON列（键数量不固定）解析为key和value两列的需求，以下提供两种实现方案：

一、常规解法（无需依赖外部UDF） #

利用OBJECT_KEYS获取所有键名，结合UNNEST和JSON_EXTRACT_ARRAY匹配对应的值：

WITH sample_data AS (
  SELECT PARSE_JSON('{"a": [1,2,3], "b":[4,5,6]}') AS key_values
)
SELECT
  key,
  JSON_EXTRACT_ARRAY(key_values, CONCAT('$.', key)) AS value
FROM sample_data,
UNNEST(OBJECT_KEYS(key_values)) AS key

二、使用BigQuery新增的bqutil函数 #

借助bqutil.fn.json_extract_keys提取所有键名数组，bqutil.fn.json_extract_values提取对应的值数组，通过UNNEST将两个数组按位置关联：

WITH sample_data AS (
  SELECT PARSE_JSON('{"a": [1,2,3], "b":[4,5,6]}') AS key_values
)
SELECT
  keys AS key,
  values AS value
FROM sample_data,
UNNEST(bqutil.fn.json_extract_keys(key_values)) AS keys
WITH OFFSET pos
JOIN UNNEST(bqutil.fn.json_extract_values(key_values)) AS values
WITH OFFSET pos
USING(pos)

内容的提问来源于stack exchange，提问作者yxteh