SoluableNonagon SoluableNonagon -4 years ago 45
SQL Question

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

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download