Combine month and year from jsonb data PostgreSQL 11.5-CodePudding

I have PostgreSQL 11.5 with something similar to this jsonb data:

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

My desired result is:

id	publishedMonthYear
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

Thank you in advance for any help

CodePudding user response：

Sample table and data structure: 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}'

CodePudding user response：

A query including an aggregation by id column of the table books cross joined to JSONB_ARRAY_ELEMENTS() without a subquery will suffice such as

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

considering the sorting by j ->> 'name' which will extract the values ($.publishedMonth and $.publishedYear) as desired alphabetically.

Demo