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

28 comments:

  1. This is great. Retrieval part is very good :)

    ReplyDelete
  2. In SQL "S" denotes to - Structured .. in NOSQL databases there are no defined structures these database allows you to store unstructured data smile emoticon .. And yes as per Nimish Use google as your friend

    ReplyDelete
    Replies
    1. Well now you can store same on Oracle. And personally i dont go with the idea that NoSQL DB are non-structured. Every JSON document need to have same structure, like you can not have Person collection have Name in one doc, Fname Lname in 2nd PersonName in 3rd or may be Pname in 4th and so on. Structure is required but yes NoSQL do not follow the rules, like data size limit or constraints. I dont know it is good or bad, but people are calling it flexibility

      Delete
    2. "I dont know it is good or bad, but people are calling it flexibility"
      I live this statement :)
      I'll just ad, more flexible<>more usable, all depends and thi JSON examples are really really great!

      Delete
  3. Very good explanation !
    Further to your opinion: not merely JSON format which appeared in version 12c of the database, but also some features of PL/SQL which exists in Oracle at least since version 8i can be very useful for NoSQL processing of the data, especially for high-loaded OLTP systems.
    Leveraging power of PL/SQL with IPC features, such as Pipelines, Alerts or Buffered Queues, we able to perform very efficient computations with low costs, low concurrency (what imply low waste of time on wait events) and with approaches of non-blocking asynchronous processing too.

    Viacheslav Kalyakin
    Oracle pl/sql developer at Digital Network

    ReplyDelete
  4. I especially like your comment "Never say you'll never need JOINS" :-)

    ~David Moss
    Solution Architect, CGI UK

    ReplyDelete
  5. Awesome post!!!!
    Nimish, great gob.
    I had a look at JSON support in Oracle 12c before but did not know it is so powerfull

    ~Jacek Gebal
    Scrum Master, Senior Oracle Developer

    ReplyDelete
  6. very helpful. Thanks you

    ReplyDelete
  7. this is really good..

    ReplyDelete
  8. well explained!

    ~Vivek Ghag
    Senior Consultant at Capgemini

    ReplyDelete
  9. good

    ~Mohan Reddy
    Chief Manager - IT at PNB MetLife India Insurance Co. Ltd

    ReplyDelete
  10. For me it is like saying : oh! do not replace your horse-drawn by a car since horse-drawn now can be fit with engine !
    We are not moving from oracle to nosql only because of lack of json support.
    There are tons of reasons not using anymore a so heavy and so expensive database, that are widelly explained in the web literature. It is finally just a matter of being able to move with the times.


    ~Mohamed Bennekrouf

    ReplyDelete
  11. I agree. But is anyone listening? Oracle need to promote this feature more.


    ~Garry Taylor
    Database Technical Lead at Met Office

    ReplyDelete
  12. Marco Dellepiane GarabelloSeptember 13, 2015 at 6:36 PM

    Interesting feature, but what about the performances? I suppose that they aren't paragonable to a real NoSql Db.. am I right?

    ReplyDelete
    Replies
    1. depends, for me it was almost near mongodb, and much faster if joins come in picture

      Delete
  13. Great demonstration of this feature!

    ~Tarun Agarwal
    Senior Associate Database Developer at Sapient Consulting Limited

    ReplyDelete
  14. Nice Article!!!

    Rajesh Prabhu,
    Oracle PLSQL Specialist at Majesco ltd

    ReplyDelete
  15. Json support in Oracle- that is quite wonderful!

    ReplyDelete
  16. Very Primitive Understanding of NoSQL databases, that none of them support JOINS. Keep yourself updated.

    ReplyDelete
    Replies
    1. You can obviously store "primary key" references inside any other entry in the database. But that doesn't make it a join. A join is a relational operator. It's not simply about looking up related data, but the result of a join is another fully-formed data entity

      Delete
    2. And yes about your question, Graph Databases do support joins. But do you wish to add a graph database with Cassandra or mongodb just to have joins ?? It is lot more complex than just saying that NoSQL supports joins.

      Delete
  17. Add to the conversation the fact that 12.2 promises sharding in an Oracle database, coupled with JSON support in 12.1, and Oracle really is trying to compete against NoSQL databases like MongoDB.

    ReplyDelete
  18. Nimish,

    Great article! Very informative.

    In an attempt to "get the music under my fingers", I tried out your example on my Windows 7 laptop running Oracle 12c version 1 using SQL*PLus, but I can't even create the DEPT_JSON table. When I run the CREATE TABLE statement, I receive a "ORA-00908: missing NULL keyword" error, with the asterisk under the second 'j' of '(json_value is json (with unique keys))'. The database seems to want the keyword NULL right after the keyword 'is'.

    Any idea as to what is going on?

    Thanks.

    ReplyDelete
    Replies
    1. Not Sure. But I can point you to Oracle Doc
      https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6371

      Delete
  19. When we say Oracle 12c supports JSON we should mention the exact version since support starts from 12.1.0.2 onwards.

    ReplyDelete
  20. How about PL/SQL -> using bind variables whjen inserting into JSON doc store.

    ReplyDelete