Parse JSON data in Oracle Database using JSON_TABLE in SQL

Recently, I got opportunity to work on JSON data in Oracle Database. We were receiving this JSON data from Cisco Webex APIs, and the JSON data was complex (nested with json array). With this blog, I am sharing simple example how to parse complex JSON data in Oracle Database using SQL.

For this example, I am using SCOTT schema, and my current database version is Oracle 12.1 which was release in July 2013. With Current versions of Oracle 18c and 19c and Oracle Autonomous Databases, there has been lot of enhancements to parse JSON data. Anyways, let us first create DEPT_JSON table, which will have JSON_VALUE column of CLOB type. One JSON_VALUE field will store complete information of a department and employees data of that department in JSON format.

ngarg> CREATE TABLE DEPT_JSON
  2  (
  3  ID NUMBER,
  4  JSON_VALUE clob
  5  );
Table created.

ngarg> INSERT INTO DEPT_JSON
  2  VALUES(10,
  3   '{"DEPTNO": "10",
  4     "DNAME": "ACCOUNTING",
  5     "EMPLOYEES" : [{ EMPNO: 7839, "ENAME": "KING", SAL: 5000, "JOB": "PRESIDENT"},
  6                    { EMPNO: 7782, "ENAME": "CLARK", SAL: 2450, "JOB": "MANAGER"},
  7                    { EMPNO: 7934, "ENAME": "MILLER", SAL: 1300, "JOB": "CLERK"}
  8  ]
  9   }'
 10  );
1 row created.

ngarg> INSERT INTO DEPT_JSON
  2  VALUES(20,
  3   '{"DEPTNO": "20",
  4     "DNAME": "RESEARCH",
  5     "EMPLOYEES" : [{ EMPNO: 7566, "ENAME": "JONES", SAL: 2975, "JOB": "MANAGER"},
  6                    { EMPNO: 7788, "ENAME": "SCOTT", SAL: 3000, "JOB": "ANALYST"},
  7                    { EMPNO: 7902, "ENAME": "FORD", SAL: 3000, "JOB": "ANALYST"},
  8    { EMPNO: 7369, "ENAME": "SMITH", SAL: 800, "JOB": "CLERK"},
  9    { EMPNO: 7876, "ENAME": "ADAMS", SAL: 1100, "JOB": "CLERK"}
 10   ]
 11   }'
 12  );
1 row created.

ngarg> INSERT INTO DEPT_JSON
  2  VALUES(30,
  3   '{"DEPTNO": "30",
  4     "DNAME": "RESEARCH",
  5     "EMPLOYEES" : [{ EMPNO: 7698, "ENAME": "BLAKE", SAL: 2850, "JOB": "MANAGER"},
  6                    { EMPNO: 7499, "ENAME": "ALLEN", SAL: 1600, "JOB": "SALESMAN"},
  7                    { EMPNO: 7521, "ENAME": "WARD", SAL: 1250, "JOB": "SALESMAN"},
  8                    { EMPNO: 7654, "ENAME": "MARTIN", SAL: 1250, "JOB": "SALESMAN"},
  9                    { EMPNO: 7844, "ENAME": "TURNER", SAL: 1500, "JOB": "SALESMAN"},
 10                    { EMPNO: 7900, "ENAME": "JAMES", SAL: 950, "JOB": "CLERK"}
 11   ]
 12   }'
 13  );
1 row created.

ngarg> commit;
Commit complete.

As you would have noticed, I have not specified any different data type to store JSON, JSON is simply being stored in CLOB field. Now we can use following SQL to parse Nested JSON data along with JSON Array in Oracle Database by using JSON_TABLE function.

ngarg> SELECT
  2    DEPTNO, DNAME, EMPNO, ENAME, JOB, SAL
  3  FROM
  4    DEPT_JSON D,
  5    JSON_TABLE
  6    (
  7      D.JSON_VALUE, '$' COLUMNS
  8      (
  9        DEPTNO NUMBER(4) PATH '$.DEPTNO',
 10        DNAME VARCHAR2(15) PATH '$.DNAME',
 11        NESTED path '$.EMPLOYEES[*]' COLUMNS
 12        (
 13          EMPNO NUMBER(5) PATH '$.EMPNO',
 14          ENAME VARCHAR2(15) PATH '$.ENAME',
 15          JOB VARCHAR2(15) PATH '$.JOB',
 16          SAL NUMBER(7,2) PATH '$.SAL'
 17        )
 18      )
 19  ) J
 20  ORDER BY DEPTNO;

    DEPTNO DNAME                EMPNO ENAME           JOB                    SAL
---------- --------------- ---------- --------------- --------------- ----------
        10 ACCOUNTING            7839 KING            PRESIDENT             5000
        10 ACCOUNTING            7934 MILLER          CLERK                 1300
        10 ACCOUNTING            7782 CLARK           MANAGER               2450
        20 RESEARCH              7369 SMITH           CLERK                  800
        20 RESEARCH              7788 SCOTT           ANALYST               3000
        20 RESEARCH              7566 JONES           MANAGER               2975
        20 RESEARCH              7902 FORD            ANALYST               3000
        20 RESEARCH              7876 ADAMS           CLERK                 1100
        30 RESEARCH              7698 BLAKE           MANAGER               2850
        30 RESEARCH              7499 ALLEN           SALESMAN              1600
        30 RESEARCH              7521 WARD            SALESMAN              1250
        30 RESEARCH              7900 JAMES           CLERK                  950
        30 RESEARCH              7844 TURNER          SALESMAN              1500
        30 RESEARCH              7654 MARTIN          SALESMAN              1250

14 rows selected.

JSON_TABLE can be used in Oracle Database to specify the structure of JSON data, and convert JSON data into relational. As you can see, JSON_TABLE is easy to use. We simply need to define the PATH of each value in JSON and if data is nested we can use NESTED PATH, and Oracle database will automatically convert single JSON data with nested JSON into multiple relational rows. At the end of this post, I would also like to recommend to read JSON in Oracle Database with Examples to understand the how can we simply use dot (.) notation in SQL to read JSON Data in Oracle database

Related Posts
- JSON in Oracle Database with Examples
- Convert XML to Rows and Columns in Oracle
- Generate XML data using SQL in Oracle Database
- Load XML File in Oracle Table
- XML output from SQL query
- Create CSV file using PL/SQL
- Load CSV file in Oracle using PL/SQL
- SQL*Loader - Load Excel/CSV file into Oracle Database Table
- SQLFormat - CSV, JSON, XML - Format output of SQL in SQLDeveloper