Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

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

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

CREATE TABLE breeds (
  guid text
, breed text
);

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

3 rows affected

-- LATERAL subquery
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;


name
breeds_text


Barkley
{Maltipoo,"Jack Russel"}

Ponyo
{"Jack Russel",Dalmatian}

Empto
{}

Nullo
{}


-- LATERAL subquery preserving order
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;


name
breeds_text


Barkley
{Maltipoo,"Jack Russel"}

Ponyo
{Dalmatian,"Jack Russel"}

Empto
{}

Nullo
{}


-- correlated subquery preserving order
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;


name
breeds_text


Barkley
{Maltipoo,"Jack Russel"}

Ponyo
{Dalmatian,"Jack Russel"}

Empto
{}

Nullo
{}


-- Bergi's query with syntax fix
-- rows with empty and NULL arrays are eliminated!
SELECT d.name, array_agg(b.breed) AS breeds_text
FROM   dogs d
LEFT   JOIN unnest(breeds) a(guid) ON true
JOIN   breeds b USING (guid)
GROUP  BY d.name;


name
breeds_text


Barkley
{Maltipoo,"Jack Russel"}

Ponyo
{"Jack Russel",Dalmatian}


-- we might as well CROSS JOIN, since the next INNER JOIN eliminates anyway:
SELECT d.name, array_agg(b.breed) AS breeds_text
FROM   dogs d
CROSS  JOIN unnest(breeds) a(guid)
JOIN   breeds b USING (guid)
GROUP  BY d.name;


name
breeds_text


Barkley
{Maltipoo,"Jack Russel"}

Ponyo
{"Jack Russel",Dalmatian}


-- *Or* we use 2x LEFT JOIN
-- now, rows are preserved. but the query is less versatile, typically slower
-- and we get an array with a single NULL element for NULL or empty arrays
SELECT d.name, array_agg(b.breed) AS breeds_text
FROM   dogs d
LEFT   JOIN unnest(breeds) a(guid) ON true
LEFT   JOIN breeds b USING (guid)
GROUP  BY d.name;


name
breeds_text


Nullo
{NULL}

Barkley
{Maltipoo,"Jack Russel"}

Ponyo
{"Jack Russel",Dalmatian}

Empto
{NULL}


-- and preserving original order of elements (typically desirable, though not in this case) is less simple and more expensive, yet.
SELECT d.name, array_agg(b.breed ORDER BY a.ord) AS breeds_text
FROM   dogs d
LEFT   JOIN unnest(breeds) WITH ORDINALITY AS a(guid, ord) ON true
LEFT   JOIN breeds b USING (guid)
GROUP  BY d.name;


name
breeds_text


Barkley
{Maltipoo,"Jack Russel"}

Empto
{NULL}

Nullo
{NULL}

Ponyo
{Dalmatian,"Jack Russel"}

name	breeds_text
Barkley	{Maltipoo,"Jack Russel"}
Ponyo	{"Jack Russel",Dalmatian}
Empto	{}
Nullo	{}

name	breeds_text
Nullo	{NULL}
Barkley	{Maltipoo,"Jack Russel"}
Ponyo	{"Jack Russel",Dalmatian}
Empto	{NULL}