JSON_AGG as Object not Array - PostgresSQL

Question

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!

GMB · Accepted Answer · 2019-12-09 14:45:17Z

2

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}

answered Dec 9, 2019 at 14:45

GMB

224k25 gold badges102 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wai Ha Lee · Accepted Answer · 2019-12-09 15:20:15Z

1

Try to replace JSON_AGG with JSON_BUILD_OBJECT. That should help!

edited Dec 9, 2019 at 15:20

Wai Ha Lee

8,856102 gold badges61 silver badges100 bronze badges

answered Dec 9, 2019 at 14:46

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

856 bronze badges

1 Comment

Aerien Over a year ago

I've tried this before:( After replacing I get the message: column "foo.data" must appear in the GROUP BY clause or be used in an aggregate function. After adding GROUP BY foo.data another error: "could not identify an equality operator for type json"

jabajke · Accepted Answer · 2023-12-07 12:05:33Z

1

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

answered Dec 7, 2023 at 12:05

jabajke

1068 bronze badges

Collectives™ on Stack Overflow

JSON_AGG as Object not Array - PostgresSQL

3 Answers 3

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Related