PostgreSQL 11.5中如何从jsonb数据提取月份年份并拼接为年月格式

你当前写法的核心问题是没有保留原始books表的行ID，将所有JSON数组展开后的年月值混在了一起，无法将同一条记录的月份和年份对应拼接。
可以直接按原始行ID分组，使用string_agg聚合拼接年月值，参考SQL如下：

SELECT 
  b.id,
  string_agg(
    elem ->> 'value', 
    '-' 
    ORDER BY CASE elem ->> 'name' 
              WHEN '$.publishedMonth' THEN 1 
              WHEN '$.publishedYear' THEN 2 
            END
  ) AS publishedMonthYear
FROM books b
CROSS JOIN jsonb_array_elements(b.result) elem
WHERE 
  b.bookstore_id = '3'
  AND elem ->> 'name' IN ('$.publishedMonth', '$.publishedYear')
GROUP BY b.id
-- 可选：过滤掉缺少年份/月份的异常记录
-- HAVING COUNT(*) = 2
ORDER BY b.id;

逻辑说明 #

• 用CROSS JOIN jsonb_array_elements展开每条记录的resultJSONB数组，全程保留原始记录的id字段
• 过滤只保留月份、年份两个目标字段
• 按原始记录id分组，用string_agg按「月份在前、年份在后」的固定顺序拼接两个值，得到目标格式
  如果你的业务场景中每条result数组内固定只包含年月两个字段，也可以简化ORDER BY逻辑，直接按elem ->> 'name'排序即可，因为$.publishedMonth字典序天然早于$.publishedYear。

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