Postgres - aggregate two columns into one item
SELECT user_id, array_agg((friend_id, confirmed)) as friendsFROM friend_mapWHERE user_id = 1GROUP BY user_iduser_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_idOr 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 friendsFROM friends_mapWHERE user_id = 1GROUP BY user_idDemo: SQL Fiddle