CODEWITHSUNDEEP
sqlarrayspostgresqlaggregate-functions
Luffydude
Luffydude

Reputation: 812

How to select from a column with a list of ids in postgresql

I've got mytable1 with row_number integer and list_of_ids int[] column

mytable2 with id integer and company text columns

Example entry for mytable1 

1 | {633681,1278392,2320888,2200426}
2 | {2443842,2959599,3703823,3330376,915750,941736}

Example entry for mytable2 

 633681 | apple
1278392 | charmander
2320888 | apple
2200426 | null
2443842 | batman

I need to feed back values from mytable2 into mytable1. This way the expected output would be 

1 | {633681,1278392,2320888,2200426} | 2 apple, 1 charmander, 1 null
2 | {2443842,2959599,3703823,3330376,915750,941736} | 1 batman etc...

Upvotes: 2

Views: 5762

Answers (1)

klin
klin

Reputation: 121889

You need to unnest the lists of ids, join mytable2 using unnested ids and finally aggregate back the data to get a single row for a row_number.

select 
    row_number, 
    list_of_ids, 
    string_agg(format('%s %s', count, company), ', ' order by count desc, company)
from (
    select 
        row_number, 
        list_of_ids, 
        coalesce(company, '<null>') as company, 
        count(*)
    from (
        select row_number, list_of_ids, unnest(list_of_ids) as id
        from mytable1
        ) t1
    join mytable2 t2 using(id)
    group by 1, 2, 3
    ) s
group by 1, 2

Db<>fiddle.

Upvotes: 2

Related Questions