我想在分组时将两列聚合为一个"数组".
假设一张这样的桌子:
friends_map:
=================================
user_id friend_id confirmed
=================================
1 2 true
1 3 false
2 1 true
2 3 true
1 4 false
我想从这个表中进行 Select ,并按用户id分组,然后获得friend id,并确认为一个由逗号分隔的串联值.
目前我有:
SELECT user_id, array_agg(friend_id) as friends, array_agg(confirmed) as confirmed
FROM friend_map
WHERE user_id = 1
GROUP BY user_id
这让我明白:
=================================
user_id friends confirmed
=================================
1 [2,3,4] [t, f, f]
我怎样才能得到:
=================================
user_id friends
=================================
1 [ [2,t], [3,f], [4,f] ]