CODEWITHSUNDEEP
postgresql
Tolga
Tolga

Reputation: 1357

POSTGRESQL: How to join tables on array column?

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.

Upvotes: 2

Views: 4633

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 10

Related Questions