How to create array column with UNNEST and JOIN in PostgreSQL?-CodePudding

Given a table Names:

name_id|naam|
------- ---- 
      1|Ad  |
      2|Bo  |
      3|Che |

and a table Widgets where each widget has an array of name id's:

widget_id|name_ids|
--------- -------- 
        1|{1,2,3} |
        2|{3,2}   |

I need to create a SELECT query to replace an array with name_ids with an array of names like this:

widget_id|names      |
--------- ----------- 
        1|{Ad,Bo,Che}|
        2|{Che,Bo}   |

In this SO post I found how to connect names to name ID's, but this leads to a new widget row for each name:

select * from 
(select widget_id , unnest (name_ids) name_id from widgets w ) ww
inner join names n on ww.name_id = n.name_id

widget_id|name_id|name_id|naam|
--------- ------- ------- ---- 
        1|      1|      1|Ad  |
        1|      2|      2|Bo  |
        1|      3|      3|Che |
        2|      3|      3|Che |
        2|      2|      2|Bo  |

What is missing, and cannot find, is some sort of GROUP BY WIDGET_ID and an inverse function of UNNEST, in order to put all names of a widget into a names array, but there seems no function NEST. I was experienced in Oracle, but PostgreSQL seems more advanced, and complex.

Any help is appreciated.

CodePudding user response：

You need to group by the widget_id after "normalizing" the widgets on the fly

select w.widget_id, w.name_ids, array_agg(n.naam) names
from widgets w
  cross join unnest(name_ids) as u(name_id)
  join names n on n.name_id = u.name_id
group by w.widget_id, w.name_ids
order by w.widget_id;

Output:

widget_id|name_ids|names      |
--------- -------- ----------- 
        1|{1,2,3} |{Ad,Bo,Che}|
        2|{3,2}   |{Che,Bo}   |