Link to home
Start Free TrialLog in
Avatar of angel7170
angel7170Flag for United States of America

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.

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";

Open in new window


Please assist.

Thank you
SampleData.txt
Book2.xlsx
Avatar of HainKurt
HainKurt
Flag of Canada image

I dont see any relation between excel and text file attached...

what is your table definition
what data do you have currently (give a few sample)
for that sample, what do you want to get...
Avatar of Sean Stuber
Sean Stuber

The sample data isn't JSON
Avatar of angel7170

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"
        }]
    }
}
]
}
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial