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。

• 用 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 处理逻辑 —— 它们往往在测试数据少时不出问题，一上生产就崩，而且错误信息不够直白。