结合 jsonb 数据 PostgreSQL 11.5 中的月份和年份

Question

我有PostgreSQL 11.5，类似于这个jsonb数据：

[{"name":"$.publishedMonth", "value":"04"},{"name":"$.publishedYear","value":"1972"}]
[{"name":"$.publishedMonth", "value":"07"},{"name":"$.publishedYear","value":"2020"}]

我想要的结果是：

日

id	已发布月年
	1972年04月1
2	07-2020

一个查询包括表id列的聚合books在没有子查询的情况下交叉连接到JSONB_ARRAY_ELEMENTS()就足够了，例如

SELECT id, STRING_AGG(j ->> 'value', '-' ORDER BY j ->> 'name') AS "publishedMonthYear"
FROM books,
JSONB_ARRAY_ELEMENTS(result) AS arr(j)
WHERE bookstore_id = 3
AND j ->> 'name' IN ('$.publishedMonth','$.publishedYear')
GROUP BY id

考虑按j ->> 'name'排序，这将按字母顺序提取所需的值($.publishedMonth和$.publishedYear)。

演示

如果需要计算串联的"已发布月份"值，则考虑使用子查询(在注释中表示)

SELECT "publishedMonthYear", COUNT(*)
FROM
( SELECT STRING_AGG(j ->> 'value', '-' ORDER BY j ->> 'name') AS "publishedMonthYear"
FROM books,
JSONB_ARRAY_ELEMENTS(result) AS arr(j)
WHERE bookstore_id = 3
AND j ->> 'name' IN ('$.publishedMonth','$.publishedYear')
GROUP BY id ) AS b
GROUP BY "publishedMonthYear"  

演示

示例表和数据结构：dbfiddle

select id, string_agg(value - >> 'value', '-' order by value - >> 'name')
from book b
cross join jsonb_array_elements(b.result ::jsonb) e
group by id
having array_agg(value - >> 'name'
order by value - >> 'name') = '{$.publishedMonth,$.publishedYear}'