BigQuery如何对STRUCT或JSON字段中的记录进行聚合操作？

首先明确，主流SQL引擎基本都内置了专用的聚合函数来实现键值对聚合，不需要手动拼接字符串，不仅可读性更高，还能避免特殊字符转义、值类型错误等手动拼接的隐患。

优化实现方案 #

1. 生成JSON字符串的专用函数 #

不同SQL引擎的对应内置函数如下：

• BigQuery/PostgreSQL：直接用JSON_OBJECT_AGG(key, val)
• Spark SQL/Trino(Presto)：可以用TO_JSON(MAP_AGG(key, val))，高版本也支持JSON_OBJECT_AGG
• Hive：高版本支持JSON_OBJECT_AGG，低版本可以用STR_TO_MAP(CONCAT_WS(',', COLLECT_LIST(CONCAT(key, ':', val))))

优化后的代码写法如下，可读性和稳定性远高于手动拼接：

SELECT
    id,
    JSON_OBJECT_AGG(`key`, val) AS json_val
FROM (
    SELECT 1 AS id, "a" AS `key`, 100 AS val
    UNION ALL
    SELECT 1 AS id, "b" AS `key`, 200 AS val
    UNION ALL
    SELECT 1 AS id, "c" AS `key`, 300 AS val
    UNION ALL
    SELECT 2 AS id, "a" AS `key`, 400 AS val
    UNION ALL
    SELECT 2 AS id, "b" AS `key`, 500 AS val
    UNION ALL
    SELECT 2 AS id, "c" AS `key`, 600 AS val
    UNION ALL
    SELECT 3 AS id, "a" AS `key`, 700 AS val
) base
GROUP BY id

输出结果和你手动拼接的完全一致，不需要额外处理引号、分隔符，引擎会自动处理值类型适配、特殊字符转义逻辑。

2. 生成Map/STRUCT类型的方案 #

如果需要结构化的映射类型而非JSON字符串：

• 生成Map类型：大部分引擎支持MAP_AGG(key, val)，直接返回键值对映射的结构化类型，后续可以直接按键取值，不需要解析JSON
• 生成STRUCT类型：STRUCT属于固定键集合的结构，要求结构在查询编译期确定，所以没有通用的动态STRUCT聚合函数，如果你的键是提前固定的，可以用MAX(CASE WHEN key = 'a' THEN val END) AS a这类写法聚合后手动构造STRUCT。

原手动拼接方案的隐患 #

你原来的写法虽然能实现基础需求，但存在很多潜在问题：

• 键或者值中包含双引号、逗号等特殊字符时，生成的JSON会出现格式错误
• 字符串、布尔、NULL等类型的转换容易出问题，比如字符串类型的值没有加引号会生成非法JSON
• 可读性差，后续维护成本高

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