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
– SoluableNonagon, Commented Dec 8, 2015 at 18:30

Eggplant · Accepted Answer · 2015-12-08 18:54:25Z

90

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 8, 2015 at 18:54

Eggplant

2,0231 gold badge16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

chrismarx Over a year ago

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

kevlarr Over a year ago

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)

FlyingV Over a year ago

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.

Ivan Burlutskiy · Accepted Answer · 2015-12-08 18:46:03Z

75

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 8, 2015 at 18:46

Ivan Burlutskiy

1,6331 gold badge12 silver badges22 bronze badges

Comments

user000001 · Accepted Answer · 2018-05-24 11:26:32Z

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, 2018 at 11:26

user000001

33.7k14 gold badges86 silver badges113 bronze badges

answered Dec 8, 2015 at 18:34

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

4 Comments

Daniel Lyons Over a year ago

Note this is building strings like '[2,t]', not nested arrays. Actual Postgres arrays cannot contain values of different types (like integer and boolean).

Hart CO Over a year ago

@DanielLyons Good point, I'm not accustomed to thinking about storing nested arrays like this in an rdbms so was thinking strings.

Daniel Lyons Over a year ago

It's alright; there's no right way to do the wrong thing. :)

SoluableNonagon Over a year ago

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.

klin · Accepted Answer · 2015-12-08 18:53:56Z

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 8, 2015 at 18:53

answered Dec 8, 2015 at 18:44

klin

123k15 gold badges240 silver badges262 bronze badges

3 Comments

Eggplant Over a year ago

Just question, what version are you using? array_agg isn't supposed to aggregate text[] or integer[]...

klin Over a year ago

Ops! PostgreSQL 9.5beta2

Eggplant Over a year ago

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.

roi3363 · Accepted Answer · 2020-08-10 13:16:10Z

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, 2020 at 13:16

roi3363

4095 silver badges8 bronze badges

Collectives™ on Stack Overflow

Postgres - aggregate two columns into one item

5 Answers 5

3 Comments

Comments

4 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

4 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related