SQL如何将同分组的多行数据合并为JSON数组格式输出

你的需求完全可以实现，核心逻辑是按Name字段分组，将同分组下的所有Pets值聚合为标准JSON数组即可。
注意你目前掌握的写法存在逻辑问题：直接把GROUP_CONCAT拼接的逗号分隔字符串传入JSON_ARRAY，最终得到的是仅有一个元素的数组["Bird,Cow,Dog"]，不符合你要的多元素数组格式。

以下是主流数据库的最优实现方案： #

MySQL 5.7及以上版本（最常用场景） #

直接使用系统自带的JSON聚合函数JSON_ARRAYAGG，这是性能最高、写法最简洁的方案：

SELECT 
  Name,
  JSON_ARRAYAGG(Pets) AS Pets
FROM 你的表名
GROUP BY Name;

如果使用的是不支持JSON_ARRAYAGG的老旧MySQL版本，可以用修正后的函数拼接方案：

SELECT
  Name,
  CONCAT('[', GROUP_CONCAT(QUOTE(Pets) SEPARATOR ','), ']') AS Pets
FROM 你的表名
GROUP BY Name;

PostgreSQL 版本 #

使用JSON_AGG聚合函数实现：

SELECT
  Name,
  JSON_AGG(Pets) AS Pets
FROM 你的表名
GROUP BY Name;

SQL Server 版本 #

使用STRING_AGG配合JSON格式化实现：

SELECT
  Name,
  JSON_QUERY('[' + STRING_AGG(QUOTENAME(Pets, '"'), ',') + ']') AS Pets
FROM 你的表名
GROUP BY Name;

以上所有方案输出的结果都和你期望的格式完全匹配。

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