Parse Json using Oracle SQL

Question

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:

You can do this using the JSON_TABLE function. What have you tried ? — Koen Lostrie
– Koen Lostrie, Commented Feb 5, 2021 at 10:59
I tried with JSON_TABLE but was not able to parse AddressArray. — Shre
– Shre, Commented Feb 5, 2021 at 11:04
@Shre - please edit your question to include the code from your attempt with json_table and the output or errors you got. We can then (hopefully) explain what you did wrong. — Alex Poole
– Alex Poole, Commented Feb 5, 2021 at 11:28
Please note there is a syntax error in the JSON. A comma is missing after ""Address2"]". So it has to be "... "Address2"], ..." — ekochergin
– ekochergin, Commented Feb 5, 2021 at 12:09

ekochergin · Accepted Answer · 2021-02-05 11:40:36Z

1

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

edited Feb 5, 2021 at 11:40

answered Feb 5, 2021 at 11:35

ekochergin

4,1592 gold badges14 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Shre Over a year ago

Thanks @ekochergin. This don't work if TextObj has one more object like below code "TextObj":[{ "mName" : "Carol", "lName" : "Cena" }, { "mName" : "Mark", "lName" : "Karlo" } ]

ekochergin Over a year ago

@Shre, it makes TextObj an array as well. In this case you need to process it exactly as AdressArray

ekochergin Over a year ago

@Shre, sorry that make the question harder and I don't have the solution right now. Try this documentation docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/… or submit another question. Perhaps there is someone who could help to crack multiple JSON arrays

Shre Over a year ago

I tried this, But not getting expected result.

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

ekochergin Over a year ago

@Shre, it'd be better to submit another question. I'm not sure I'll be able to find the solution until next week

Collectives™ on Stack Overflow

Parse Json using Oracle SQL

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related