json_agg Two columns in Postgres

Question

I have a table which has the following contains two columns that look like the following:

rev | level
-----------
B   | 1001
B   | 1002
B   | 1003
C   | 1004
C   | 1005
D   | 1006

I am trying to return a column that looks like this:

{"B":["1001","1002","1003"], "C":["1002","1003"], "D":["1006"]}

the best I could get was using this query:

SELECT d.rev,
       to_json(ARRAY(SELECT level
                     FROM details
                     WHERE rev = d.rev
                     GROUP BY level
                     ORDER BY level DESC
                    )) AS level
FROM details d
GROUP BY d.rev
ORDER BY d.rev DESC

This gives me the following rows :

____________________________________
|  B    | ["1001","1002","1003"]   |
|  C    | ["1004","1005"]          |
|  D    | ["1006"]                 |
|__________________________________|

How do I combine these columns into one JSON object?

select version()?

Clodoaldo Neto
– Clodoaldo Neto

2017-04-17 15:28:50 +00:00
Commented Apr 17, 2017 at 15:28 — Clodoaldo Neto
– Clodoaldo Neto, Commented Apr 17, 2017 at 15:28

klin · Accepted Answer · 2017-04-17 15:31:30Z

16

You can use json_agg() in your query:

select rev, json_agg(level) levels
from details
group by 1
order by 1;

 rev |       levels       
-----+--------------------
 B   | [1001, 1002, 1003]
 C   | [1004, 1005]
 D   | [1006]
(3 rows)

and json_object_agg() to aggregate the result to a single json:

select json_object_agg(rev, levels order by rev)
from (
    select rev, json_agg(level) levels
    from details
    group by 1
    ) s;

                        json_object_agg                         
----------------------------------------------------------------
 { "B" : [1001, 1002, 1003], "C" : [1004, 1005], "D" : [1006] }
(1 row)

answered Apr 17, 2017 at 15:31

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

The Kingdom Tech Over a year ago

Thank you. This is what I was looking for!

Collectives™ on Stack Overflow

json_agg Two columns in Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related