Combine month and year from jsonb data PostgreSQL 11.5

user_17243687 0
Viewed 58

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

jsonb postgresql sql
2 Answers

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}'
user_67593680

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

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

Demo

If concatenated "publishedMonthYear" values needed to be counted, then consider using a subquery( as expressed within the comment )

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"

Demo

user_58413060
Related