Postgres - aggregate two columns into one item

Question

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

But what I'm looking for is grouping under one variable name. This is just an example, it's really part of a larger join query, which contains more tables and data. — SoluableNonagon, Dec 08 '15 at 18:30

score 80 · Answer 1 · answered Dec 08 '15 at 18:54

80

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;

answered Dec 08 '15 at 18:54

Eggplant

1,903
1
14
24

3

This is definitely the right answer, you get nice result as json (which is probably what we all want anyways), and it looks like [[1990,"pm25"],[1995,"pm25"],[2000,"pm25"]] – chrismarx Sep 24 '17 at 23:18
1

This is absolutely beautiful - and as @chrismarx said, it's exactly what I wanted my end result to be. (I assumed I would have to process outside of postgres after query) – kevlarr Jan 09 '20 at 20:50
This is the right answer; The goal here is to create a nested array, and this does exactly that. As reflected in the other conversations, nested arrays of different types is not allowed. – FlyingV Apr 20 '20 at 04:31

score 73 · Answer 2 · answered Dec 08 '15 at 18:46

73

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)"}

answered Dec 08 '15 at 18:46

Ivan Burlutskiy

1,605
1
12
22

score 37 · Accepted Answer · edited May 24 '18 at 11:26

37

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

edited May 24 '18 at 11:26

user000001

32,226
12
81
108

answered Dec 08 '15 at 18:34

Hart CO

34,064
6
48
63

4

Note this is building strings like `'[2,t]'`, not nested arrays. Actual Postgres arrays cannot contain values of different types (like integer and boolean). – Daniel Lyons Dec 08 '15 at 18:35
@DanielLyons Good point, I'm not accustomed to thinking about storing nested arrays like this in an rdbms so was thinking strings. – Hart CO Dec 08 '15 at 18:36
1

It's alright; there's no right way to do the wrong thing. :) – Daniel Lyons Dec 08 '15 at 18:37
Thanks, this would probably do well, the end result goes to javascript, which can have different types in an array. The other way for me to do this is to convert the type of confirmed to an int (0 and 1) for representing false and true, then types would be the same. – SoluableNonagon Dec 08 '15 at 18:41

klin · Answer 4 · 2015-12-08T18:53:56.967

21

In Postgres 9.5 you can obtain array of arrays of text:

SELECT user_id, array_agg(array[friend_id::text, confirmed::text])
FROM friend_map
WHERE user_id = 1
GROUP BY user_id;

 user_id |           array_agg            
---------+--------------------------------
       1 | {{2,true},{3,false},{4,false}}
(1 row)

or array of arrays of int:

SELECT user_id, array_agg(array[friend_id, confirmed::int])
FROM friend_map
WHERE user_id = 1
GROUP BY user_id;

 user_id |      array_agg      
---------+---------------------
       1 | {{2,1},{3,0},{4,0}}
(1 row)

edited Dec 08 '15 at 18:53

answered Dec 08 '15 at 18:44

klin

112,967
15
204
232

Just question, what version are you using? `array_agg` isn't supposed to aggregate `text[]` or `integer[]`... – Eggplant Dec 08 '15 at 18:49
Ops! PostgreSQL 9.5beta2 – klin Dec 08 '15 at 18:53
Aha! You got me there, I was wondering what kind of *sorcery* was that :) 9.5 isn't released yet, so I assume the OP is looking for some production-ready solution. – Eggplant Dec 08 '15 at 18:55

score 0 · Answer 5 · answered Aug 10 '20 at 13:16

0

You can use: array[]

Just do that:

SELECT user_id, array[friend_id, confirmed]
FROM friend_map
WHERE user_id = 1
GROUP BY 1;

answered Aug 10 '20 at 13:16

roi3363

278
4
8

Postgres - aggregate two columns into one item

5 Answers5

Linked