在Postgres中进行JOIN时保留数组中项目的顺序

假设我有两个表:帖子和嵌入.帖子中的每个帖子都可能有一些与之关联的嵌入,特定顺序必须保留.此查询似乎有效,产生如下所示的结果«Post_embeds»:

SELECT p.id AS pid, array_agg(e.code) AS code
  FROM posts p, unnest(p.embeds) embed_id
  JOIN embeds e ON e.id = embed_id
GROUP BY p.id;

如您所见,订单嵌入是保留的.但后来我创建了一个视图post_embeds并尝试查询此视图:

SELECT * FROM post_embeds WHERE pid=1

嵌入的顺序消失了.我怀疑我的第一个查询并不能保证总是保留顺序,而且只是因为巧合.

如何正确查询一些关联嵌入的帖子,保留订单？

使用unnest() WITH ORDINALITY和聚合中的正常:

SELECT p.id AS pid, array_agg(e.code ORDER BY ordinality) AS code
  FROM posts p, unnest(p.embeds) WITH ORDINALITY AS u(embed_id, ordinality)
  JOIN embeds e ON e.id = embed_id
GROUP BY p.id;

阅读文档:

如果指定了WITH ORDINALITY子句​​,则会在函数结果列中添加bigint类型的附加列.此列对函数结果集的行进行编号,从1开始.