How can I match the items in an array column, with a different table in Postgres?

Question

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 · Accepted Answer · 2021-07-06T22:53:25.747

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:

How to apply ORDER BY and LIMIT in combination with an aggregate function?

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:

How to implement a many-to-many relationship in PostgreSQL?

How can I match the items in an array column, with a different table in Postgres?

1 Answers1