BigQuery中如何两次使用UNNEST解析嵌套JSON数组获取目标查询结果

原有查询会将每个属性拆分为独立的行，无法在同一行展示多个属性值，需要配合行转列逻辑实现预期输出，以下是两种可行的实现方案：

方案1：条件聚合（兼容性高，灵活度高） #

该方案适配性强，后续新增属性只需新增对应条件判断行即可：

SELECT 
  c.id AS Category_ID,
  c.name AS Name_ID,
  MAX(IF(p.name = 'Property_1', p.value.value, NULL)) AS Property_1,
  MAX(IF(p.name = 'Property_2', p.value.value, NULL)) AS Property_2
FROM `DataBase-xxxxxx`
CROSS JOIN UNNEST(categories) AS c
CROSS JOIN UNNEST(c.properties) AS p
GROUP BY Category_ID, Name_ID

逻辑说明：两次UNNEST完成数组打平后，通过IF条件提取对应属性的值，再按分类ID、分类名称分组聚合，即可将多行属性数据合并为单行输出，此处MAX仅用于过滤分组后的NULL值，不影响实际结果。

方案2：PIVOT语法（适合属性较多的场景） #

利用BigQuery原生行转列语法实现，代码结构更简洁：

SELECT * FROM (
  SELECT 
    c.id AS Category_ID,
    c.name AS Name_ID,
    p.name AS prop_name,
    p.value.value AS prop_value
  FROM `DataBase-xxxxxx`
  CROSS JOIN UNNEST(categories) AS c
  CROSS JOIN UNNEST(c.properties) AS p
)
PIVOT (
  MAX(prop_value) FOR prop_name IN ('Property_1', 'Property_2')
)

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