sql - PostgreSQL JOIN with array type with array elements order, how to implement?

Question

I have two tables in database:

CREATE TABLE items(
 id SERIAL PRIMARY KEY,
 ... some other fields
);

This table contains come data row with unique ID.

CREATE TABLE some_chosen_data_in_order(
 id SERIAL PRIMARY KEY,
 id_items INTEGER[],
);

This table contains array type field. Each row contains values of IDs from table items in specific order. For example: {2,4,233,5}.

Now, I want to get data from table items for chosen row from table some_chosen_data_in_order with order for elements in array type.

My attempt was JOIN:

SELECT I.* FROM items AS I 
JOIN some_chosen_data_in_order AS S ON I.id = ANY(S.id_items) WHERE S.id = ?

Second attempt was subquery like:

SELECT I.* FROM items AS I 
WHERE I.id = ANY 
(ARRAY[SELECT S.id_items FROM some_chosen_data_in_order  WHERE id = ?])

But none of them keep IDs in the same order as in array field. Could you help me, how to get data from items table with correspond with array IDs order from some_chosen_data_in_order table for specific row?

Answer 1

SELECT t.*
FROM unnest(ARRAY[1,2,3,2,3,5]) item_id
LEFT JOIN items t on t.id=item_id

The above query select items from items table with ids: 1,2,3,2,3,5 in that order.