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}