Postgres - aggregate two columns into one item
I would like to aggregate two columns into one "array" when grouping.
Assume a table like so:
friends_map:
=================================
user_id friend_id confirmed
=================================
1 2 true
1 3 false
2 1 true
2 3 true
1 4 false
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.
Currently I have this:
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
which gets me:
=================================
user_id friends confirmed
=================================
1 [2,3,4] [t, f, f]
How can I get:
=================================
user_id friends
=================================
1 [ [2,t], [3,f], [4,f] ]
SELECT user_id, array_agg((friend_id, confirmed)) as friends
FROM friend_map
WHERE user_id = 1
GROUP BY user_id
user_id | array_agg
--------+--------------------------------
1 | {"(2,true)","(3,false)","(4,false)"}
You could avoid the ugliness of the multidimentional array and use some json which supports mixed datatypes:
SELECT user_id, json_agg(json_build_array(friend_id, confirmed)) AS friends
FROM friends_map
WHERE user_id = 1
GROUP BY user_id
Or use some key : value
pairs since json allows that, so your output will be more semantic if you like:
SELECT user_id, json_agg(json_build_object(
'friend_id', friend_id,
'confirmed', confirmed
)) AS friends
FROM friends_map
WHERE user_id = 1
GROUP BY user_id;
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
Demo: SQL Fiddle