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)