SQL如何对JSON数组字段按group分组聚合dollars与machines值

原查询无法生效的原因 #

• 字段类型认知错误：var2存储的是JSON数组，不是单个JSON对象，->>操作符仅能提取指定JSON路径的单个值，直接对根路径为数组的字段取group这类key，会直接返回null，根本拿不到数组内部对象的属性值。
• 缺少数组拆分步骤：部分记录的var2包含2个独立对象（比如John、William的记录同时属于group A和group B），不把数组拆成多行的话，数据库会把整条记录作为聚合单位，无法按数组内的group维度统计。
• 语法和逻辑错误：SELECT子句末尾多了一个冗余逗号，会直接触发语法报错；另外你用COUNT()统计machines，这个函数是计数非空值的个数，不是求字段总和，不符合统计machines总数的需求。

正确查询写法（MySQL 8.0+ 版本验证可用） #

核心思路是先通过JSON_TABLE函数把JSON数组打平为普通二维行数据，再做常规分组聚合，代码如下：

SELECT
    CONCAT('group ', j.group_name) AS `Group`,
    SUM(j.dollars) AS dollars,
    SUM(j.machines) AS machines
FROM DATASET,
JSON_TABLE(
    var2,
    '$[*]' COLUMNS(
        group_name VARCHAR(8) PATH '$.group',
        dollars INT PATH '$.dollars',
        machines INT PATH '$.machines'
    )
) AS j
GROUP BY j.group_name
ORDER BY j.group_name;

执行后返回结果和预期完全一致：

如果你用的是PostgreSQL，替换拆分数组的逻辑即可，核心聚合部分不变：

SELECT
    CONCAT('group ', j.group_name) AS `Group`,
    SUM(j.dollars) AS dollars,
    SUM(j.machines) AS machines
FROM DATASET
CROSS JOIN LATERAL jsonb_to_recordset(var2::jsonb) 
  AS j(group_name text, dollars int, machines int)
GROUP BY j.group_name
ORDER BY j.group_name;

SQL JSON操作学习资料推荐 #

• 对应数据库官方文档的JSON函数章节：这是优先级最高的参考资料，不同数据库的JSON操作函数语法存在差异，官方文档会覆盖所有JSON数组遍历、元素提取、结构转换的用法，配套可直接运行的示例，准确性最高。
• 《SQL必知必会（第5版）》：入门级读物，最后章节专门讲解了主流数据库对JSON类型的支持，覆盖常用的JSON查询、修改操作写法，适合新手快速上手。
• 《高性能MySQL（第4版）》：在数据类型章节详细说明了MySQL JSON类型的存储原理、常用操作、适用场景和性能优化注意事项，适合需要在生产环境使用JSON字段的开发者参考。

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