Postgresql 11 - Order by integer array index

Question

I want to list my products in the database according to their id order in the array

My array

'{3,2,1}'::int[]

For example

SELECT id FROM product WHERE id = ANY ('{3,2,1}'::int[]);

This query gets the products with ordered by product id

|id|
|1 |
|2 |
|3 |

But i want to list my products ordered by index of the array ids. Like this :

|id|
|3 |
|2 |
|1 |

Is it possible to do this ? How can i do ?

Answer 1

You can use array_position():

ORDER BY array_position('{3,2,1}'::int[], id)

If you didn't want to repeat the array twice:

select p.id
from product p join
     (values ('{3,2,1}'::int[])) v(ar)
     on p.id = any(v.ar)
order by array_position(v.ar, p.id);

Answer 2

You can unnest() the array with option WITH ORDINALITY to keep track of the index of each element, join it with the table and use the index as ORDER BY criteria:

SELECT p.id
FROM product AS p
INNER JOIN unnest('{3,2,1}'::int[]) WITH ORDINALITY AS a(id, nr) 
    ON p.id = a.id
ORDER BY a.nr;