I have a nested structure json . How to extract the specific elements(keys) and values?
How to access " sky: selling":"1" Or "U1":"0000" ? I tried json_object_keys and json_array_elements for extracting the array . But I don't know exactly how to do this query. Example code:
Table- record and column name : report
{
"PIname": {
"n1": "x1",
"n2": "x2",
"params": {
"S1": {
"code1": "y1",
"Code2": "y2",
},
"id": "2d",
"Dest": {
"Code3": "mi"
}
},
"PIDataArea": {
"m1": null,
"PInven": {
"head": {
"Code4": "Increase",
"line": "2020-01"
},
"PILine": [
{
"u1": "0000",
"u2": "0",
"u3": "1",
"modes": {
"@cID": "Sng",
"#txt": "12.21"
} },
{
"Qualify": ".0001",
"QOrder": "1",
"UPriceAmt": {
"@cID": "sng",
"#txt": "13" },
"sky:Qa": ".000",
"sky:Partcode": {
"@c1ID": "a"
},
"sky:SCode": "Ni",
"sky:PItem": {
"sky:ID": "h"
},
"sky:Forest": {
"sky:q1": [
{
"sky:selling": "1"
}
{
"sky:selling": "0"
}
]
} } }} }}
I tried lot ,one example query here like, Select * from record r Where exists( select report->'sky: selling' from json_each(r.report) b where b.value->>'sky:selling' Ilike '0');
