PostgreSQL 如何用 LATERAL + unnest 处理数组展开聚合

直接用 unnest() 在 select 列表中报错，因 postgresql 要求集合返回函数必须在 LATERAL 子查询或 FROM 项中执行，以明确行生成上下文；LATERAL 支持逐行展开并引用左侧行，而普通 SELECT 无此语义。

为什么直接用 unnest() 在 SELECT 里会报错“set-returning function called in context that cannot accept a set”

因为 PostgreSQL 不允许在普通 SELECT 列表中直接调用返回多行的函数（如 unnest()），除非它被包裹在 LATERAL 子查询中，或作为独立的 FROM 项。常见错误是写成：

SELECT id, unnest(tags) FROM posts;

——这在 9.6+ 会失败。

根本原因是：SQL 标准要求集合返回函数必须有明确的“行生成上下文”，LATERAL 就是告诉优化器“这个函数依赖当前行，且每行可展开出 0–N 行”。

• 不加 LATERAL，PostgreSQL 无法确定 unnest() 的执行时机和关联粒度
• LATERAL 允许右侧子查询引用左侧的列，形成逐行展开语义
• 即使数组为空（NULL 或 {}），LATERAL + unnest() 默认跳过该行；需加 LEFT JOIN 才保留空数组行

如何用 LATERAL + unnest() 实现带聚合的数组展开（比如统计每个 tag 出现次数）

关键在于把 unnest() 放进 FROM 子句，并用 LATERAL 绑定到原表，再对展开后的结果做 GROUP BY。

例如统计所有文章中的标签频次：

SELECT t.tag, COUNT(*) AS cnt FROM posts p,      LATERAL unnest(p.tags) AS t(tag) GROUP BY t.tag ORDER BY cnt DESC;

• p, 是旧式隐式 CROSS JOIN 写法，等价于 JOIN LATERAL ... ON TRUE；更推荐显式写法：FROM posts p CROSS JOIN LATERAL unnest(p.tags) AS t(tag)
• AS t(tag) 必须显式命名输出列，否则聚合时无法引用（如 unnest(p.tags) AS t 会让列名变成 unnest，易混淆）
• 如果某行 p.tags 是 NULL 或空数组，该行不会出现在结果中；要保留，改用 LEFT JOIN LATERAL

怎么保留原表字段并同时展开数组（比如查文章标题和每个 tag）

这是最常见需求：既要原始记录信息，又要每条展开后的 tag 行。只需在 SELECT 中混用原表字段和 LATERAL 输出列即可。

SELECT p.id, p.title, t.tag FROM posts p          CROSS JOIN LATERAL unnest(p.tags) AS t(tag);

• 用 CROSS JOIN LATERAL 更清晰，语义上强调“每行展开为多行”
• 若某篇文章 tags = NULL，整行被丢弃；需要显示 id、title 但 tag 为 NULL，则改用：

  SELECT p.id, p.title, t.tag FROM posts p          LEFT JOIN LATERAL unnest(p.tags) AS t(tag) ON TRUE;

• 注意 ON TRUE 是必须的——LEFT JOIN LATERAL 要求有 ON 条件，哪怕恒真

性能和兼容性要注意什么

LATERAL + unnest() 在 PostgreSQL 9.3+ 可用，但高并发或大数组场景下容易成为瓶颈。

• 数组越长，展开后中间行数越多，GROUP BY 或 JOIN 开销指数上升；单行含上千元素时需警惕内存与排序压力
• 没有索引能加速 unnest() 本身，但可在展开后对 t.tag 建普通索引（仅对后续过滤有效）
• 若只是检查某个 tag 是否存在，别展开——改用 @> Array['xxx'] 或 ARRAY['xxx'] && tags，快一个数量级
• 从 jsONB 数组展开？先用 jsonb_array_elements_text() 替代 unnest()，语法类似但类型不同

真正麻烦的是嵌套数组或需要保留原始数组下标——这时得用 unnest(ARRAY, ARRAY) 双参数形式或 WITH ORDINALITY，不是简单加个 LATERAL 就能搞定的。