Skip to content
Advertisement

JSON_AGG as Object not Array – PostgresSQL

I have the table “mytable” with many “bad” and “good” records:

My current query is:

And the result I have:

But the result I want to get:

Is there a way to get it (combine two JSON objects into one)? Thanks!

Advertisement

Answer

json_build_object() with just one level of aggregation should be enough:

Demo on DB Fiddle:

h     | js                     
:---- | :----------------------
12:00 | {"good" : 1, "bad" : 1}
13:00 | {"good" : 3, "bad" : 0}
14:00 | {"good" : 0, "bad" : 1}
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement