Preserve the order of items in array when doing JOIN in Postgres

Question

Say I have two tables: posts and embeds. Every post in posts may have a number of embeds associated with it in particular order which must be preserved. This query seems to work, producing the result shown below as «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;

As you can see, the order is embeds is preserved. But then I created a view post_embeds and tried to query this view:

SELECT * FROM post_embeds WHERE pid=1

The order of embeds was gone. I suspect my first query is not guaranteed to preserve order after all and it did only due to coincidence.

How to correctly query some posts with associated embeds, preserving the order?

Answer 1

Use unnest() WITH ORDINALITY and the ordinality in an aggregate:

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;

Read in the documentation:

If the WITH ORDINALITY clause is specified, an additional column of type bigint will be added to the function result columns. This column numbers the rows of the function result set, starting from 1.