如何在BigQuery中提取JSON对象的唯一键值对？

问题原因 #

你之前的查询用JSON_EXTRACT_ARRAY()处理JSON对象类型的extra_details字段，但这个函数仅对JSON数组有效，所以返回空结果，自然没有数据输出。

正确查询方案 #

需要先过滤掉extra_details为null的行，再提取每个JSON对象的所有键，展开后对应提取值，最后去重得到唯一键值对。

完整SQL查询 #

SELECT DISTINCT
  key,
  JSON_EXTRACT(extra_details, CONCAT('$.', key)) AS value
FROM
  `my_table`,
  UNNEST(IFNULL(JSON_KEYS(extra_details), [])) AS key
WHERE
  extra_details IS NOT NULL

关键说明 #

• IFNULL(JSON_KEYS(extra_details), [])：处理extra_details为null的情况，避免UNNEST处理空值报错
• JSON_KEYS()：提取JSON对象的所有键，返回字符串数组
• UNNEST()：将键数组展开为多行
• JSON_EXTRACT()：根据键提取对应的值，保留原始数据类型（如数字、布尔值）
• DISTINCT：去除重复的键值对

测试结果 #

针对你的样本数据，会返回如下唯一键值对：

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