如何将MySQL分组聚合查询结果转换为单个JSON对象

问题重现 #

你的数据表结构（假设表名为stock）：

---------+-----------
| type_id | A | B | A |
---------+-----------
|   stock | 1 | 2 | 2 |
---------+-----------

注：推测你实际要统计的是库存数值（比如列A、B、A对应库存字段，或表中存在stock列），目标是按type_id分组求和后转为JSON格式。

你尝试的SQL语句触发Error(1111): Invalid use of group function错误：

select 
    json_arrayagg( 
        JSON_ARRAY(
             'type_id', stock.type_id, 
             'stock', sum(stock.stock)
         )
    )
from stock group by type_id ;

错误原因 #

错误核心是聚合函数嵌套使用不当：sum()是分组聚合函数，不能直接放在JSON_ARRAY内部与json_arrayagg同层级执行，此时MySQL无法正确解析分组计算的执行顺序。

解决方案 #

方案1：子查询分组+外层JSON聚合（推荐） #

先通过子查询完成分组求和，再将结果转为JSON数组：

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'type_id', type_id,
        'stock', total_stock
    )
)
FROM (
    -- 先完成分组求和，得到每个type_id的统计结果
    SELECT type_id, SUM(stock) AS total_stock
    FROM stock
    GROUP BY type_id
) AS grouped_stock;

此方法先让分组求和逻辑独立执行，再将结果逐行转为JSON对象，最后聚合为一个JSON数组，完全规避函数嵌套冲突。

方案2：直接生成单条JSON对象（适用于单分组场景） #

如果你的数据只有单个type_id分组（比如示例中的stock），可以直接生成单个JSON对象，无需数组聚合：

SELECT JSON_OBJECT(
    'type_id', type_id,
    'stock', SUM(stock)
)
FROM stock
GROUP BY type_id;

补充说明 #

• 去掉json_arrayagg后返回多行结果：这是分组查询的默认行为，每行对应一个type_id的统计值
• 去掉group by后结果错误：此时MySQL会将全表视为一个分组，求和结果是全表库存总和，且type_id会随机取某一行的值，不符合需求

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