如何在PostgreSQL中使用ARRAY_AGG函数将分组数据转为数组
作者：袖梨
2026-07-14
是，默认会将NULL值纳入数组，如[1, NULL, 3]，易导致UNNEST或JSON转换异常；需用FILTER(WHERE col IS NOT NULL)显式过滤或COALESCE处理。
ARRAY_AGG 会把 NULL 值也塞进数组里吗？
默认会。只要分组内某行的字段值为 NULL，它就会被包含在最终数组中，不是被跳过，也不是变成空字符串。这常导致后续用 UNNEST 或应用层解析时出错。
- 如果想排除
NULL，必须显式过滤：ARRAY_AGG(col) FILTER (WHERE col IS NOT NULL) - 如果字段是字符串类型，且希望用空字符串替代
NULL，得先用COALESCE(col, '')转换再聚合 - 注意：
FILTER子句不能和普通WHERE混用——前者作用于聚合输入行，后者作用于整个分组前的原始行
ORDER BY 在 ARRAY_AGG 里怎么写才生效？
必须写在函数内部，不能写在外部 GROUP BY 后的 ORDER BY。外部排序只影响结果集顺序，不影响数组内元素排列。
- 正确写法：
ARRAY_AGG(col ORDER BY col DESC) - 支持多字段排序：
ARRAY_AGG(col ORDER BY priority DESC, id ASC) - 如果排序字段可能为
NULL，记得加NULLS FIRST或NULLS LAST，否则不同 PostgreSQL 版本行为可能不一致（比如 15+ 默认NULLS LAST）
聚合字符串时，用 ARRAY_AGG 还是 STRING_AGG？
看下游怎么消费。如果应用代码或后续 SQL 需要按索引取值、做集合运算（如 @> 包含判断）、或保留重复项，就用 ARRAY_AGG；如果只是拼成逗号分隔的字符串用于展示或导出，STRING_AGG 更轻量、更易读。
-
ARRAY_AGG返回text[]类型，支持数组操作符（ANY、@>、&&等），但序列化开销略高 -
STRING_AGG可指定分隔符和ORDER BY，且对NULL默认忽略（不像ARRAY_AGG那样需要手动FILTER） - 别硬套：把
ARRAY_AGG结果再用ARRAY_TO_STRING转成字符串，不如直接用STRING_AGG
大表 GROUP BY + ARRAY_AGG 性能突然变慢，可能卡在哪？
不是聚合本身慢，而是内存不足触发了磁盘临时文件（temp_file），尤其当单个分组产出超大数组时（比如一个用户关联几千条标签）。PostgreSQL 默认 work_mem 往往不够。
- 查是否用了磁盘：
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ARRAY_AGG(...) GROUP BY ...，看是否有Temp File行 - 临时调优可设会话级
SET work_mem = '64MB'，但别全局改——容易耗尽内存 - 更可持续的做法是：提前用子查询限制每组最大数量，例如
ARRAY_AGG(sub.col) FILTER (WHERE sub.rn ，配合 <code>ROW_NUMBER() OVER (PARTITION BY ...)
