I have the table “mytable” with many “bad” and “good” records:
x
|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!
Advertisement
Answer
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
h | js :---- | :---------------------- 12:00 | {"good" : 1, "bad" : 1} 13:00 | {"good" : 3, "bad" : 0} 14:00 | {"good" : 0, "bad" : 1}