如何对PostgreSQL的jsonb字段求和并筛选符合条件的查询结果

需求拆解 #

需要完成两个核心操作：

• 对每个uid下process_stat_json中所有time字段求和
• 筛选出其中type为Unknown且time值超过对应uid总时间10%的对象

原查询问题分析 #

你之前的查询错误在于直接尝试从顶层jsonb对象中提取time字段，但process_stat_json的结构是顶层为键值对（如"Type A"），每个键对应包含time和type的子对象，因此需要先将这些子对象展开才能遍历计算。

正确查询语句 #

WITH process_stats AS (
    -- 展开每个uid对应的所有子统计对象
    SELECT
        p.id,
        p.uid,
        stat.key AS process_type,
        (stat.value->>'time')::numeric AS process_time,
        stat.value->>'type' AS process_type_category
    FROM process p
    CROSS JOIN jsonb_each(p.process_stat_json) stat
),
uid_total_time AS (
    -- 计算每个uid的总time之和
    SELECT
        uid,
        SUM(process_time) AS total_time
    FROM process_stats
    GROUP BY uid
)
-- 筛选符合条件的对象
SELECT
    ps.id,
    ps.uid,
    ps.process_type,
    ps.process_time,
    utt.total_time,
    ROUND((ps.process_time / utt.total_time) * 100, 2) AS percentage
FROM process_stats ps
JOIN uid_total_time utt ON ps.uid = utt.uid
WHERE
    ps.process_type_category = 'Unknown'
    AND ps.process_time > utt.total_time * 0.1
ORDER BY ps.id;

查询说明 #

1. process_stats CTE：使用jsonb_each将每个process_stat_json的顶层键值对展开，提取出每个子对象的time、type及对应的类型名称（如"Type A"）。
2. uid_total_time CTE：按uid分组，计算每个用户的所有time总和。
3. 最终筛选：关联两个CTE，筛选出type为Unknown且time超过总时间10%的记录，同时返回占比便于验证。

执行结果 #

该查询会返回符合条件的两条记录：

• id=1：Type A的time=500，总时间=585，占比≈85.47%
• id=4：Type D的time=60，总时间=300，占比=20.00%

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