3

I have a table in a Postgres 12 database with that has an array column with a bunch of dog breed guids. I want to lookup these values and basically return the lookup values instead of the guids.

CREATE TABLE dogs(
 name text,
 breeds text[]
);

INSERT INTO dogs (name, breeds) VALUES ('Barkley', '{"abc", "xyz"}');
INSERT INTO dogs (name, breeds) VALUES ('Ponyo', '{"zzz", "xyz"}');


CREATE TABLE breeds(
 guid text,
 breed text
);


INSERT INTO breeds (guid, breed) VALUES ('abc', 'Maltipoo');
INSERT INTO breeds (guid, breed) VALUES ('xyz', 'Jack Russel');
INSERT INTO breeds (guid, breed) VALUES ('zzz', 'Dalmatian');

I would like to be able to return the following:

Barkley, ['Maltipoo', 'Jack Russel']
Ponyo, ['Jack Russel', 'Dalmatian']

Essentially, look them up in my 'breeds' table before returning the values. Order of elements is not relevant.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
Kamilski81
  • 231
  • 2
  • 13

1 Answers1

4
SELECT d.name, b.breeds_text
FROM   dogs d
CROSS  JOIN LATERAL (
   SELECT ARRAY(SELECT b.breed
                FROM   unnest(breeds) a(guid)
                JOIN   breeds b USING (guid)) AS breeds_text
   ) b;

Because we JOIN after unnnest(), the order of elements is not necessarily preserved.

I had LEFT JOIN LATERAL (...) ON true at first. But since the ARRAY constructor makes the subquery always return exactly one row, that's equivalent to a simpler CROSS JOIN. See:

To guarantee order of elements (if you need that?), use WITH ORDINALITY and ORDER BY in the LATERAL subquery:

SELECT d.name, b.breeds_text
FROM   dogs d
CROSS  JOIN LATERAL (
   SELECT ARRAY(SELECT b.breed
                FROM   unnest(breeds) WITH ORDINALITY AS a(guid, ord)
                JOIN   breeds b USING (guid)
                ORDER  BY a.ord) AS breeds_text
   ) b;

Or a lowly correlated subquery, probably a bit faster:

SELECT d.name
     , ARRAY(SELECT b.breed
             FROM   unnest(d.breeds) WITH ORDINALITY AS a(guid, ord)
             JOIN   breeds b USING (guid)
             ORDER  BY a.ord) AS breeds_text
FROM   dogs d;

db<>fiddle here

NULL values and empty arrays produce an empty array in the result. To preserve NULL, you'd need to do more. Like: use CASE ...

Duplicates in the array are preserved as given with either query.

See:

Or consider a normalized many-to-many relational design instead of the array to begin with:

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • That LEFT JOIN LATERAL (…) ON true looks weird. Is there any reason one would prefer this over putting the subquery directly into the SELECT expressions? – Bergi Jul 06 '21 at 09:09
  • I would have expected a SELECT d.name, array_agg(b.breeds_text) FROM dogs d LEFT JOIN unnest(breeds) a(guid) JOIN breeds b USING (guid) GROUP BY d.name as an alternative, although that approach has weird edge cases with empty arrays – Bergi Jul 06 '21 at 09:11
  • 1
    @Bergi: LEFT JOIN LATERAL (...) ON true makes sense to keep all rows to the left in the result if the right table expression does not return any rows. Since that cannot actually happen in this case, I switched to a simpler CROSS JOIN now. (Equivalent in this case.). Generally, a correlated subquery is equivalent to LEFT JOIN LATERAL (...) ON true, but the latter is more versatile. (Allows various join types, join conditions, and it can return multiple columns.) It's not needed in this particular query. So I also suggested the simpler correlated subquery. – Erwin Brandstetter Jul 06 '21 at 22:41
  • @Bergi: As for your added query: It would eliminate rows with empty or NULL arrays (after fixing it). We can alter it to preserve all rows, but the query is still less versatile, typically slower, and we get an array with a single NULL element for NULL or empty arrays. There are more fine points and intricacies ... I added some demos to my fiddle. – Erwin Brandstetter Jul 06 '21 at 22:50
  • @Bergi: Maybe most importantly: The alternative query multiplies rows in the outer SELECT, which have to be aggregated back correctly and for a price. Simple in the example. Typically, there are more columns and it's not as simple. – Erwin Brandstetter Jul 06 '21 at 22:53
  • I'd be interested to know how to filter with an approach like this. WHERE b.breed = 'Dalmatian' in the sub-query does not work. – dylankb Aug 12 '22 at 05:07