LEFT OUTER JOIN on array column with multiple values

arrays, left-join, postgresql, sql

Solution

Yes, the overlap operator `&&` can use a GIN index on arrays. Very useful for queries like this one to find rows with a given person (`1`) among an array of actors:

SELECT * FROM eg_assoc WHERE actors && '{1}'::int[]

However, the logic of your query is the other way round, looking for all persons listed in the arrays in `eg_assoc`. A GIN index is no help here. We just need the btree index of the PK `person.id`.

Proper queries

Basics:

- PostgreSQL unnest() with element number

The following queries preserve original arrays exactly as given, including possible duplicate elements and original order of elements. Works for 1-dimenstional arrays. Additional dimensions are folded into a single dimension. It's more complex to preserve multiple dimensions (but totally possible):

`WITH ORDINALITY` in Postgres 9.4 or later

SELECT aid, actors
     , ARRAY(SELECT name
             FROM   unnest(e.actors) WITH ORDINALITY a(id, i)
             JOIN   eg_person p USING (id)
             ORDER  BY a.i) AS act_names
     , benefactors
     , ARRAY(SELECT name
             FROM   unnest(e.benefactors) WITH ORDINALITY b(id, i)
             JOIN   eg_person USING (id)
             ORDER  BY b.i) AS ben_names
FROM   eg_assoc e;

`LATERAL` queries

For PostgreSQL 9.3+.

SELECT e.aid, e.actors, a.act_names, e.benefactors, b.ben_names
FROM   eg_assoc e
, LATERAL (
   SELECT ARRAY( SELECT name
                 FROM   generate_subscripts(e.actors, 1) i
                 JOIN   eg_person p ON p.id = e.actors[i]
                 ORDER  BY i)
   ) a(act_names)
, LATERAL (
   SELECT ARRAY( SELECT name
                 FROM   generate_subscripts(e.benefactors, 1) i
                 JOIN   eg_person p ON p.id = e.benefactors[i]
                 ORDER  BY i)
   ) b(ben_names);

db<>fiddle here with a couple of variants. Old sqlfiddle

Subtle detail: If a person is not found, it's just dropped. Both of these queries generate an empty array (`'{}'`) if no person is found for the whole array. Other query styles would return `NULL`. I added variants to the fiddle.

Correlated subqueries

For Postgres 8.4+ (where `generate_subsrcipts()` was introduced):

SELECT aid, actors
     , ARRAY(SELECT name
             FROM   generate_subscripts(e.actors, 1) i
             JOIN   eg_person p ON p.id = e.actors[i]
             ORDER  BY i) AS act_names
     , benefactors
     , ARRAY(SELECT name
             FROM   generate_subscripts(e.benefactors, 1) i
             JOIN   eg_person p ON p.id = e.benefactors[i]
             ORDER  BY i) AS ben_names
FROM   eg_assoc e;

May still perform best, even in Postgres 9.3. The `ARRAY` constructor is faster than `array_agg()`. See:

- Why is array_agg() slower than the non-aggregate ARRAY() constructor?

Your failed query

The query provided by @a_horse seems to do the job, but it is unreliable, misleading, potentially incorrect and needlessly expensive.

Proxy cross join because of two unrelated joins. A sneaky anti-pattern. See:

- Two SQL LEFT JOINS produce incorrect result

Fixed superficially with `DISTINCT` in `array_agg()`to eliminates the generated duplicates, but that's really putting lipstick on a pig. It also eliminates duplicates in the original because its impossible to tell the difference at this point - which is potentially incorrect.

The expression `a_person.id = any(eg_assoc.actors)` works, but eliminates duplicates from the result (happens two times in this query), which is wrong unless specified.

Original order of array elements is not preserved. This is tricky in general. But it's aggravated in this query, because actors and benefactors are multiplied and made distinct again, which guarantees arbitrary order.

No column aliases in the outer `SELECT` result in duplicate column names, which makes some clients fails (not working in the fiddle without aliases).

`min(actors)` and `min(benefactors)` are useless. Normally one would just add the columns to `GROUP BY` instead of fake-aggregating them. But `eg_assoc.aid` is the PK column anyway (covering the whole table in `GROUP BY`), so that's not even necessary. Just `actors, benefactors`.

Aggregating the whole result is wasted time and effort to begin with. Use a smarter query that doesn't multiply the base rows, then you don't have to aggregate them back.

Problem

Original source

Related problems

• PostgreSQL unnest() with element number
• Two SQL LEFT JOINS produce incorrect result