BigQuery动态解析未知数量嵌套JSON节点并拆分为独立列

问题场景 #

待解析JSON结构中rewards字段下的reward_N子节点数量不固定，无法提前枚举所有节点，需要自动解析所有节点的type、amount字段并映射为独立列。

实现方案 #

通过BigQuery原生JSON函数提取所有动态键，结合动态PIVOT语法自动生成列，无需提前知晓节点总数。

查询代码 #

-- 注意替换代码中`your_project.your_dataset.your_table`为实际表路径，`json_data`为存储JSON内容的字段名
EXECUTE IMMEDIATE FORMAT("""
WITH parsed_rewards AS (
  SELECT
    -- 可按需在此处添加需要保留的其他原始表字段，如记录ID、上报时间等
    json_data,
    reward_key,
    JSON_VALUE(reward_obj, '$.type') AS reward_type,
    JSON_VALUE(reward_obj, '$.amount') AS reward_amount
  FROM `your_project.your_dataset.your_table`
  -- 拆分rewards下所有子节点为独立行
  CROSS JOIN UNNEST(JSON_KEYS(json_data.rewards)) AS reward_key
  CROSS JOIN UNNEST([JSON_QUERY(json_data.rewards, '$.' || reward_key)]) AS reward_obj
)
SELECT * FROM parsed_rewards
PIVOT(
  ANY_VALUE(reward_type) AS type,
  ANY_VALUE(reward_amount) AS amount
  FOR reward_key IN (%s)
)
""",
-- 自动扫描全表所有出现过的reward_N键，动态拼接PIVOT列清单
(
  SELECT STRING_AGG(DISTINCT CONCAT("'", reward_key, "'"), ORDER BY CAST(REPLACE(reward_key, 'reward_', '') AS INT64))
  FROM `your_project.your_dataset.your_table`,
  UNNEST(JSON_KEYS(json_data.rewards)) AS reward_key
)
);

使用说明 #

• 输出列名自动按照reward_N_type、reward_N_amount格式生成，和节点一一对应
• 若仅需单次解析固定JSON字符串而非表中存储的批量数据，可将FROM子句替换为SELECT PARSE_JSON('待解析的JSON字符串') AS json_data即可
• 代码中默认按reward后缀的数字数值排序生成列，若需要其他排序规则，修改STRING_AGG函数内的ORDER BY逻辑即可
• 若JSON存储为字符串格式，先通过PARSE_JSON函数转为JSON类型再做解析即可

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