PostgreSQL中如何将JSON_AGG结果转为单个JSON对象而非数组？

当然可以实现你想要的结果！原来的查询是先按小时和good状态分组生成单个JSON对象，再聚合为数组，我们可以调整思路，直接在小时分组内计算good和bad的计数，一次性构建出包含两个键的单个JSON对象，同时确保缺失的类别显示0值。

方法1：使用CASE表达式计数 #

SELECT 
    hour AS h,
    JSON_BUILD_OBJECT(
        'good', COUNT(CASE WHEN good = TRUE THEN 1 END),
        'bad', COUNT(CASE WHEN good = FALSE THEN 1 END)
    ) AS result
FROM mytable
GROUP BY hour
ORDER BY hour;

方法2：使用PostgreSQL的FILTER子句（更简洁） #

PostgreSQL支持COUNT搭配FILTER子句，语法更直观易读：

SELECT 
    hour AS h,
    JSON_BUILD_OBJECT(
        'good', COUNT(*) FILTER (WHERE good = TRUE),
        'bad', COUNT(*) FILTER (WHERE good = FALSE)
    ) AS result
FROM mytable
GROUP BY hour
ORDER BY hour;

原理说明 #

两种方法的核心逻辑一致：

• 在每个小时分组内，分别统计good和bad的记录数；
• 当某类没有匹配记录时，COUNT会自动忽略空值（CASE返回null）或FILTER筛选不到行，最终返回0；
• 用JSON_BUILD_OBJECT直接将两个计数组合成单个JSON对象，省去了后续合并数组的步骤。

预期输出 #

执行后会得到完全符合你需求的结果：

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

内容的提问来源于stack exchange，提问作者Aerien