PostgreSQL中如何在GROUP BY子句中创建JSON列聚合数据？

原始数据集 #

NAME-----T----V

Adam-----90---1
Adam-----91---2
Adam-----93---3
Matt-----90---0
Matt-----91---9
Matt-----93---8

期望输出 #

Adam, [{"90": 1}, {"91": 2}, {"93": 3}]
Matt, [{"90": 0}, {"91": 9}, {"93": 8}]

不同数据库的实现方式略有差异，以下是主流数据库的解决方案：

PostgreSQL #

利用json_agg和json_build_object函数实现JSON数组聚合：

SELECT
    CONCAT(NAME, ', ', json_agg(json_build_object(T::text, V))) AS final_result
FROM your_table
GROUP BY NAME;

• json_build_object(T::text, V)：将每行的T（转为字符串）和V构建为单个JSON键值对对象
• json_agg()：将同一NAME下的所有JSON对象聚合为一个JSON数组
• CONCAT()：将NAME和聚合结果拼接成示例格式的字符串

MySQL 5.7+ #

使用JSON_ARRAYAGG和JSON_OBJECT函数：

SELECT
    CONCAT(NAME, ', ', JSON_UNQUOTE(JSON_ARRAYAGG(JSON_OBJECT(T, V)))) AS final_result
FROM your_table
GROUP BY NAME;

• JSON_OBJECT(T, V)：生成单个JSON键值对
• JSON_ARRAYAGG()：聚合为JSON数组
• JSON_UNQUOTE()：去除JSON数组的外层引号，再用CONCAT()拼接成目标格式

SQL Server 2016+ #

通过STRING_AGG拼接JSON字符串，再组合成数组：

SELECT
    CONCAT(NAME, ', [', STRING_AGG(CONCAT('{"', T, '": ', V, '}'), ', '), ']') AS final_result
FROM your_table
GROUP BY NAME;

• CONCAT('{"', T, '": ', V, '}')：手动构造单个JSON键值对字符串
• STRING_AGG()：将同一NAME下的所有JSON字符串用逗号分隔拼接
• 外层CONCAT()：组合NAME和数组格式的最终字符串

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