如何在PostgreSQL中使用JSON_AGG函数把分组结果直接导出为JSON

JSON_AGG是聚合函数，仅生成JSON值而非导出文件；需配合psql命令（如-t -P format=unaligned）或外部工具落地为纯JSON文件，且须注意分组、NULL处理、排序及大结果集内存风险。

JSON_AGG 本质是聚合函数，不是导出命令

直接用 JSON_AGG 不会“导出文件”，它只在查询结果中生成一个 JSON 值（类型为 json 或 jsonb）。想落地为文件，得靠客户端或外部工具配合。PostgreSQL 本身不提供“一键导出 JSON 文件”的 SQL 语句。

常见误操作是写 SELECT JSON_AGG(row_to_json(t)) FROM table t GROUP BY ... 后以为结果能自动保存成文件——实际只是返回一列 JSON 字符串，长度超限还可能被截断（尤其在 psql 中默认 pager 或 max_width 限制）。

• 用 psql 时加 t o output.json 才能重定向输出（注意：输出是带列名和分隔符的文本，不是纯 JSON）
• 要纯 JSON，必须关掉字段头和分隔符：t on + pset format unaligned + pset tuples_only on
• 若结果含换行或特殊字符，psql -c "..." > output.json 更可靠，但需确保查询只返回单个 JSON 值（避免多行）

GROUP BY + JSON_AGG 的典型写法和易错点

常见需求是按某字段分组，把每组的记录聚合成一个 JSON 数组。关键在于：先构造每行的结构（用 ROW_TO_JSON 或 TO_JSONB），再聚合。

错误示范：SELECT group_id, JSON_AGG(*) FROM t GROUP BY group_id —— * 在聚合里非法，语法直接报错。

• 正确做法是显式构造对象：SELECT group_id, JSON_AGG( JSON_BUILD_OBJECT('id', id, 'name', name, 'value', value) ) AS items FROM t GROUP BY group_id
• 若原表字段多且不想逐个列名，可用 TO_JSONB(t)（t 是表别名），但要注意：NULL 字段会被忽略，且嵌套关系丢失
• JSON_AGG 默认保留 NULL 行；如需过滤，加 WHERE field IS NOT NULL 在子查询或 WHERE 子句中
• 排序影响结果一致性：未加 ORDER BY 时，聚合顺序不确定。生产环境务必显式排序：JSON_AGG(... ORDER BY id)

大结果集下 JSON_AGG 的内存与性能风险

JSON_AGG 会把整组数据加载进内存构建 JSON 文本，当单组记录数超几万或字段含大文本/二进制时，极易触发 out of memory 或显著拖慢查询。

• 用 EXPLAIN ANALYZE 观察 Aggregate 节点的 Memory Usage，超过 100MB 就该警惕
• 替代方案：改用游标分批处理，或用 JSONB_AGG（比 JSON_AGG 略快，且支持去重、合并等后续操作）
• 导出超大数据时，别硬扛——用 COPY (SELECT ...) TO '/path' WITH (FORMAT CSV) 导出 CSV，再用 Python/Node.js 转 JSON，更稳

psql 导出纯 JSON 文件的最小可行命令

假设你要按 category 分组，把每组所有记录转成 JSON 数组并存为 data.json：

psql -d mydb -t -P format=unaligned -c "SELECT JSON_AGG(  JSON_BUILD_OBJECT(    'id', id,    'title', title,    'score', score  ) ORDER BY id) FROM items GROUP BY categoryLIMIT 1;" > data.json

注意：LIMIT 1 是因为上面查询会返回多行（每组一行），而 data.json 预期是单个 JSON 值。真要多组，得在外层再包一层 JSON_AGG 或用应用层处理。

真正麻烦的从来不是语法，而是分组边界是否清晰、NULL 怎么处理、以及导出时要不要美化缩进——这些细节不提前想好，生成的 JSON 很可能下游解析失败。