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":
id | serial_no |
---|---|
1 | PART_A01 |
2 | PART_A02 |
3 | PART_A03 |
4 | PART_A04 |
5 | PART_A05 |
6 | PART_A06 |
Table "process.assemblies":
id | assembly_order |
---|---|
1 | {3, 6, 1} |
2 | {5, 2, 4} |
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:
output |
---|
PART_A03 |
PART_A06 |
PART_A01 |
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?
2
Answers
I’m sure you don’t want to hear this now, but this is not the right way to organize this data. There should be a
parts
table, anassembly
table, and anassembly_order
table that links the two, with one step per row. Like:process.assembly_order
Now you can do
Given the schema you currently have, you can:
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.If you want all ids, you can use the whole table in step 1 instead a selection of it.
Output:
Check the demo here.