PostgreSQL 11.5中统计JSONB数据里各标题的发布次数

我使用PostgreSQL 11.5，数据库books表的result字段存储着如下格式的JSONB数据：

[{"name":"$.publishedTitle", "value":"Code"},{"name":"$.publishedYear","value":"1972"}]
[{"name":"$.publishedTitle", "value":"Test"},{"name":"$.publishedYear","value":"2020"}]
[{"name":"$.publishedTitle", "value":"Code"},{"name":"$.publishedYear","value":"2019"}]

我需要统计每个标题对应的不同发布年份数量，期望结果如下：

我尝试了以下SQL但未得到正确结果，请求修正：

SELECT distinct(b.field_value) AS publishedYearCount, COUNT(*)
  FROM (SELECT *
          FROM (SELECT (jsonb_array_elements(result) ::jsonb) ->> 'name' field_name,
                       (jsonb_array_elements(result) ::jsonb) ->> 'value' field_value,
                  FROM books
                 WHERE bookstore_id = '3') a
         WHERE a.field_name in ('$.publishedTitle', '$.publishedYear')) b
 GROUP BY b.field_value

原SQL的问题 #

原SQL存在两个核心错误：

1. 两次调用jsonb_array_elements(result)会导致同一条记录的标题和年份产生笛卡尔积，数据关联关系混乱；
2. 直接把标题和年份的value混在一起分组统计，无法区分哪个value是标题、哪个是年份，逻辑完全偏离需求。

修正后的SQL（两种可选方法） #

方法一：聚合为JSON对象后提取（推荐）

这种方法先把同一条记录的属性聚合成一个JSON对象，再提取标题和年份，逻辑更清晰：

SELECT 
  obj ->> '$.publishedTitle' AS title,
  COUNT(DISTINCT obj ->> '$.publishedYear') AS publishedYearCount
FROM (
  SELECT 
    jsonb_object_agg(elem ->> 'name', elem ->> 'value') AS obj
  FROM books,
       jsonb_array_elements(result) elem
  WHERE bookstore_id = '3'
  GROUP BY books.id -- 这里用表的主键（比如id）确保同一条记录的属性聚合在一起
) t
GROUP BY title
ORDER BY title;

方法二：分别提取标题和年份再关联

如果不想用聚合函数，也可以分别筛选出标题和年份的记录，再通过主键关联：

SELECT 
  t_title.value AS title,
  COUNT(DISTINCT t_year.value) AS publishedYearCount
FROM (
  SELECT 
    id,
    elem ->> 'value' AS value
  FROM books,
       jsonb_array_elements(result) elem
  WHERE bookstore_id = '3'
    AND elem ->> 'name' = '$.publishedTitle'
) t_title
JOIN (
  SELECT 
    id,
    elem ->> 'value' AS value
  FROM books,
       jsonb_array_elements(result) elem
  WHERE bookstore_id = '3'
    AND elem ->> 'name' = '$.publishedYear'
) t_year ON t_title.id = t_year.id
GROUP BY t_title.value
ORDER BY t_title.value;

关键说明 #

• 两种方法都用COUNT(DISTINCT ...)统计不同的年份数量，确保重复年份不会被重复计数；
• 必须保证标题和年份属于同一条原始记录，要么通过聚合关联，要么通过主键关联，避免数据错位。

内容的提问来源于stack exchange，提问作者Xux.rd