在BigQuery中按分组合并JSON字符串并汇总对应值

需求说明 #

给定包含年份和JSON字符串的表数据：

2023, {"hen":4, "owl":3}
2023, {"crow":4, "owl":2}
2022, {"owl":6, "crow":2}
2022, {"hen":5}
2021, {"hen":2, "crow":1}

需要按year字段分组，合并每组内的JSON字符串，并对相同键对应的数值求和，最终得到：

2023, {"hen":4, "owl":5, "crow":4}
2022, {"hen":5, "owl":6, "crow":2}
2021, {"hen":2, "crow":1}

解决方案SQL #

可以通过拆分JSON键值对、分组求和、重新聚合JSON三个步骤实现，以下是可直接运行的代码：

WITH sample_data AS (
  -- 模拟示例数据，替换为你的实际表名和字段名
  SELECT '2023' AS year, '{"hen":4, "owl":3}' AS json_str UNION ALL
  SELECT '2023' AS year, '{"crow":4, "owl":2}' AS json_str UNION ALL
  SELECT '2022' AS year, '{"owl":6, "crow":2}' AS json_str UNION ALL
  SELECT '2022' AS year, '{"hen":5}' AS json_str UNION ALL
  SELECT '2021' AS year, '{"hen":2, "crow":1}' AS json_str
)
SELECT
  year,
  TO_JSON_STRING(ARRAY_AGG(STRUCT(key, value) ORDER BY key)) AS merged_json
FROM (
  -- 第一步：拆分JSON键值对并按年份+键求和
  SELECT
    year,
    key,
    SUM(CAST(JSON_EXTRACT_SCALAR(json_str, CONCAT('$.', key)) AS INT64)) AS value
  FROM sample_data,
  UNNEST(JSON_OBJECT_KEYS(json_str)) AS key
  GROUP BY year, key
)
GROUP BY year
ORDER BY year DESC;

代码说明 #

1. 拆分JSON键值对：使用UNNEST(JSON_OBJECT_KEYS(json_str))提取每个JSON字符串中的所有键，再通过JSON_EXTRACT_SCALAR获取对应的值并转为整数类型。
2. 分组求和：子查询中按year和key分组，对每个键的数值求和，得到每个年份下各键的总数值。
3. 聚合为JSON：外层查询通过ARRAY_AGG将每个年份的键值对聚合成数组，再用TO_JSON_STRING转换为JSON字符串，得到最终合并结果。

注意事项 #

• 如果JSON中的值是浮点数，将CAST(...) AS INT64替换为CAST(...) AS FLOAT64即可。
• 若实际表中JSON字段是JSON类型而非字符串，可直接使用json_col替代json_str，无需额外处理。

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