PostgreSQL中统计JSON数据顶级键出现次数并降序排列

架构老林

2026-5-12

统计PostgreSQL JSON顶级键的出现次数并降序排列

嘿，这问题我熟！要统计JSON字段里顶级键的出现次数，PostgreSQL提供了很方便的JSON函数来处理，下面分两种情况给你解决方案：

1. 先获取键和对应次数的明细列表

如果你想先看到清晰的「键-次数」对应关系，可以用这个SQL：

SELECT 
    key, 
    COUNT(*) AS occurrence
FROM 
    your_table, -- 替换成你的实际表名
    json_object_keys(contents) AS key
GROUP BY 
    key
ORDER BY 
    occurrence DESC;

逻辑解释：

json_object_keys(contents) 会把每一行contents字段里的JSON顶级键拆分成单独的行，比如原来一行{"49": ...}会生成一行key='49'的记录
之后用GROUP BY key分组，COUNT(*)统计每个键在全表中出现的总次数
ORDER BY occurrence DESC 让结果按次数从多到少排序

如果你的contents字段是jsonb类型（推荐使用，性能更优），只需要把json_object_keys换成jsonb_object_keys即可。

2. 直接生成你想要的JSON数组格式

如果想直接得到像[{"50":5,"48":4,...}]这样的聚合JSON结果，可以用嵌套查询+JSON聚合函数实现：

SELECT json_agg(json_build_object(key, occurrence)) AS result
FROM (
    SELECT 
        key, 
        COUNT(*) AS occurrence
    FROM 
        your_table, -- 替换成实际表名
        json_object_keys(contents) AS key
    GROUP BY 
        key
    ORDER BY 
        occurrence DESC
) AS counted_keys;