Postgres-将两列汇总为一项 [英] Postgres - aggregate two columns into one item

问题描述

我希望在分组时将两列汇总为一个数组。

I would like to aggregate two columns into one "array" when grouping.

假设这样一个表：

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

我想从该表中进行选择，并按user_id进行分组，并获取friend_id并确认为以逗号分隔的连接值。

I would like to select from this table and group by user_id and get friend_id and confirmed as a concatenated value separated by a comma.

目前我有这个：

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] ]

推荐答案

可以先将这些值连接在一起，然后再将它们输入到 array_agg（）函数：

You can concatenate the values together prior to feeding them into the array_agg() function:

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

演示： SQL提琴

这篇关于Postgres-将两列汇总为一项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！