CODEWITHSUNDEEP
sqloracle-databaseplsql
Shre
Shre

Reputation: 35

Parse Json using Oracle SQL

I am trying to parse JSON in Oracle SQL.

Oracle DB version 12.1.0.2

{
 "Rownum": "1",
 "Name": "John",
 "AddressArray":["Address1", "Address2"],
 "TextObj":[{
             "mName" : "Carol",
             "lName" : "Cena"
            },
            {
             "mName" : "Mark",
             "lName" : "Karlo"
            }
           ]
}

output should look like below:

enter image description here

Upvotes: 0

Views: 2514

Answers (1)

ekochergin
ekochergin

Reputation: 4129

I suppose "nested" will do the trick

select * from json_Table('{"Rownum": "1", "Name": "John", "AddressArray":["Address1", "Address2"], "TextObj":[{"mName" : "Carol","lName" : "Cena",}]}', '$' columns (rownr number path '$.Rownum',
                                            name varchar2(100) path '$.Name',
                                            mName varchar2(100) path '$.TextObj[*].mName',
                                            lName varchar2(100) path '$.TextObj[*].lName',
                                            nested path '$.AddressArray[*]' columns(AddressArray varchar2(100) path '$') 
                                           ));

My output:

ROWNR NAME MNAME LNAME ADDRESSARRAY
1 John Carol Cena Address1
1 John Carol Cena Address2

Upvotes: 1

Related Questions