CODEWITHSUNDEEP
postgresql
Wrishi
Wrishi

Reputation: 33

How to extract from json using pgsql?

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

Upvotes: 0

Views: 106

Answers (1)

user330315
user330315

Reputation:

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)

Upvotes: 1

Related Questions