CODEWITHSUNDEEP
sqlarrayspostgresqljoin
silent-tiger
silent-tiger

Reputation: 538

Join a table to itself using an array column?

I have a table:

id        integer
status_id integer
children  integer[]

How do I write a query to join the table to itself on children and find all records where (all children items have status_id 2) & the item has status of 1?

In addition should children be indexed and with what index?

Edit: Based on krokodilko answer I think the query may be:

SELECT id
FROM (
    SELECT p.id, p.status_id, every(c.status_id = 2) AS all_children_status_2
    FROM positions p
    JOIN positions c
    ON c.id = ANY (p.children)
    GROUP BY p.id, p.status_id
) sub
WHERE all_children_status_2 IS TRUE AND status_id = 1;

Edit 2: Please note I have found in my reading that the array columns should use a GIN or GIST index. However unfortunately postgres does not use these indexes when using ANY. These mean that while the above query works it is very slow.

Upvotes: 1

Views: 1742

Answers (1)

krokodilko
krokodilko

Reputation: 36127

Use ANY operator:

Demo: http://www.sqlfiddle.com/#!17/2540d/1

CREATE TABLE parent(
  id int,
  child int[]
);

INSERT INTO parent VALUES(1, '{2,4,5}');

CREATE TABLE child(
  id int
);

INSERT INTO child VALUES (1),(2),(3),(4),(5);

SELECT *
FROM parent p
JOIN child c
ON c.id = any( p.child );

| id | child | id |
|----|-------|----|
|  1 | 2,4,5 |  2 |
|  1 | 2,4,5 |  4 |
|  1 | 2,4,5 |  5 |

In addition should children be indexed and with what index?

Yes - if children table is huge (more than a few hundreds/thousands rows).

CREATE INDEX some_name ON child( id )

Upvotes: 1

Related Questions