JSON in Oracle Database 12c

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

o        when 2 people book same hotel room because 2 nodes were not able to talk.

o        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.