sql - 结合来自 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	发表月年
1	04-1972
2	07-2020

SELECT b.field_value AS publishedMonthYear, 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 ('$.publishedMonth', '$.publishedYear')) b
 GROUP BY b.field_value

预先感谢您的任何帮助

标签： sqlpostgresqljsonb

Answer 1

示例表和数据结构：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}'