Postgres - Look up every array value in another table

Question

Suppose I have a table Products that looks like:

category | items
-----------------
fruit    | {"apple", "banana"}
vegetable| {"carrot"}

and a table Prices that looks like:

name   | price
----------------
apple  | 1
banana | 2
carrot | 3

What's the simplest way to get an output like:

category | prices
-----------------
fruit    | {1, 2}
vegetable| {3}

Feel free to use CTE or any other niceties that will make the query easy to read.

klin · Accepted Answer · 2018-02-02 03:10:15Z

1

Use unnest() to get elements of the items array which can be joined with names of the prices table. Finally, prices should be aggregate in groups by category:

select category, array_agg(price) as prices
from products
cross join unnest(items) u(item)
join prices on name = item
group by category
order by category;

 category  | prices 
-----------+--------
 fruit     | {1,2}
 vegetable | {3}
(2 rows)

answered Feb 2, 2018 at 3:10

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Postgres - Look up every array value in another table

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