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

Question

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...

A lateral cross join with unnest, then join and aggregate. — Laurenz Albe
– Laurenz Albe, Commented Dec 13, 2019 at 15:12

klin · Accepted Answer · 2019-12-13 15:15:50Z

2

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.

answered Dec 13, 2019 at 15:15

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related