如何在SQL中将聚合结果直接转为JSON格式_使用JSON

json_agg适用于postgresql 12+，将多行聚合成json数组，需配合row_to_json或json_build_object使用；sql server用for json；mysql需group_concat模拟；跨库推荐应用层序列化。

如何在sql中将聚合结果直接转为json格式_使用json_agg或for json

JSON_AGG 适合 PostgreSQL 和标准 SQL 场景

PostgreSQL 12+ 原生支持 JSON_AGG，它把多行结果聚合成一个 JSON 数组。注意它只接受单个表达式（比如整行、某个字段或 ROW_TO_JSON()），不能直接写多个字段——否则报错 function json_agg(record) does not exist。

常见写法是配合 ROW_TO_JSON() 或子查询：

SELECT JSON_AGG(ROW_TO_JSON(t)) FROM (SELECT id, name, age FROM users WHERE age > 30) t;

如果只想聚合部分字段且控制键名，得用 JSON_BUILD_OBJECT：

SELECT JSON_AGG(JSON_BUILD_OBJECT('uid', id, 'label', name)) FROM users;

容易踩的坑：

JSON_AGG(NULL) 返回 [null]，不是 []；需要先 FILTER (WHERE ... IS NOT NULL)
空结果集返回 NULL，不是空数组；可用 COALESCE(JSON_AGG(...), '[]'::json) 补默认值
字段含特殊字符（如点号、斜杠）会导致 key 不合法；需手动转义或改用字符串拼接

FOR JSON 是 SQL Server 的专属语法

SQL Server 2016+ 提供 FOR JSON，但它不是函数，而是查询末尾的输出修饰子句，语法位置固定、不可嵌套调用。最常用的是 FOR JSON AUTO 和 FOR JSON PATH。

AUTO 自动推导结构，但列名带点（如 u.name）会生成嵌套对象；PATH 更可控，支持别名中用点号定义层级：

chartjs-reporter

此技能用于在用户需要将结构化数据（查询结果、CSV 摘要、JSON 记录或 Python 字典/列表）转换为独立的 HTML 页面时使用。

下载

SELECT u.id AS 'user.id', u.name AS 'user.name', a.city AS 'address.city' 
FROM users u 
JOIN addresses a ON u.id = a.user_id 
FOR JSON PATH, ROOT('result');

关键限制：

不能在子查询或 CTE 中直接用 FOR JSON；必须是顶层 SELECT
聚合函数（如 COUNT）和 FOR JSON 共存时，必须配 GROUP BY，否则报错 is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
中文等 Unicode 字符默认被编码为 uXXXX；加 WITHOUT_ARRAY_WRAPPER 可去掉外层方括号，但仅限单行结果

MySQL 没有 JSON_AGG，得用 JSON_OBJECTAGG + GROUP_CONCAT 曲线救国

MySQL 5.7+ 有 JSON_OBJECTAGG(key, value)，但它只能做“键值对”聚合（类似 Map），不能直接把整行变 JSON 对象。想实现类似 JSON_AGG 效果，得组合 JSON_OBJECT 和 GROUP_CONCAT：

SELECT CONCAT('[', GROUP_CONCAT(
  JSON_OBJECT('id', id, 'name', name, 'score', score)
  SEPARATOR ','
), ']') AS json_array
FROM students WHERE class = 'A';

问题很明显：一旦某字段含单引号、换行或反斜杠，GROUP_CONCAT 产出的 JSON 就非法。必须提前转义：

用 REPLACE(REPLACE(..., '\', '\\'), '"', '\"') 处理双引号
数值字段不用处理，但字符串字段建议统一过 JSON_QUOTE()
GROUP_CONCAT 默认长度上限 1024，超长会被截断；需临时设 SET SESSION group_concat_max_len = 1000000;