Dot Notation In Arrays

View All Scripts Login to Run Script

Script Name Dot Notation In Arrays
Description Navigating through JSON arrays using dot notation.
Area SQL General / JSON
Contributor Darryl Hurley
Created Sunday October 29, 2017

Statement 1

Create a table with a JSON column

Create Table

CREATE TABLE json_data   
( unique_id NUMBER,   
  json_col CLOB   
     CONSTRAINT must_be_json   
     CHECK ( json_col IS JSON ) )

Table created.

Statement 2

Create one row including JSON data.

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.

Statement 3

Create another row including JSON data

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.

Statement 4

Finds scalar Name values

Find Name

SELECT jd.json_col.Name    
  FROM json_data jd


NAME
Marty
Zigby

2 rows selected.

NAME
Marty
Zigby

Statement 5

Finds JSON Reason values

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}]

2 rows selected.

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}]

Statement 6

Finds all scalars at Reason.Title and returns them as JSON

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

2 rows selected.

REASON
["Madagascar","Madagascar Escape 2 Africa","Madagascar 3 : Europes Most Wanted"]
["Zigby Camps Out","Zigby Hunts For Treasure","Zigby and the Ant Invaders"]

Statement 7

Finds the first occurrence of Title in Reason and returns it as a scalar

First Title

SELECT jd.json_col.Reason[0].Title    
  FROM json_data jd


REASON
Madagascar
Zigby Camps Out

2 rows selected.

REASON
Madagascar
Zigby Camps Out

Statement 8

Finds the second occurrence of Title in Reason and returns it as scalar

Second Title

SELECT jd.json_col.Reason[1].Title    
  FROM json_data jd


REASON
Madagascar Escape 2 Africa
Zigby Hunts For Treasure

2 rows selected.

REASON
Madagascar Escape 2 Africa
Zigby Hunts For Treasure

Statement 9

Finds all Reason.Title values as JSON

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

2 rows selected.

REASON
["Madagascar","Madagascar Escape 2 Africa","Madagascar 3 : Europes Most Wanted"]
["Zigby Camps Out","Zigby Hunts For Treasure","Zigby and the Ant Invaders"]

Statement 10

Finds all Reason.Title values if any of them contain 'ad' and returns them as JSON

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

2 rows selected.

REASON
["Madagascar","Madagascar Escape 2 Africa","Madagascar 3 : Europes Most Wanted"]
["Zigby Camps Out","Zigby Hunts For Treasure","Zigby and the Ant Invaders"]

Statement 11

Finds all Reason.Title values if the 3rd contains 'ad' and returns them as JSON

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

2 rows selected.

REASON
["Madagascar","Madagascar Escape 2 Africa","Madagascar 3 : Europes Most Wanted"]
["Zigby Camps Out","Zigby Hunts For Treasure","Zigby and the Ant Invaders"]

Statement 12

Returns NULL because neither row has a 4th title

No 4th Value so NULL

SELECT jd.json_col.Reason[3].Title       
  FROM json_data jd


REASON
 - 
 - 

2 rows selected.

REASON
-
-

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database