如何从PostgreSQL的CTE中展开两个或多个数组？

可行方案：通过LATERAL JOIN关联聚合结果与UNNEST #

可以利用LATERAL关键字将聚合后的CTE与UNNEST关联，既能正确引用CTE中的数组列，又能避免重复扫描原表：

WITH cte AS (
  SELECT type, array_agg(foo) AS foos, array_agg(bar) AS bars
  FROM my_table
  GROUP BY type
)
SELECT cte.type, t.foos, t.bars
FROM cte, LATERAL UNNEST(cte.foos, cte.bars) AS t(foos, bars);

也可以显式使用CROSS JOIN LATERAL，效果完全一致：

WITH cte AS (
  SELECT type, array_agg(foo) AS foos, array_agg(bar) AS bars
  FROM my_table
  GROUP BY type
)
SELECT cte.type, t.foos, t.bars
FROM cte
CROSS JOIN LATERAL UNNEST(cte.foos, cte.bars) AS t(foos, bars);

原语句报错原因 #

你之前的写法未将cte加入FROM子句，导致数据库无法识别cte的表引用。UNNEST无法直接访问CTE的列，必须通过JOIN（此处用LATERAL JOIN是因为UNNEST需要依赖CTE每行的数组值）关联才能正确引用。

替代方案：子查询直接展开 #

如果不需要CTE，也可以用子查询聚合后直接展开，同样无需重复扫描原表：

SELECT type, unnest(foos) AS foos, unnest(bars) AS bars
FROM (
  SELECT type, array_agg(foo) AS foos, array_agg(bar) AS bars
  FROM my_table
  GROUP BY type
) AS agg;

注：PostgreSQL 10+版本支持这种写法，但官方更推荐LATERAL JOIN的方式，逻辑更清晰，适配性更强。

内容的提问来源于stack exchange，提问作者Niel de Wet