SQL中如何将分组结果转换为JSON格式？-SQL-PHP中文网

postgresql用json_agg()直接聚合为json数组，mysql需json_object()与json_arrayagg()组合，sql server依赖for json或string_agg()拼接；跨库无兼容方案，小数据量且结构固定时才建议数据库层处理。

sql中如何将分组结果转换为json格式？

PostgreSQL 的 `json_agg()` 是最直接的方案

PostgreSQL 原生支持将分组后的行聚合成 JSON 数组，json_agg() 就是为此设计的聚合函数。它会把每个分组内的所有行（或指定字段）打包成一个 JSON 数组，不需要额外转换逻辑。

常见错误是误用 row_to_json() 或嵌套子查询，导致结果多一层包装或性能下降。正确做法是直接在 GROUP BY 后对目标字段或整个行调用 json_agg()：

SELECT user_id, json_agg(ROW(name, email)) AS contacts
FROM users
GROUP BY user_id;

注意：ROW() 生成匿名记录，若需带键名，改用 json_build_object() 配合 json_agg()：

SELECT user_id, json_agg(json_build_object('name', name, 'email', email)) AS contacts
FROM users
GROUP BY user_id;

如果字段含 NULL，json_agg() 默认保留 null 值；如需过滤，加 WHERE name IS NOT NULL
聚合前未排序时，数组顺序不确定；需稳定顺序请加 ORDER BY 子句（PostgreSQL 9.5+ 支持）
大数据量分组时，json_agg() 内存占用随组内行数线性增长，单组超万行建议拆分或加 LIMIT

MySQL 8.0+ 必须用 `JSON_OBJECTAGG()` 和 `JSON_ARRAYAGG()` 组合

MySQL 没有等价于 json_agg(ROW(...)) 的一键函数，必须区分“键值对聚合”和“数组聚合”两种场景。多数分组转 JSON 实际需要的是数组，但 JSON_ARRAYAGG() 只接受单字段或表达式，不能直接传入多列结构。

典型错误是试图写 JSON_ARRAYAGG(name, email)——这会报错 Incorrect number of arguments for FUNCTION JSON_ARRAYAGG。正确路径是先用 JSON_OBJECT() 构造单个对象，再用 JSON_ARRAYAGG() 聚合：

SELECT user_id, JSON_ARRAYAGG(JSON_OBJECT('name', name, 'email', email)) AS contacts
FROM users
GROUP BY user_id;

JSON_OBJECT() 中键名必须是字符串字面量，不能是变量或字段名（否则报错 Invalid JSON text）
若某组无匹配行，JSON_ARRAYAGG() 返回 NULL，不是空数组 []；需空数组请用 COALESCE(..., JSON_ARRAY())
MySQL 对 JSON 字符串长度有限制（默认 group_concat_max_len=1024），大结果可能被截断，需提前设高： SET SESSION group_concat_max_len = 1000000;

SQL Server 的 `FOR JSON` 语法不支持直接分组聚合

SQL Server 的 FOR JSON 是查询级特性，不能作为聚合函数嵌套在 GROUP BY 中使用。常见误区是写 SELECT ..., (SELECT ... FOR JSON PATH) FROM ... GROUP BY ...，这会导致“无法对包含 FOR JSON 的子查询进行聚合”错误。

Json Schema Toolkit

使用 JSON Schema 验证 JSON 数据，从示例 JSON 生成 schema，并将其转换为 TypeScript 接口、Python 数据类或 Markdown 文档。

下载

可行解法是用 CROSS APPLY + 子查询模拟分组内 JSON 构建：

SELECT u.user_id, j.contacts
FROM (SELECT DISTINCT user_id FROM users) u
CROSS APPLY (
  SELECT name, email
  FROM users u2
  WHERE u2.user_id = u.user_id
  FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) j(contacts);

但这样得到的是 JSON 字符串而非原生 JSON 类型（SQL Server 2016+ 才支持 ISJSON() 校验）。更稳妥的做法是用 STRING_AGG() 拼接后手动补括号（仅适用于简单结构）：

SELECT user_id,
  '[' + STRING_AGG(
    '{"name":"' + REPLACE(name, '"', '\"') + '","email":"' + REPLACE(email, '"', '\"') + '"}',
    ','
  ) + ']' AS contacts
FROM users
GROUP BY user_id;