JSON_ARRAYAGG / JSON_OBJECTAGG 在 PostgreSQL 中的高级用法

JSON_ARRAYAGG 避免 NULL 导致结果为 NULL：用 FILTER (WHERE col IS NOT NULL) 过滤，或 COALESCE(JSON_ARRAYAGG(col), ‘[]’::json)；JSON_OBJECTAGG 要求 key 唯一，需提前去重或聚合 value；嵌套构造推荐 LEFT JOIN + GROUP BY + COALESCE；大数据量时防 OOM，应限流、分片或交由应用层处理。

JSON_ARRAYAGG 怎么避免 NULL 值导致整个聚合结果为 NULL

PostgreSQL 中 JSON_ARRAYAGG 遇到输入行含 NULL 值时，本身不会失败，但若聚合字段全为 NULL（比如 SELECT JSON_ARRAYAGG(NULL)），结果就是 NULL，而非空数组 []。这常导致上层应用解析出错或逻辑断裂。

• 用 COALESCE 包裹聚合结果：COALESCE(JSON_ARRAYAGG(col), '[]'::json)
• 更稳妥的做法是过滤掉 NULL 行：JSON_ARRAYAGG(col) FILTER (WHERE col IS NOT NULL)
• 注意：如果 col 是 JSON 类型字段且值为 json 'null'（即 JSON null 字面量），它会被正常加入数组；只有 SQL NULL 才被忽略（当用 FILTER 时）

JSON_OBJECTAGG 的 key 冲突怎么处理

JSON_OBJECTAGG(key, value) 要求 key 必须唯一，否则直接报错：ERROR: cannot construct json object with duplicate keys。这不是警告，是硬性限制。

• 提前去重：用 DISTINCT ON (key) 或子查询 + ROW_NUMBER() OVER (PARTITION BY key ORDER BY ...) 保留每组 key 的一条记录
• 合并 value：先用 STRING_AGG 或 JSON_AGG 把重复 key 对应的多个 value 聚合成数组，再传给 JSON_OBJECTAGG，例如：

  SELECT JSON_OBJECTAGG(key, vals) FROM (  SELECT key, JSON_AGG(value) AS vals  FROM t  GROUP BY key) s;

• 不推荐用 GROUP BY 后直接 JSON_OBJECTAGG —— 若没显式控制 key 唯一性，运行时仍可能崩

嵌套 JSON 构造：JSON_ARRAYAGG 里套 JSON_OBJECTAGG 怎么写才干净

常见需求是把“一对多”关系转成嵌套 JSON：比如每个用户带一个订单列表。这时要避免在子查询里多次扫描、或写出难以维护的多层 LATERAL。

本文档主要讲述的是fortran语言语法；FORTRAN是世界上最早出现的高级编程语言，是工程界最常用的编程语言，它在科学计算中（如航空航天、地质勘探、天气预报和建筑工程等领域）发挥着极其重要的作用。经过40多年的发展，伴随着FORTRAN语言多次版本的更新及相应开发系统的出现，其功能不断完善，最新版本的开发系统几乎具备了VC、VB的所有特点，如图形界面编程、数据库等。希望本文档会给有需要的朋友带来帮助；感兴趣的朋友可以过来看看

• 用 LATERAL 关联子查询最直观：

  SELECT u.id, u.name,  (SELECT JSON_AGG(JSON_BUILD_OBJECT('id', o.id, 'amount', o.amount))     FROM orders o WHERE o.user_id = u.id) AS orders_jsonFROM users u;

• 更高效的方式是先 JOIN 再分组：SELECT u.id, u.name, JSON_AGG(JSON_BUILD_OBJECT('id', o.id, 'amount', o.amount)) FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name
• 注意 LEFT JOIN 后若某用户无订单，JSON_AGG 返回 NULL，需用 COALESCE(..., '[]'::json) 统一为空数组
• 别用 JSON_OBJECTAGG 替代 JSON_BUILD_OBJECT 来构造单个对象——前者专为映射设计，后者才是造对象字面量的正途

性能陷阱：大结果集下 JSON_ARRAYAGG 可能 OOM 或极慢

当聚合数万行生成一个巨型 JSON 数组时，PostgreSQL 会把整个结果暂存在内存中，容易触发 out of memory 或显著拖慢查询。

• 加 LIMIT 控制返回条数，尤其在调试或分页场景下：JSON_ARRAYAGG(...) LIMIT 100（注意：LIMIT 作用于聚合前的行）
• 用游标或分批查询替代单次大聚合，例如按时间范围切片：WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'
• 确认是否真需要服务端拼 JSON —— 有时把扁平结果集交给应用层组装，反而更可控、易调试、低
• JSONB 类型字段存大 JSON 时，写入/读取开销也高，聚合前考虑是否可先用 TO_JSONB 提前转换，避免运行时反复解析

实际用的时候，最容易被忽略的是 key 唯一性校验和 NULL 处理逻辑 —— 它们往往在测试数据少时不出问题，一上生产就崩，而且错误信息不够直白。