Join a table to itself using an array column?

Question

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.

krokodilko · Accepted Answer · 2018-06-23 03:44:30Z

1

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 )

answered Jun 23, 2018 at 3:44

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Join a table to itself using an array column?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related