POSTGRESQL: How to join tables on array column?

Question

I would like to ask your help to create a postgresql query so that I can left join categories & products tables and replace the prodcutnums with the actual product names.

Below you can see the tables structures & desired output for the query

Categories Table:

              name               |              productnums                                 
---------------------------------+------------------------------
 Books                           | {605,614,663,647,645,619,627}
 Kitchen                         | {345,328}
 Electronics                     | {145,146}

Products Table:

              id                 |              name                                 
---------------------------------+----------------------
 145                             | LCD Monitor
 147                             | Mouse
 345                             | Glass

Desired Output:

              name               |              productnums                                 
---------------------------------+-------------------------------------------
 Electronics                     | {LCD Monitor,Mouse}

I will appreciate any kind of support.

user330315 · Accepted Answer · 2019-03-27 10:59:55Z

10

You can use the ANY operator in a JOIN condition, then use array_agg to aggregate the product names.

select c.name, 
       array_agg(p.name) as products
from categories c 
   left join products p on p.id = any(c.productnums)
group by c.name;

answered Mar 27, 2019 at 10:59

user330315

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

Collectives™ on Stack Overflow

POSTGRESQL: How to join tables on array column?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related