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

Question

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.

This would be so much easier with a properly normalized model (which would also allow you to define proper foreign keys) — , Sep 21 '22 at 14:41
@a_horse_with_no_name so true, but someone invents an array type and then people are storing all kinds of data as array . . . — Roland, Sep 21 '22 at 17:27

score 1 · Accepted Answer · edited Sep 22 '22 at 06:25

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

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

1 Answers1