JSON: JavaScript Object Notation.

JSON is a syntax for storing and exchanging data.

JSON is text, written with JavaScript object notation.

 
JSON syntax

Data is in key/value pairs.
A field name (in double quotes), followed by a colon, followed by a value.

(Note that in an XML attribute:value pair the attribute is NOT in double quotes.)

Example:

"name":"John"


Curly braces hold JSON objects
JSON objects are written as key/value pairs.

Key/value pairs are separated by commas.

Example:

{ "name":"John", "age":30, "car":null }

 

Note the special value null and that 30 does not need double quotes.

true and false (without double quotes) are also allowed values:

{ "sale":true }

 
Square brackets hold arrays

Example:

[ "Ford", "BMW", "Fiat" ]

 
NESTING:

Objects can be nested as VALUES of a pair inside of objects:

myObj = {
    "name":"John",
    "age":30,
    "cars": {
        "car1":"Ford",
        "car2":"BMW",
        "car3":"Fiat"
    }
 }

Arrays can be nested as VALUES of a pair inside of objects:

{
"name":"John",
"age":30,
"cars":[ "Ford", "BMW", "Fiat" ]
}

An array can contain objects:

{"cars": [
        { "name":"Ford", "model":"Fiesta"},
        { "name":"BMW", "model":"X3" },
        { "name":"Fiat", "model":"Panda" }
    ]
 }


JSON versus XML

JSON is Like XML Because

Both JSON and XML are "self describing" (that means: human readable)
Both JSON and XML are hierarchical (values within values)
Both JSON and XML can be parsed and used by lots of programming languages
 

JSON is Unlike XML Because

JSON doesn't use end tag
JSON is shorter
JSON is quicker to read and write
JSON can use arrays
 

JSON also interacts much better with the widely used JavaScript language.

Diagram representations of JSON vary widely.

We will use the following representation:

{   "name":"John",
    "age":30,
    "cars": [
        { "name":"Ford", "model":"Fiesta" },
        { "name":"BMW", "model":"X3" },
        { "name":"Fiat", "model":"Panda" }
    ]
 }

The first { and last } become the root. It is represented as a box with a {} in there.

Objects become boxes with {} in there.

Arrays become boxes with [] in there.

Names become boxes.

Values are without boxes.


JSON in SQL

 

create table json_documents (
      mydata CLOB
      constraint c1 check (mydata is JSON)      

)
/

NOTE: NO constructor!
 
insert into json_documents values (

   '{
      "FirstName" : "John",
      "LastName"  : "Doe",
      "Address"   : {
                       "Street" : "99 Main Street",
                       "City"   : "Newark",
                       "State"  : "NJ"
                    },
      "Contact"   : {
                       "Email"  : "John.doe@gmail.com",
                       "Phone"  : "(973) 555-1212"
                    }
    }')
/


insert into json_documents values (

   '{
      "FirstName" : "Tom",
      "LastName"  : "Brown",
      "Address"   : {
                       "Street" : "19 Elm Street",
                       "City"   : "Hillside",
                       "State"  : "NJ"
                    },
      "Contact"   : {
                       "Email"  : "Tom.Brown@gmail.com",
                       "Phone"  : "(973) 555-3434"
                    }
    }')
/

 

select * from json_documents
/
 

select mydata from json_documents
/

select a.mydata from json_documents a
/

select a.mydata.LastName from json_documents a
/

select a.mydata.Contact.Email from json_documents a
/

Note that the above does NOT work without the alias "a".

select a.mydata from json_documents a where a.mydata is json
/

There are two ways to access JSON data.
A simple one with object paths and a complicated one.

Here is the simple version:

select mydata from json_documents j
where j.mydata.FirstName  = 'John'
/


Below is the complicated version.

select mydata from json_documents
where json_value(mydata, '$.FirstName') = 'John'
/

Simple:

select mydata from json_documents j
where j.mydata.Address.City  = 'Newark'
/

Complicated:

select mydata from json_documents where json_value(mydata, '$.Address.City') = 'Newark'
/

Simple:

select j.mydata.LastName  from json_documents j
where j.mydata.FirstName = 'John'
/

Complicated:

select json_value(mydata, '$.LastName')  from json_documents
where json_value(mydata, '$.FirstName') = 'John'
/

select json_value(mydata, '$.FirstName'),json_value(mydata, '$.LastName') from
json_documents
where json_value(mydata, '$.FirstName') = 'John'
/
 
select json_value(mydata, '$.FirstName') as FN,json_value(mydata, '$.LastName') as LN
from json_documents
where json_value(mydata, '$.FirstName') = 'John'
/

select json_value(mydata, '$.FirstName') as FN,json_value(mydata, '$.LastName') as LN, 
json_value(mydata, '$.Address.City') as CITY from json_documents
where json_value(mydata, '$.FirstName') = 'John'
/


Cursor, simple version:

begin
   for jcur in (select j.mydata.FirstName as FN, j.mydata.LastName as LN,
       j.mydata.Address.City as CITY from json_documents j)
   loop
       dbms_output.put_line(jcur.ln || ' ' || jcur.fn || ' ' || jcur.CITY);
   end loop;

end;
/


Cursor: Complicated version.

begin
   for j in (select json_value(mydata, '$.FirstName') as FN,json_value(mydata,
'$.LastName') as LN, 
       json_value(mydata, '$.Address.City') as CITY from json_documents)
   loop
       dbms_output.put_line(j.ln || ' ' || j.fn || ' ' || j.CITY);
   end loop;

end;
/

Very interesting:

SQL Update is NOT allowed for JSON data.