With the release of Database 12.1.0.2, Oracle introduced a new functionality oriented to exchange information in JavaScript Object Notation (JSON) data format between applications and the database engine.

JSON is a language-independent data format based on the JavaScript Programming Language often used for exchanging structured data between heterogeneous web applications.

JSON database implementation allows you store and retrieve data between the applications and the database engine conforming to the JSON standard avoiding the need of writing custom procedures and special code.

Loading JSON documents into the database

Suppose you have a master catalog which has a product identification, a product name, a price field and some optional tags.

The JSON record description will be as follows:

{
    "id": 14,
    "name": "Table",
    "price": 22.95,
    "details": {"color" : "brown",
                "stock" : "yes" }
                }

(You can validate your json records with “jsonlint”: http://jsonlint.com/).

1.) Create a relational table with a JSON data column

A very easy approach is to create a relational table including a CLOB data type column to store json data and adding to this column a special json check constraint to enforce the JSON standard to the data.

SQL> CREATE TABLE product_catalog
  2    (ident number generated always as identity,
  3     json_document CLOB
  4     CONSTRAINT validate_json CHECK (json_document IS JSON));

Table created.

Let’s populate the table with some data:

SQL> INSERT INTO product_catalog (json_document)
  2    VALUES ('{
  3        "id": 14,
  4        "name": "Table",
  5        "price": 22.95,
  6        "details": {"color" : "brown",
  7        "stock" : "yes" }
  8     }');

1 row created.

SQL> INSERT INTO product_catalog (json_document)
  2    VALUES ('{
  3        "id": 22,
  4        "name": "Door",
  5        "price": 14.99,
  6        "details": {"color" : "white",
  7        "stock" : "yes" }
  8     }');

1 row created.

SQL> INSERT INTO product_catalog (json_document)
  2    VALUES ('{
  3        "id": 99,
  4        "name": "Chair",
  5        "price": 5.25,
  6        "details": {"color" : "black",
  7        "stock" : "no" }
  8     }');

1 row created.

JSON data will always be returned as VARCHAR2 data type. Here are some simple examples to query the JSON data.

The following query extracts from each JSON document the “id”, “name” and “price” fields. Also this query will return the auto generated “ident” record number as part of the resultset:

SQL> SET linesize 50
SQL> COL ident format 999
SQL> COL record_id format a5
SQL> COL id format a5
SQL> COL name format a10
SQL> COL price format a5
SQL> SELECT ident,
  2        json.json_document.id as id,
  3        json.json_document.name as name,
  4        json.json_document.price as price
  5    FROM product_catalog json;

IDENT ID    NAME       PRICE                      
----- ----- ---------- -----                      
    1 14    Table      22.95                      
    2 22    Door       14.99                      
    3 99    Chair      5.25

If you want to extract each object in array “details” then you can select each object as part of the query expression:

SQL> SET linesize 50
SQL> COL name format a10
SQL> COL item_color format a10
SQL> COL item_stock format a10
SQL> SELECT json.json_document.name as name,
  2        json.json_document.details.color as item_color,
  3        json.json_document.details.stock as item_stock
  4    FROM product_catalog json;

NAME       ITEM_COLOR ITEM_STOCK                  
---------- ---------- ----------                  
Table      brown      yes                         
Door       white      yes                         
Chair      black      no

You can also add JSON fields to the SQL predicate in order to specify a subset of rows to be returned. JSON predicates are specified in the “where” clause of a SQL statement as follows:

SQL> SET linesize 50
SQL> COL id format a5
SQL> COL name format a10
SQL> COL item_stock format a10
SQL> SELECT json.json_document.id as id,
  2        json.json_document.name as name ,
  3        json.json_document.details.stock as item_stock
  4    FROM product_Catalog json
  5   WHERE json.json_document.details.stock = 'yes';

ID    NAME       ITEM_STOCK                       
----- ---------- ----------                       
14    Table      yes                              
22    Door       yes

Let’s update a table row. You can perform DML operations very easily:

SQL> UPDATE product_catalog json
  2    SET json.json_document = '{
  3         "id": 99,
  4         "name": "Chair",
  5         "price": 5.25,
  6         "details": {"color" : "black",
  7         "stock" : "yes" }
  8        }'
  9   WHERE json.json_document.id = 99;

1 row updated.

SQL> SET linesize 50
SQL> COL id format a5
SQL> COL name format a10
SQL> COL item_stock format a10
SQL> SELECT json.json_document.id as id,
  2        json.json_document.name as name ,
  3        json.json_document.details.stock as item_stock
  4    FROM product_Catalog json
  5   WHERE json.json_document.details.stock = 'yes';

ID    NAME       ITEM_STOCK                       
----- ---------- ----------                       
14    Table      yes                              
22    Door       yes                              
99    Chair      yes

The examples described in this blog are known as “JSON Dot-Notation Query” syntax.

The dot-notation syntax is a table alias followed by a dot (.) and the name of a JSON column. The table alias is mandatory.

There are other very powerful possibilities to handle JSON data that will be addressed in future blogs.

Link to script that contains the examples in this post.

1 Comment

2014-12 – JSON in Oracle Database 12c

HEADQUARTERS