JSON in Oracle Database with Examples

In one of my previous post, I mentioned "Migrating from Oracle to NoSQL could be a big mistake especially now when Oracle Database 12c supports JSON". Many of my friends are using NoSQL databases just because of flexibility and schema-less design. They were not quite inline with my thoughts and asked me to provide details to support my thoughts.

First let me create some sample JSON data, I used scott schema data to create this JSON Data.

-- Department Data
create table dept_json
(
    id    number,
    json_value  clob constraint dept_json_chk check (json_value is json (with unique keys))
);

insert into dept_json values (10, '{ deptno: 10, "dname": "ACCOUNTING", "loc": "NEW YORK"}');
insert into dept_json values (20, '{ deptno: 20, "dname": "RESEARCH", "loc": "DALLAS"}');
insert into dept_json values (30, '{ deptno: 30, "dname": "SALES", "loc": "CHICAGO"}');
insert into dept_json values (40, '{ deptno: 40, "dname": "OPERATIONS", "loc": "BOSTON"}');

-- Employee Data
create table emp_json
(
    id number,
    json_value  clob constraint emp_json_chk check (json_value is json (with unique keys))
);

insert into emp_json values (7839, '{ empno: 7839, "ename": "KING", sal: 5000, "job": "PRESIDENT", "hiredate": "11/17/1981", deptno: 10}');
insert into emp_json values (7698, '{ empno: 7698, "ename": "BLAKE", sal: 2850, "job": "MANAGER", mgr: 7839, "hiredate": "05/01/1981", deptno: 30 }');
insert into emp_json values (7782, '{ empno: 7782, "ename": "CLARK", sal: 2450, "job": "MANAGER", mgr: 7839, "hiredate": "06/09/1981", deptno: 10 }');
insert into emp_json values (7566, '{ empno: 7566, "ename": "JONES", sal: 2975, "job": "MANAGER", mgr: 7839, "hiredate": "04/02/1981", deptno: 20 }');
insert into emp_json values (7788, '{ empno: 7788, "ename": "SCOTT", sal: 3000, "job": "ANALYST", mgr: 7566, "hiredate": "04/19/1987", deptno: 20 }');
insert into emp_json values (7902, '{ empno: 7902, "ename": "FORD", sal: 3000, "job": "ANALYST", mgr: 7566, "hiredate": "12/03/1981", deptno: 20 }');
insert into emp_json values (7369, '{ empno: 7369, "ename": "SMITH", sal: 800, "job": "CLERK", mgr: 7902, "hiredate": "12/17/1980", deptno: 20 }');
insert into emp_json values (7499, '{ empno: 7499, "ename": "ALLEN", sal: 1600, "job": "SALESMAN", mgr: 7698, "hiredate": "02/20/1981", deptno: 30 }');
insert into emp_json values (7521, '{ empno: 7521, "ename": "WARD", sal: 1250, "job": "SALESMAN", mgr: 7698, "hiredate": "02/22/1981", deptno: 30 }');
insert into emp_json values (7654, '{ empno: 7654, "ename": "MARTIN", sal: 1250, "job": "SALESMAN", mgr: 7698, "hiredate": "09/28/1981", deptno: 30 }');
insert into emp_json values (7844, '{ empno: 7844, "ename": "TURNER", sal: 1500, "job": "SALESMAN", mgr: 7698, "hiredate": "09/08/1981", deptno: 30 }');
insert into emp_json values (7876, '{ empno: 7876, "ename": "ADAMS", sal: 1100, "job": "CLERK", mgr: 7788, "hiredate": "05/23/1987", deptno: 20 }');
insert into emp_json values (7900, '{ empno: 7900, "ename": "JAMES", sal: 950, "job": "CLERK", mgr: 7698, "hiredate": "12/03/1981", deptno: 30 }');
insert into emp_json values (7934, '{ empno: 7934, "ename": "MILLER", sal: 1300, "job": "CLERK", mgr: 7782, "hiredate": "01/23/1982", deptno: 10 }');

Lets check what is in the EMP_JSON table?

SQL> select * from emp_json;
        ID JSON_VALUE
---------- -----------------------------------------------------------------------------------------------------------------------
      7839 { empno: 7839, "ename": "KING", sal: 5000, "job": "PRESIDENT", "hiredate": "11/17/1981", deptno: 10}
      7698 { empno: 7698, "ename": "BLAKE", sal: 2850, "job": "MANAGER", mgr: 7839, "hiredate": "05/01/1981", deptno: 30 }
      7782 { empno: 7782, "ename": "CLARK", sal: 2450, "job": "MANAGER", mgr: 7839, "hiredate": "06/09/1981", deptno: 10 }
      7566 { empno: 7566, "ename": "JONES", sal: 2975, "job": "MANAGER", mgr: 7839, "hiredate": "04/02/1981", deptno: 20 }
      7788 { empno: 7788, "ename": "SCOTT", sal: 3000, "job": "ANALYST", mgr: 7566, "hiredate": "04/19/1987", deptno: 20 }
      7902 { empno: 7902, "ename": "FORD", sal: 3000, "job": "ANALYST", mgr: 7566, "hiredate": "12/03/1981", deptno: 20 }
      7369 { empno: 7369, "ename": "SMITH", sal: 800, "job": "CLERK", mgr: 7902, "hiredate": "12/17/1980", deptno: 20 }
      7499 { empno: 7499, "ename": "ALLEN", sal: 1600, "job": "SALESMAN", mgr: 7698, "hiredate": "02/20/1981", deptno: 30 }
      7521 { empno: 7521, "ename": "WARD", sal: 1250, "job": "SALESMAN", mgr: 7698, "hiredate": "02/22/1981", deptno: 30 }
      7654 { empno: 7654, "ename": "MARTIN", sal: 1250, "job": "SALESMAN", mgr: 7698, "hiredate": "09/28/1981", deptno: 30 }
      7844 { empno: 7844, "ename": "TURNER", sal: 1500, "job": "SALESMAN", mgr: 7698, "hiredate": "09/08/1981", deptno: 30 }
      7876 { empno: 7876, "ename": "ADAMS", sal: 1100, "job": "CLERK", mgr: 7788, "hiredate": "05/23/1987", deptno: 20 }
      7900 { empno: 7900, "ename": "JAMES", sal: 950, "job": "CLERK", mgr: 7698, "hiredate": "12/03/1981", deptno: 30 }
      7934 { empno: 7934, "ename": "MILLER", sal: 1300, "job": "CLERK", mgr: 7782, "hiredate": "01/23/1982", deptno: 10 }

Look simple yet, what if I want to query select empno, ename, sal from emp_json

SQL> select e.json_value.empno empno, e.json_value.ename ename, e.json_value.sal sal from emp_json e;
EMPNO      ENAME      SAL
---------- ---------- ----------
7839       KING       5000
7698       BLAKE      2850
7782       CLARK      2450
7566       JONES      2975
7788       SCOTT      3000
7902       FORD       3000
7369       SMITH      800
7499       ALLEN      1600
7521       WARD       1250
7654       MARTIN     1250
7844       TURNER     1500
7876       ADAMS      1100
7900       JAMES      950
7934       MILLER     1300

WOW, Select Query is supporting the DOT (.) notation for JSON. Why not try filter some data here.

SQL> select e.json_value.empno empno, e.json_value.ename ename, e.json_value.sal sal from emp_json e
  2  where e.json_value.deptno = 10 order by e.json_value.sal desc;

EMPNO      ENAME      SAL
---------- ---------- ----------
7839       KING       5000
7782       CLARK      2450
7934       MILLER     1300

Oracle made it really easy and simple, I hope you also have started liking it. Let me try some aggregation or better analytic to find employees with maximum salary in each department.

SQL> select empno, ename, sal, deptno from
  2  (
  3  select e.json_value.empno empno, e.json_value.ename ename, e.json_value.sal sal, e.json_value.deptno deptno,
  4  rank() over (partition by e.json_value.deptno order by e.json_value.sal desc) rn
  5  from emp_json e
  6  ) where rn=1
  7  order by deptno;

EMPNO      ENAME      SAL        DEPTNO
---------- ---------- ---------- ----------
7839       KING       5000       10
7369       SMITH      800        20
7900       JAMES      950        30

Analytic and Aggregation is quite a powerful feature which Oracle has. With JSON it is simply awesome.

Now moving to the Nightmare of NoSQL Database developer... Creating MAP Reduce for JOINS. Never say you'll never need JOINS.

SQL> select empno, ename, sal, dname from
  2  (
  3  select e.json_value.empno empno, e.json_value.ename ename, e.json_value.sal sal, d.json_value.dname dname,
  4  rank() over (partition by e.json_value.deptno order by e.json_value.sal desc) rn
  5  from emp_json e, dept_json d
  6  where d.json_value.deptno = e.json_value.deptno
  7  ) where rn=1
  8  order by dname;

EMPNO      ENAME      SAL        DNAME
---------- ---------- ---------- ---------------
7839       KING       5000       ACCOUNTING
7369       SMITH      800        RESEARCH
7900       JAMES      950        SALES

Let me also try self-join, the famous Employee Manager list

SQL> select e.json_value.empno empno, e.json_value.ename ename, m.json_value.empno mgrno, m.json_value.ename mname
  2  from emp_json e, emp_json m
  3  where e.json_value.mgr = m.json_value.empno (+);

EMPNO      ENAME      MGRNO      MNAME
---------- ---------- ---------- ----------
7566       JONES      7839       KING
7782       CLARK      7839       KING
7698       BLAKE      7839       KING
7900       JAMES      7698       BLAKE
7844       TURNER     7698       BLAKE
7654       MARTIN     7698       BLAKE
7521       WARD       7698       BLAKE
7499       ALLEN      7698       BLAKE
7934       MILLER     7782       CLARK
7902       FORD       7566       JONES
7788       SCOTT      7566       JONES
7876       ADAMS      7788       SCOTT
7369       SMITH      7902       FORD
7839       KING

And last but not the least, Can we create Indexes on JSON document, the answer is of-course YES !!!

SQL> create index emp_json_idx on emp_json e(e.json_value.deptno);
Index created.

So here are some basic points you should consider before migrating to NoSQL Databases from Oracle

1. In Oracle 12c, You can store and retrieve JSON in simple friendly way using SQL. JSON support feature is available within all Database editions.
2. With Oracle 12c, going schema-less or developing applications with JSON became easy, robust and resourceful.
3. NoSQL database does not support Joins. Believe me there are 99.99% chances that at some point or other you may want to club your NoSQL data with another Relational Data or may be NoSQL data for some reporting or may be some new application or may be just want to extend your current application features. You certainly don't wish to write MAP-REDUCE for each possible scenario.
4. Yes, horizontal scale is there in NoSQL database but think about a scenario
• when 2 people book same hotel room because 2 nodes were not able to talk.
• or all nodes were up but before making the transaction complete, there is a delay before writing data to verify consistency check with all nodes in network.
5. If your organisation is running on Oracle, and you add some NoSQL database, you may need expertise for maintaining and running it. Don't depend on one person.
6. But yes, if you really think that your database may deal in some Petabytes of data, then it is worth considering NoSQL database.

My suggestion is to think twice before moving to NoSQL Database because it will add only complexity and will not provide any new features. With Oracle 12c you can store and retrieve JSON fully transparently via SQL and leverage all the feature of RDBMS. Take right decision before it is too late.

Related Links:
- Top 15 new features of Oracle Database 12.2 for developers
- Being a PL/SQL Developer and Optimizing Code
- Oracle Database 12c New Features for Developers
- SQLNET: How does Oracle Client connect with Oracle Server
- Query optimization tips for Oracle
- SQL Interview Question Answers

- Generate XML data using SQL in Oracle Database
- Convert XML to Rows and Columns in Oracle
- Complex View Merging Transformation and ORA-00979 in Oracle 12c