How to extract from json using pgsql?

Question

I have a field 'item_json' in item table and json like this below.

{
  "MPA0000004":{
    "2020-21":{
      "Q1":"100",
      "Q2":"25",
      "Q3":"",
      "Q4":""
    },
    "2021-22":{
      "Q1":"5",
      "Q2":"",
      "Q3":"",
      "Q4":""
    }
  },
  "MPA0000006":{
    "2020-21":{
      "Q1":"",
      "Q2":"2",
      "Q3":"",
      "Q4":"4"
    },
    "2021-22":{
      "Q1":"",
      "Q2":"6",
      "Q3":"",
      "Q4":"8"
    }
  },
  "MPA0000007":{
    "2020-21":{
      "Q1":"",
      "Q2":"",
      "Q3":"3",
      "Q4":""
    },
    "2021-22":{
      "Q1":"",
      "Q2":"",
      "Q3":"7",
      "Q4":""
    }
  }
}

Need data like this :

ITEM_ID | YEAR | Q1 | Q2 |Q3 | Q4

user330315 · Accepted Answer · 2021-07-30 17:13:59Z

1

You need to unnest the nested value twice so that you can extract the quarters by year:

select i.item_id,  
       y.year,
       y.numbers ->> 'Q1' as q1,
       y.numbers ->> 'Q2' as q2,
       y.numbers ->> 'Q3' as q3,
       y.numbers ->> 'Q4' as q4
from data d
  cross join jsonb_each(d.item_json) as i(item_id, item)
  cross join jsonb_each (i.item) as y(year, numbers)

answered Jul 30, 2021 at 17:13

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to extract from json using pgsql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related