CODEWITHSUNDEEP
sqljsonpostgresqlgroup-by
Aerien
Aerien

Reputation: 23

JSON_AGG as Object not Array - PostgresSQL

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

|id|good|hour |
|1 |t   |12:00|
|2 |f   |12:00|
|3 |t   |13:00|
|4 |t   |13:00|
|5 |t   |13:00|
|6 |f   |14:00|
....

My current query is:

SELECT 
    h, 
    JSON_AGG(data) 
FROM (
    hour AS h, 
    JSON_BUILD_OBJECT(CASE WHEN good = TRUE THEN 'good' ELSE 'bad' END, COUNT(*)) AS data 
    FROM mytable 
    GROUP BY h, good
) AS foo 
GROUP BY h 
ORDER BY h;

And the result I have:

 12:00 | [{"good" : 1}, {"bad" : 1}]
 13:00 | [{"good" : 3}]
 14:00 | [{"bad" : 1}]
 ...

But the result I want to get:

 12:00 | {"good" : 1, "bad" : 1}
 13:00 | {"good" : 3, "bad" : 0}
 14:00 | {"good" : 0, "bad" : 1}
 ...

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

Upvotes: 2

Views: 2274

Answers (3)

jabajke
jabajke

Reputation: 106

if you use json_build_object, you will have to enter each field manually, so you may use to_json, and the json object (without array) will be automatically generated, hope it will be useful for someone

Upvotes: 1

Евлампий Романов
Евлампий Романов

Reputation: 85

Try to replace JSON_AGG with JSON_BUILD_OBJECT. That should help!

Upvotes: 1

GMB
GMB

Reputation: 222672

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

select 
    hour as h,
    json_build_object(
        'good',
        count(*) filter (where good = true),
        'bad',
        count(*) filter (where good = false)
    ) js
from mytable
group by hour
order by hour

Demo on DB Fiddle:

h     | js                     
:---- | :----------------------
12:00 | {"good" : 1, "bad" : 1}
13:00 | {"good" : 3, "bad" : 0}
14:00 | {"good" : 0, "bad" : 1}

Upvotes: 2

Related Questions