Create Table
CREATE TABLE json_data
( unique_id NUMBER,
json_col CLOB
CONSTRAINT must_be_json
CHECK ( json_col IS JSON ) )
Table created.
Row 1
INSERT INTO json_data
VALUES(1,
'{"Name" : "Marty",
"Reason" : [ { "Medium" : "Movie",
"Title" : "Madagascar",
"Year" : 2005 },
{ "Medium" : "Movie",
"Title" : "Madagascar Escape 2 Africa",
"Year" : 2008 },
{ "Medium" : "Movie",
"Title" : "Madagascar 3 : Europes Most Wanted",
"Year" : 2012 } ] }')
1 row(s) inserted.
Row 2
INSERT INTO json_data
VALUES(2,
'{"Name" : "Zigby",
"Reason" : [ { "Medium" : "Book",
"Title" : "Zigby Camps Out",
"Year" : 2002 },
{ "Medium" : "Book",
"Title" : "Zigby Hunts For Treasure",
"Year" : 2002 },
{ "Medium" : "Book",
"Title" : "Zigby and the Ant Invaders",
"Year" : 2003 } ] }')
1 row(s) inserted.
Find Name
SELECT jd.json_col.Name
FROM json_data jd
| NAME | Marty | Zigby |
|---|
Get Reason
SELECT jd.json_col.Reason
FROM json_data jd
| REASON | [{"Medium":"Movie","Title":"Madagascar","Year":2005},{"Medium":"Movie","Title":"Madagascar Escape 2 Africa","Year":2008},{"Medium":"Movie","Title":"Madagascar 3 : Europes Most Wanted","Year":2012}] | [{"Medium":"Book","Title":"Zigby Camps Out","Year":2002},{"Medium":"Book","Title":"Zigby Hunts For Treasure","Year":2002},{"Medium":"Book","Title":"Zigby and the Ant Invaders","Year":2003}] |
|---|
All Titles
SELECT jd.json_col.Reason.Title
FROM json_data jd
| REASON | ["Madagascar","Madagascar Escape 2 Africa","Madagascar 3 : Europes Most Wanted"] | ["Zigby Camps Out","Zigby Hunts For Treasure","Zigby and the Ant Invaders"] |
|---|
First Title
SELECT jd.json_col.Reason[0].Title
FROM json_data jd
| REASON | Madagascar | Zigby Camps Out |
|---|
Second Title
SELECT jd.json_col.Reason[1].Title
FROM json_data jd
| REASON | Madagascar Escape 2 Africa | Zigby Hunts For Treasure |
|---|
All Titles
SELECT jd.json_col.Reason[*].Title
FROM json_data jd
| REASON | ["Madagascar","Madagascar Escape 2 Africa","Madagascar 3 : Europes Most Wanted"] | ["Zigby Camps Out","Zigby Hunts For Treasure","Zigby and the Ant Invaders"] |
|---|
All Titles With 'ad'
SELECT jd.json_col.Reason[*].Title
FROM json_data jd
WHERE jd.json_col.Reason[*].Title LIKE '%ad%'
| REASON | ["Madagascar","Madagascar Escape 2 Africa","Madagascar 3 : Europes Most Wanted"] | ["Zigby Camps Out","Zigby Hunts For Treasure","Zigby and the Ant Invaders"] |
|---|
3rd Title With 'ad' (JSON Return)
SELECT jd.json_col.Reason[*].Title
FROM json_data jd
WHERE jd.json_col.Reason[2].Title LIKE '%ad%'
| REASON | ["Madagascar","Madagascar Escape 2 Africa","Madagascar 3 : Europes Most Wanted"] | ["Zigby Camps Out","Zigby Hunts For Treasure","Zigby and the Ant Invaders"] |
|---|
No 4th Value so NULL
SELECT jd.json_col.Reason[3].Title
FROM json_data jd
| REASON | - | - |
|---|