How to extract data from json object and json array in same POSTGRESQL query

Question

I have a column(name: additional_information) in the database having mode of payments recorded in Json Format like this:

First Scenario:

{
   "cash":{
      "use":1,
      "amount":"285"
   },
   "card_payment":[
      {
         "method":"Debit Card",
         "use":1,
         "amount":"5900",
         "details":"081958"
      },
      {
         "method":"Debit Card",
         "use":1,
         "amount":"3245",
         "details":"379613"
      }
   ],
   "method_title":"POS Payment"
}

Second Scenario:

{
   "method_title":"Cash On Delivery",
   "instructions":""
}

Third Scenaario:

{
   "others":[
      {
         "use":1,
         "amount":"649",
         "method":"Phonepe",
         "details":"2022040089 "
      },
      {
         "use":1,
         "amount":"59",
         "method":"Phonepe",
         "details":"T22040584749376 "
      }
   ],
   "method_title":"POS Payment"
}

I want to extract all the necessary info out of this For example

cash
card
others others_type

I have tried this:

 select replace(json_extract_path(sop.additional_information::json,'cash','amount')::text,'"','') AS cash,
              json_array_elements_text(json_extract_path(sop.additional_information::json,'card_payment'))::json->>'amount' AS card,
              json_array_elements_text(json_extract_path(sop.additional_information::json,'others'))::json->>'amount' AS custom,
              json_array_elements_text(json_extract_path(sop.additional_information::json,'others'))::json->>'method' AS custom_type,
              case when sop.method = 'cashondelivery' then so.grand_total::text end as cash2,
              case when sop.method = 'paytm' then so.grand_total::text end as custom2 from table sop

I am finding difficulty in adding all the cards value if there are more than 1 card used. I hope I explained my questions clearly. Thanks in advance.

Answer 1

For extracting elements (keys and values) you can use jsonb_each function. For extracting array elements you can use jsonb_array_elements function. And also you can use jsonb_typeof for gets type of key values. For example:

select 
    t2.pkey,
    t2.pvalue,   
    jsonb_typeof(t2.pvalue) as valuetype 
from sample_table t1 
cross join jsonb_each(t1.json_text) t2(pkey, pvalue)

Return:

pkey	pvalue	valuetype
cash	{"use": 1, "amount": "285"}	object
card_payment	[{"use": 1, "amount": "5900", "method": "Debit Card", "details": "081958"}, {"use": 1, "amount": "3245", "method": "Debit Card", "details": "379613"}]	array
method_title	"POS Payment"	string
others	[{"use": 1, "amount": "649", "method": "Phonepe", "details": "2022040089 "}, {"use": 1, "amount": "59", "method": "Phonepe", "details": "T22040584749376 "}]	array
method_title	"POS Payment"	string
instructions	""	string
method_title	"Cash On Delivery"	string

Which type of values is array or object, for these values you must use jsonb_each function again. But Recommended creating recursive function sub arrays and sub json's. I wrote an SQL query sample for extracting all keys and values, but without recursive.

with json_table as materialized (
    select 
        t2.pkey,
        t2.pvalue,   
        jsonb_typeof(t2.pvalue) as valuetype 
    from test.users t1 
    cross join jsonb_each(t1.json_text) t2(pkey, pvalue)
    where t1.id in (1,3,123)
)
select pkey, pvalue from json_table where valuetype not in ('object', 'array')
union all 
select t2.pkey, t2.pvalue from json_table t1
cross join jsonb_each(t1.pvalue) t2(pkey, pvalue)
where t1.valuetype in ('object')
union all 
select m2.pkey, m2.pvalue 
from 
    (
        select t1.pkey, t2.pvalue from json_table t1
        cross join jsonb_array_elements(t1.pvalue) t2(pvalue)
        where t1.valuetype in ('array')
    ) m1 
cross join jsonb_each(m1.pvalue) m2(pkey, pvalue);

And result of this query:

pkey	pvalue
method_title	"POS Payment"
method_title	"POS Payment"
instructions	""
method_title	"Cash On Delivery"
use	1
amount	"285"
use	1
amount	"5900"
method	"Debit Card"
details	"081958"
use	1
amount	"3245"
method	"Debit Card"
details	"379613"
use	1
amount	"649"
method	"Phonepe"
details	"2022040089 "
use	1
amount	"59"
method	"Phonepe"
details	"T22040584749376 "