PostgreSQL如何合并jsonb键并统计各键对应值的出现次数

基础信息 #

产品表products字段如下：

• id：整数类型，产品主键
• name：文本类型，产品名称
• category：文本类型，产品分类
• specs：JSONB类型，存储动态规格键值对

样例数据：

需求是传入指定分类，统计该分类下所有specs键对应不同值的出现次数，最终输出嵌套JSONB结构，格式参考：

{
  "brand": {"brand1": 2, "brand20": 1},
  "color": {"red": 1, "white": 1},
  "memory": {"8GB": 2, "32GB": 1}
}

可直接运行的SQL #

SELECT jsonb_object_agg(spec_key, value_count_obj) AS stat_result
FROM (
  SELECT
    spec_key,
    jsonb_object_agg(spec_value, val_count) AS value_count_obj
  FROM (
    SELECT
      entry.key AS spec_key,
      entry.value AS spec_value,
      COUNT(*) AS val_count
    FROM products
    LATERAL jsonb_each(specs) entry
    WHERE category = $1 -- $1为传入的分类参数，例如'phones'
    GROUP BY entry.key, entry.value
  ) t_val_cnt
  GROUP BY spec_key
) t_key_obj;

逻辑拆解 #

• 用LATERAL jsonb_each(specs)把每行产品的specs对象拆成独立的键值对行，一行对应一个规格项
• 最内层分组按「规格键+规格值」聚合，算出每个键下每个值的出现次数
• 第一层jsonb_object_agg按规格键分组，把同键下的值和计数拼成{值: 次数}的JSONB对象
• 最外层jsonb_object_agg把所有规格键和对应的计数对象拼成最终的顶层JSON结果

如果需要统一值的大小写再统计（比如把8GB、8gb视为同一个值），把entry.value替换成to_jsonb(lower(entry.value #>> '{}'))即可，会把所有字符串值转成小写后再计数。

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