aggregate arrays in postgresql

gistfile1.md

Tables

Table restaurants
        Column         |           Type           
-----------------------+--------------------------
 id                    | integer                  
 
Table contacts
    Column     |            Type             
---------------+-----------------------------
 id            | integer                     
 restaurant_id | integer                     
 emails        | text[]                      

Looking above, we have multiple contacts per restaurant (many-to-1). How can we collect all of the contact emails as a single array per restaurant?

For example, looking up the contacts for restaurant 10:

select * from contacts where restaurant_id = 10;

 id  | restaurant_id |                  emails
-----+---------------+-------------------------------------------
  53 |            10 | {preston@goodybag.com}
 101 |            10 | {coffee@time.com,wakkafloka@flame.com}

The solution is to unnest the emails per restaurant and then aggregate them.

# with unnested as (
   select unnest(emails) emails, restaurant_id from contacts
  )
  select array_agg(unnested.emails) as emails from restaurants 
  join unnested on restaurants.id=unnested.restaurant_id
  where restaurants.id = 10;

                             emails
----------------------------------------------------------------
 {preston@goodybag.com,coffee@time.com,wakkafloka@flame.com}