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?

score 141 · Accepted Answer · edited Jul 27 '18 at 03:12

141

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.

edited Jul 27 '18 at 03:12

Soviut

88,194
49
192
260

answered Sep 04 '12 at 16:32

Marcin Kapusta

5,076
3
38
55

2

unnest() is a really cool trick! I had a list of ID's from a customer and needed to join against them but wanted to find a way to do it w/o a temp table. You can join (select unnest(ARRAY[1,2,3])) as idlist and it works like a champ. thanks! – apinstein Apr 03 '13 at 02:11
@apinstein can you please show me the query? still dint get it – manocha_ak Apr 10 '13 at 05:18
7

If you want to join two tables with each other you could for instance use `SELECT b.*, a.item_id FROM (SELECT unnest(array_column_in_table_a) item_id FROM table_a) as a RIGHT JOIN table_b b on b.id=a.item_id;` – Timothy Dalton Oct 19 '17 at 11:52
9

I needed to join to tables, but the above comment didn't work for me, but this did: `SELECT * FROM table_a a JOIN table_b b ON b.id = ANY(a.array_column_in_table_a);`, thanks to https://www.garysieling.com/blog/postgres-join-on-an-array-field – tim-phillips Aug 01 '18 at 17:02
This doesn't match the question, why was it chosen? – Gringo Suave Feb 17 '21 at 10:08

Scott Bailey · Answer 2 · 2010-03-22T04:50:09.183

53

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 idx
FROM some_chosen_data_in_order s
JOIN items i ON i.id = ANY(s.id_items)
ORDER BY idx(id_items, i.id)

edited Mar 22 '10 at 04:50

answered Mar 22 '10 at 03:01

Scott Bailey

7,748
2
23
21

1

It's clearly what I need. Thanx! I'm denormalizing my database due to efficiency reasons. – Adiasz Mar 24 '10 at 18:41
Works, thanks. The order by clause works with just `idx` as well. – Gringo Suave Feb 17 '21 at 10:22
`where s.id = ?` -- an id number – Gringo Suave Feb 17 '21 at 10:44

aruis · Answer 3 · 2018-04-27T05:04:59.433

8

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)

edited Apr 27 '18 at 05:04

answered Apr 27 '18 at 04:59

aruis

721
7
10

score 1 · Answer 4 · edited Jan 16 '18 at 10:28

1

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

edited Jan 16 '18 at 10:28

BinaryButterfly

18,137
13
50
91

answered May 28 '15 at 18:03

Roy Merrill

11
2

6

When adding code as answer, then add a bit explanation to your answer for future readers.. – HaveNoDisplayName May 28 '15 at 18:23

score 1 · Answer 5 · answered Jul 14 '21 at 11:02

1

If you don't like functions (https://stackoverflow.com/a/2489805/642096):

SELECT jt.*
FROM (
     SELECT ARRAY_AGG(DISTINCT dupes_column) AS unique_vals
     FROM dupes_table
) q
LEFT JOIN joined_table jt
ON jt.id = ANY(q.unique_vals);

answered Jul 14 '21 at 11:02

cetver

11,279
5
36
56

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

5 Answers5

Linked