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?

Answer 1

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)