Postgres - join on array values

Question

Say I have a table with schema as follows

id  |  name  |   tags   |
1   |  xyz   |  [4, 5]  |

Where tags is an array of references to ids in another table called tags.

Is it possible to join these tags onto the row? i.e. replacing the id numbers with the values for thise rows in the tags table such as:

id  |  name  |                        tags                        |
1   |  xyz   | [[tag_name, description], [tag_name, description]] |

If not, I wonder if this an issue with the design of the schema?

Answer 1

Example tags table:

create table tags(id int primary key, name text, description text);
insert into tags values
(4, 'tag_name_4', 'tag_description_4'),
(5, 'tag_name_5', 'tag_description_5');

You should unnest the column tags, use its elements to join the table tags and aggregate columns of the last table. You can aggregate arrays to array:

select t.id, t.name, array_agg(array[g.name, g.description])
from my_table as t
cross join unnest(tags) as tag
join tags g on g.id = tag
group by t.id;

 id | name |                            array_agg                            
----+------+-----------------------------------------------------------------
  1 | xyz  | {{tag_name_4,tag_description_4},{tag_name_5,tag_description_5}}
(1 row) 

or strings to array:

select t.id, t.name, array_agg(concat_ws(', ', g.name, g.description))
...

or maybe strings inside a string:

select t.id, t.name, string_agg(concat_ws(', ', g.name, g.description), '; ')
...

or the last but not least, as jsonb:

select t.id, t.name, jsonb_object_agg(g.name, g.description)
from my_table as t
cross join unnest(tags) as tag
join tags g on g.id = tag
group by t.id;

 id | name |                            jsonb_object_agg                            
----+------+------------------------------------------------------------------------
  1 | xyz  | {"tag_name_4": "tag_description_4", "tag_name_5": "tag_description_5"}
(1 row)

Live demo: db<>fiddle.

Answer 2

not sure if this is still helpful for anyone, but unnesting the tags is quite a bit slower than letting postgres do the work directly from the array. you can rewrite the query and this is generally more performant because the g.id = ANY(tags) is a simple pkey index scan without the expansion step:

SELECT t.id, t.name, ARRAY_AGG(ARRAY[g.name, g.description])
  FROM my_table AS t
    LEFT JOIN tags AS g 
      ON g.id = ANY(tags)
  GROUP BY t.id;