Cfrgtkky

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?

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)