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?

klin · Accepted Answer · 2019-10-14 14:56:34Z

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.

Mr. T · Accepted Answer · 2020-07-30 09:25:14Z

8

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;

answered Jul 30, 2020 at 9:25

Mr. T

13.1k5 gold badges42 silver badges49 bronze badges

1 Comment

AdamAL Over a year ago

Are the order of the tags guaranteed by this method?

Collectives™ on Stack Overflow

Postgres - join on array values

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related