Home In postgres/presto/AWS-Athena, what's the opposite of `array_agg( (col1, col2) )` to get multiple rows per group?

Questions

In postgres/presto/AWS-Athena, what's the opposite of `array_agg( (col1, col2) )` to get multiple rows per group?

May 23, 2023

This answer shows how to collapse several columns per group to one array of key:value pairs.

friends_map:
=================================
user_id    friend_id    confirmed
=================================
1          2            true
1          3            false
2          1            true
2          3            true
1          4            false

SELECT user_id, array_agg((friend_id, confirmed)) as friends
FROM friend_map
WHERE user_id = 1
GROUP BY user_id

returns

user_id |           friends            
--------+--------------------------------
      1 | {"(2,true)","(3,false)","(4,false)"}

My question

How to go the other way around, from the result table back to the initial table?

>Solution :

In PrestoSQL/Trino you can use unnest:

-- sample data
with dataset(user_id, friend_id, confirmed) as(
    values (1, 2, true),
    (1, 3, false),
    (2, 1, true),
    (2, 3, true),
    (1, 4, false)
),
friend_map as (
    SELECT user_id, array_agg((friend_id, confirmed)) as friends
    FROM dataset
    GROUP BY user_id
)

-- query
select user_id, friend_id, confirmed
from friend_map,
unnest(friends) as t(friend_id, confirmed);

UPD

It seems that currently Athena (do not have access to Athena ATM to test it myself) does not support automatic unnest of rows to columns so you can try:

select user_id, r.friend_id, r.confirmed 
from (
    select user_id, cast(r as row(friend_id INT, confirmed VARCHAR))) r
    from friend_map,
    unnest(friends) as t(r)
);

P.S.

Note that Athena is not based on PostgreSQL, it is based on Presto/Trino (dependent on the engine version)