angel7170
asked on
Parse JSON Object and value in Oracle SQL
Hello,
I am trying to parse a JSON format data that is stored in oracle CLOB data column. There are multiple/nested objects in the JSON data so I need a query to automatically get all the object/parent name and the value related. Please see attached is the sample example of the JSON data and the expected output.
I tried the below query but it only gives the value and not the object/Key name.
Please assist.
Thank you
SampleData.txt
Book2.xlsx
I am trying to parse a JSON format data that is stored in oracle CLOB data column. There are multiple/nested objects in the JSON data so I need a query to automatically get all the object/parent name and the value related. Please see attached is the sample example of the JSON data and the expected output.
I tried the below query but it only gives the value and not the object/Key name.
SELECT jt.*
FROM JSON_DOCUMENT_lob a,
JSON_TABLE(json_msg, '$."Additional Statement No".*'
COLUMNS (
"Attribute/Parent" varchar2(32000) PATH '$.parent()',
"Value" varchar2(32000) PATH '$'
)) "JT";
Please assist.
Thank you
SampleData.txt
Book2.xlsx
The sample data isn't JSON
ASKER
Sorry, it didn't attach my sample JSON file. So I am pasting it.
{
"TM": [
{"Additional Statement No": {
"description": "If you are not ready to provide additional statements at this time, Reduced Fee application.\n",
"allowExtension": false,
"fields": [{
"name": "Fee Appplication",
"type": "Application - Reduced Fee -"
}, {
"type": "invisible"
}, {
"type": "invisible"
}, {
"type": "invisible"
}, {
"type": "invisible"
}]
},
"Additional Statement Yes": {
"description": "Please provide all the necessary documents related to the application\n",
"allowExtension": false,
"fields": [{
"name": "Yes, I am ready to pay all fees",
"type": "Pay All Fees Yes"
}, {
"type": "invisible"
}, {
"type": "invisible"
}, {
"name": "No, I prefer to pay some fees later",
"type": "Pay All Fees No"
}, {
"type": "invisible"
}, {
"type": "invisible"
}, {
"type": "invisible"
}, {
"type": "invisible"
}, {
"type": "invisible"
}]
}
}
]
}
{
"TM": [
{"Additional Statement No": {
"description": "If you are not ready to provide additional statements at this time, Reduced Fee application.\n",
"allowExtension": false,
"fields": [{
"name": "Fee Appplication",
"type": "Application - Reduced Fee -"
}, {
"type": "invisible"
}, {
"type": "invisible"
}, {
"type": "invisible"
}, {
"type": "invisible"
}]
},
"Additional Statement Yes": {
"description": "Please provide all the necessary documents related to the application\n",
"allowExtension": false,
"fields": [{
"name": "Yes, I am ready to pay all fees",
"type": "Pay All Fees Yes"
}, {
"type": "invisible"
}, {
"type": "invisible"
}, {
"name": "No, I prefer to pay some fees later",
"type": "Pay All Fees No"
}, {
"type": "invisible"
}, {
"type": "invisible"
}, {
"type": "invisible"
}, {
"type": "invisible"
}, {
"type": "invisible"
}]
}
}
]
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
While this is possible, it's not something the oracle json functions were intended to do.
I spend some time to get the element names but not successful and gave up :)
either Oracle jSon support is very limited or documentation is not good enough...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
what is your table definition
what data do you have currently (give a few sample)
for that sample, what do you want to get...