在Postgres/Presto/AWS Athena中，如何反向array_agg聚合实现行拆分？

背景 #

之前的方法可将多行多列数据聚合为键值对数组，例如针对friends_map表：

执行以下聚合SQL：

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

得到聚合结果：

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

问题 #

如何从上述聚合后的结果还原为初始的friends_map表结构？

解决方案 #

使用PostgreSQL的unnest函数展开数组，再拆分数组中的行类型元素为单独列即可完成逆向操作，具体SQL如下：

方法1：直接拆分展开

SELECT 
    user_id,
    (unnest(friends)).friend_id,
    (unnest(friends)).confirmed
FROM 你的聚合结果表名;

方法2：更规范的关联写法（避免重复调用unnest的潜在问题）

SELECT 
    t.user_id,
    f.friend_id,
    f.confirmed
FROM 你的聚合结果表名 t,
     unnest(t.friends) AS f(friend_id, confirmed);

针对示例聚合结果的完整测试

WITH aggregated_data AS (
    SELECT user_id, array_agg((friend_id, confirmed)) as friends
    FROM friends_map
    WHERE user_id = 1
    GROUP BY user_id
)
SELECT 
    ad.user_id,
    f.friend_id,
    f.confirmed
FROM aggregated_data ad,
     unnest(ad.friends) AS f(friend_id, confirmed);

执行后将得到与原表中user_id=1对应的原始记录：

内容的提问来源于Stack Exchange，提问作者Emman