Question 174377 in PostgreSQL Question posted in
The official documentation can be found here.

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":

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?

Tags:

2

Answers


  1. 0

    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, an assembly table, and an assembly_order table that links the two, with one step per row. Like:

    process.assembly_order

    part step assembly
    1 1 3
    1 2 6
    1 3 1
    2 1 5
    2 2 2
    2 3 4

    Now you can do

    SELECT part,assembly FROM parts,assembly_order WHERE assembly_order.part = parts.id ORDER BY part, step;
    Login or Signup to reply.
  2. 0

    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:

    serial_no
    PART_A03
    PART_A06
    PART_A01

    Check the demo here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search