如何在BigQuery中从JSON列提取label与key并拆分为独立行

解决方案 #

你可以组合使用BigQuery的JSON_EXTRACT_ARRAY、UNNEST和JSON_VALUE函数实现需求，完整可运行代码如下：

WITH sample_data AS (
  SELECT '1' AS Row, '[{"label":"Label 1","key":"label_1","description":"Value of label_1"},{"label":"Label 2","key":"label_2","error":"Something"}]' AS Fields 
  UNION ALL
  SELECT '2' AS Row, '[{"description":"something","label":"Row 1","key":"row_1"},{"label":"Row 2","message":"message_1","key":"row_2"}]' AS Fields
)
SELECT
  Row,
  JSON_VALUE(field_item, '$.label') AS Label,
  JSON_VALUE(field_item, '$.key') AS Key
FROM sample_data,
UNNEST(JSON_EXTRACT_ARRAY(Fields)) AS field_item

代码说明 #

1. 首先用JSON_EXTRACT_ARRAY(Fields)将字符串格式的JSON数组转为BigQuery可处理的JSON数组类型
2. 通过UNNEST函数将数组中的每个JSON对象拆分为独立的行，别名field_item对应每个JSON对象
3. 最后用JSON_VALUE分别提取每个JSON对象中label和key对应的值，不需要关心两个字段在JSON中的位置，JSON路径直接匹配键名即可，支持任意合法的键值内容格式

运行上述代码即可得到你预期的输出结果。

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