PostgreSQL JOIN with array type with array elements order, how to implement?
SELECT t.*FROM unnest(ARRAY[1,2,3,2,3,5]) item_idLEFT JOIN items t on t.id=item_idThe above query select items from items table with ids: 1,2,3,2,3,5 in that order.
Probably normalizing your table would be the best advice I can give you.
The int_array contrib module has an idx function that will give you the int's index position in the array. Also there is an idx function on the snippets wiki that works for array's of any data types.
SELECT i.*, idx(id_items, i.id) AS idxFROM some_chosen_data_in_order sJOIN items i ON i.id = ANY(s.id_items)ORDER BY idx(id_items, i.id)
select distinct on (some_chosen_data_in_order.id) some_chosen_data_in_order.*, array_to_json( array_agg(row_to_json( items)) over ( partition by some_chosen_data_in_order.id ))from some_chosen_data_in_order left join items on items.id = any (some_chosen_data_in_order.id_items)