PostgreSQL: Select data from another table by elements of an array and then display in order

I have a table with a collection of parts. These parts have an id and a serial number provided by the manufacturer. Parts are put together to build an assembly and the order which the parts are assembled matters. When this assembly is made, a new part is created which goes into a new table with an array which points to the first table with the ids in order. In other words:

Table "process.parts":

Table "process.assemblies":

I need to select an assembly from Table 2 and display it in order of assembly. In other words, selection of assembly 1 should display:

The SQL command I am using to select the parts is as follows:

SELECT id, serial_no
FROM process.parts
WHERE id IN (SELECT unnest(assembly_order) FROM process.assemblies WHERE id = 1);

This script does not preserve the order from the array. Is there a way to modify this to loop through the elements of the array to preserve order?

Given the schema you currently have, you can:

• select your needed ids
• unnest the corresponding "assembly_order" field
• join with the parts table on matching ids

In order to keep the order of the array elements unaltered, you use the WITH ORDINALITY clause, that allows you to retrieve the index alongside your unnested array.

SELECT cte.id
FROM       (SELECT assembly_order FROM assemblies WHERE id = 1) a 
CROSS JOIN UNNEST(a.assembly_order) WITH ORDINALITY cte(id, idx) 
INNER JOIN parts p
        ON p.id = cte.id
ORDER BY cte.idx

If you want all ids, you can use the whole table in step 1 instead a selection of it.

Output:

Check the demo here.