aggregation of tables with array columns

user_1297780 0
Viewed 28

Table 1

berechtigen_ids: _int4
{1,2,3}
{1}
{2}

Table 2

id name
1 Karl
2 Fritz
3 Chlodwig

View 3

berechtigte
Karl, Fritz, Chlodwig
Karl
Fritz

I have table 1 and table 2, I want to achieve view 3. How can I do that?

postgresql sql
2 Answers

You can use a lateral join or subquery:

select (select string_agg(t2.name, ', ')
        from table2 t2
        where t2.id = any(t1. berechtigen_ids)
       ) as names
from table1 t1;

Here is a db<>fiddle.

Note that this does not use aggregation, so if two rows have the same ids, there are still two rows in the result set.

user_11440350

You can do the following:

select string_agg(name, ', ') as berechtigte
from table2 t2
join table1 t1
on t2.id = any(t1.berechtigen_ids)
group by berechtigen_ids

Though this code won't run properly if you have duplcates in the table1. If that's the case I would suggest adding a new column id in the table1 and grouping by it.

user_128854680
Related