Bill Bill - 1 year ago 54
SQL Question

LEFT OUTER JOIN on array column with multiple values

I cannot seem to find the trick to join two tables through an array column when one table is not an array value, and the other table's array value can contain multiple values. It does work when there is a single valued array.

Here's a simple minimal example of what I'm talking about. The real tables have GIN indexes on the array columns FWIW. These do not, but the query behaves the same.

INSERT INTO eg_person (id, name) VALUES
(1, 'alice')
, (2, 'bob')
, (3, 'charlie');

CREATE TABLE eg_assoc (aid INT PRIMARY KEY, actors INT[], benefactors INT[]);
INSERT INTO eg_assoc (aid, actors, benefactors) VALUES
(1, '{1}' , '{2}')
, (2, '{1,2}', '{3}')
, (3, '{1}' , '{2,3}')
, (4, '{4}' , '{1}');

SELECT aid, actors,, benefactors,
FROM eg_assoc
LEFT JOIN eg_person a_person on array[] @> eg_assoc.actors
LEFT JOIN eg_person b_person on array[] @> eg_assoc.benefactors;

The actual results are this like so. The issue here is that name column comes up
if either
contains more than one value.

aid | actors | name | benefactors | name
1 | {1} | alice | {2} | bob
2 | {1,2} | | {3} | charlie
3 | {1} | alice | {2,3} |
4 | {4} | | {1} | alice

I was expecting this:

aid | actors | name | benefactors | name
1 | {1} | alice | {2} | bob
2 | {1,2} | alice | {3} | charlie
2 | {1,2} | bob | {3} | charlie
3 | {1} | alice | {2,3} | bob
3 | {1} | alice | {2,3} | charlie
4 | {4} | | {1} | alice

It would be really nice if I could get it to look like this, though:

aid | actors | name | benefactors | name
1 | {1} | {alice} | {2} | {bob}
2 | {1,2} | {alice,bob} | {3} | {charlie}
3 | {1} | {alice} | {2,3} | {bob, charlie}
4 | {4} | | {1} | {alice}

I'm aware that this schema denormalized, and I'm willing to go to a normal representation if need be. However, this is for a summary query and it already involves a lot more joins than I'd like.

Answer Source

&& operator and index?

You are right, the overlap operator && could use a GIN index on arrays. Very useful for queries like the following (to find rows with person 1 among the 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

Proper queries

This is not a trivial problem. Start by reading this:

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):

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 = 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 = e.benefactors[i]
              ORDER  BY i) AS ben_names
FROM   eg_assoc e;

May still perform best, even in pg 9.3.
Using an array constructor which is faster than array_agg().

LATERAL queries

For PostgreSQL 9.3+.

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

SQL Fiddle with a couple of variants (except the version for pg 9.4).

Subtle detail: If a person is not found, it's just left out. 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.

WITH ORDINALITY in Postgres 9.4+

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;

Failed query

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

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

    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.

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

  3. 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.

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

  5. 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.