Home » SQL & PL/SQL » SQL & PL/SQL » Parse Json using Oracle SQL (Oracle DB version 12.1.02)
Parse Json using Oracle SQL [message #683596] |
Fri, 05 February 2021 04:41 |
|
Shre
Messages: 4 Registered: October 2020
|
Junior Member |
|
|
I am trying to parse JSON in Oracle SQL.
Oracle DB version 12.1.02
{
"Rownum": "1",
"Name": "John",
"AddressArray":["Address1", "Address2"],
"TextObj":[{
"mName" : "Carol",
"lName" : "Cena"
},
{
"mName" : "Mark",
"lName" : "Karlo"
}
]
}
output should look like below:
Rownum Name AddressArray mName lname
1 John Address1 Carlo Cena
1 John Address1 Mark Karlo
1 John Address2 Carlo Cena
1 John Address2 Mark Karlo
Tried below code but not getting output like above:
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 '$')
)
);
[Updated on: Fri, 05 February 2021 09:19] Report message to a moderator
|
|
|
Re: Parse Json using Oracle SQL [message #683597 is a reply to message #683596] |
Fri, 05 February 2021 10:33 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You can do something like this:
SQL> with
2 data as (
3 select
4 '{
5 "Rownum": "1",
6 "Name": "John",
7 "AddressArray":["Address1", "Address2"],
8 "TextObj":[{
9 "mName" : "Carol",
10 "lName" : "Cena"
11 },
12 {
13 "mName" : "Mark",
14 "lName" : "Karlo"
15 }
16 ]
17 }' data from dual
18 )
19 select rn, name, address, mName, lName
20 from data,
21 json_table(data, '$'
22 columns (
23 rn number path '$.Rownum',
24 Name varchar2(10) path '$.Name',
25 addresses varchar2(30) format json path '$.AddressArray',
26 TextObj varchar2(80) format json path '$.TextObj'
27 )
28 ) p1,
29 json_table('{adresses:'||addresses||'}', '$.adresses[*]'
30 columns (
31 address varchar2(20) path '$'
32 )
33 ) p2,
34 json_table('{names:'||TextObj||'}', '$.names[*]'
35 columns (
36 mName varchar2(10) path '$.mName',
37 lName varchar2(10) path '$.lName'
38 )
39 ) p3
40 /
RN NAME ADDRESS MNAME LNAME
---------- ---------- -------------------- ---------- ----------
1 John Address1 Carol Cena
1 John Address2 Carol Cena
1 John Address1 Mark Karlo
1 John Address2 Mark Karlo
4 rows selected.
Not very pretty, maybe Solomon will see this topic and provide a better solution.
|
|
|
Re: Parse Json using Oracle SQL [message #683610 is a reply to message #683597] |
Sat, 06 February 2021 06:02 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
with data as (
select '{
"Rownum": "1",
"Name": "John",
"AddressArray":["Address1", "Address2"],
"TextObj":[
{
"mName" : "Carol",
"lName" : "Cena"
},
{
"mName" : "Mark",
"lName" : "Karlo"
}
]
}' json_doc
from dual
)
select ja.*,
jt.*
from data,
json_table(
json_doc,
'$'
columns(
rn number path '$.Rownum',
name varchar2(10) path '$.Name',
nested path '$.AddressArray[*]'
columns(
address varchar2(10) path '$'
)
)
) ja,
json_table(
json_doc,
'$.TextObj[*]'
columns(
mname varchar2(10) path '$.mName',
lname varchar2(10) path '$.lName'
)
) jt
/
RN NAME ADDRESS MNAME LNAME
---------- ---------- ---------- ---------- ----------
1 John Address1 Carol Cena
1 John Address1 Mark Karlo
1 John Address2 Carol Cena
1 John Address2 Mark Karlo
SQL>
SY.
|
|
|
|
Re: Parse Json using Oracle SQL [message #683612 is a reply to message #683610] |
Sat, 06 February 2021 06:21 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Oops, this will not work when JSON contains multiple "Rownum":
SQL> with data as (
2 select '[
3 {
4 "Rownum": "1",
5 "Name": "John",
6 "AddressArray":["Address1", "Address2"],
7 "TextObj":[
8 {
9 "mName" : "Carol",
10 "lName" : "Cena"
11 },
12 {
13 "mName" : "Mark",
14 "lName" : "Karlo"
15 }
16 ]
17 },
18 {
19 "Rownum": "2",
20 "Name": "Jim",
21 "AddressArray":["Address3", "Address4"],
22 "TextObj":[
23 {
24 "mName" : "Sam",
25 "lName" : "Adams"
26 },
27 {
28 "mName" : "Jose",
29 "lName" : "Cuervo"
30 }
31 ]
32 }
33 ]' json_doc
34 from dual
35 )
36 select ja.*,
37 jt.*
38 from data,
39 json_table(
40 json_doc,
41 '$[*]'
42 columns(
43 rn number path '$.Rownum',
44 name varchar2(10) path '$.Name',
45 nested path '$.AddressArray[*]'
46 columns(
47 address varchar2(10) path '$'
48 )
49 )
50 ) ja,
51 json_table(
52 json_doc,
53 '$.TextObj[*]'
54 columns(
55 mname varchar2(10) path '$.mName',
56 lname varchar2(10) path '$.lName'
57 )
58 ) jt
59 /
RN NAME ADDRESS MNAME LNAME
---------- ---------- ---------- ---------- ----------
1 John Address1 Carol Cena
1 John Address1 Mark Karlo
1 John Address1 Sam Adams
1 John Address1 Jose Cuervo
1 John Address2 Carol Cena
1 John Address2 Mark Karlo
1 John Address2 Sam Adams
1 John Address2 Jose Cuervo
2 Jim Address3 Carol Cena
2 Jim Address3 Mark Karlo
2 Jim Address3 Sam Adams
RN NAME ADDRESS MNAME LNAME
---------- ---------- ---------- ---------- ----------
2 Jim Address3 Jose Cuervo
2 Jim Address4 Carol Cena
2 Jim Address4 Mark Karlo
2 Jim Address4 Sam Adams
2 Jim Address4 Jose Cuervo
16 rows selected.
SQL>
Corrected solution:
with data as (
select '[
{
"Rownum": "1",
"Name": "John",
"AddressArray":["Address1", "Address2"],
"TextObj":[
{
"mName" : "Carol",
"lName" : "Cena"
},
{
"mName" : "Mark",
"lName" : "Karlo"
}
]
},
{
"Rownum": "2",
"Name": "Jim",
"AddressArray":["Address3", "Address4"],
"TextObj":[
{
"mName" : "Sam",
"lName" : "Adams"
},
{
"mName" : "Jose",
"lName" : "Cuervo"
}
]
}
]' json_doc
from dual
)
select ja.*,
jt.mname,
jt.lname
from data,
json_table(
json_doc,
'$[*]'
columns(
rn number path '$.Rownum',
name varchar2(10) path '$.Name',
nested path '$.AddressArray[*]'
columns(
address varchar2(10) path '$'
)
)
) ja,
json_table(
json_doc,
'$[*]'
columns(
rn number path '$.Rownum',
nested path '$.TextObj[*]'
columns(
mname varchar2(10) path '$.mName',
lname varchar2(10) path '$.lName'
)
)
) jt
where jt.rn = ja.rn
/
RN NAME ADDRESS MNAME LNAME
---------- ---------- ---------- ---------- ----------
1 John Address1 Carol Cena
1 John Address1 Mark Karlo
1 John Address2 Carol Cena
1 John Address2 Mark Karlo
2 Jim Address3 Sam Adams
2 Jim Address3 Jose Cuervo
2 Jim Address4 Sam Adams
2 Jim Address4 Jose Cuervo
8 rows selected.
SQL>
SY.
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:34:36 CDT 2024
|