如何在PostgreSQL中用UNNEST和JOIN生成数组列？

在PostgreSQL中没有Oracle类似的NEST函数，但可以通过array_agg()函数实现将分组后的行数据重新聚合为数组的需求，核心是要保留原数组的顺序，否则聚合结果会不符合预期。

方法一：基于你已有的UNNEST查询改造 #

在拆分数组并关联名称表后，结合原数组的下标排序来聚合名称：

SELECT 
  ww.widget_id,
  array_agg(n.naam ORDER BY ww.name_id_pos) AS names
FROM (
  SELECT 
    widget_id,
    unnest(name_ids) AS name_id,
    -- 获取原数组中元素的位置，用于后续排序保证顺序
    generate_subscripts(name_ids, 1) AS name_id_pos
  FROM widgets
) ww
INNER JOIN names n ON ww.name_id = n.name_id
GROUP BY ww.widget_id
ORDER BY ww.widget_id;

方法二：使用横向连接（JOIN LATERAL）+ UNNEST WITH ORDINALITY #

PostgreSQL 9.4及以上版本支持unnest ... WITH ORDINALITY语法，可直接获取数组元素及其在原数组中的位置，写法更简洁：

SELECT 
  w.widget_id,
  -- 按原数组的位置排序后聚合
  array_agg(n.naam ORDER BY u.ordinality) AS names
FROM widgets w
-- 横向连接拆分数组，同时获取元素位置
JOIN LATERAL unnest(w.name_ids) WITH ORDINALITY u(name_id) ON true
JOIN names n ON u.name_id = n.name_id
GROUP BY w.widget_id
ORDER BY w.widget_id;

关键注意点 #

如果省略ORDER BY子句，array_agg()会默认按聚合字段（此处为naam）的字母顺序排列，导致widget_id=2的结果变成{Bo,Che}，不符合预期。通过generate_subscripts或ordinality获取原数组的位置并排序，才能保证结果数组的顺序和原name_ids完全一致。

执行结果 #

上述两种方法都会得到你期望的输出：

widget_id|names      |
---------+-----------+
        1|{Ad,Bo,Che}|
        2|{Che,Bo}   |

内容的提问来源于stack exchange，提问作者Roland Kwee