Generate Nested JSON using SQL in Oracle Database

It has been a long time, since I wrote my last post on Parse JSON data in Oracle Database. I got lot of questions on my last post mainly how did I create the nested JSON data for the demo. So with this post, I am sharing the SQL which I used to create the nested JSON data for demo. Oracle Database provides JSON_OBJECT, which creates JSON from the resulting SQL. We just need to pass output Schema to input schema mapping. I am using Oracle Autonomous Database for this demo. 
SQL> SELECT D.DEPTNO ID,
  2  JSON_OBJECT (
  3  'DEPTNO'    VALUE D.DEPTNO,
  4  'DNAME'     VALUE D.DNAME,
  5  'EMPLOYEES' VALUE JSON_ARRAYAGG
  6  (
  7  JSON_OBJECT
  8  (
  9  'EMPNO'    VALUE EMPNO,
 10  'ENAME'    VALUE ENAME,
 11  'JOB'      VALUE JOB,
 12  'SAL'      VALUE SAL
 13  )
 14  ) FORMAT JSON
 15  ) JSON_VALUE
 16  FROM   DEPT D, EMP E
 17  WHERE  D.DEPTNO = E.DEPTNO
 18  GROUP  BY D.DEPTNO, D.DNAME;

        ID JSON_VALUE
---------- ------------------------------------------------------------------------------------------------------------------------
        10 {"DEPTNO":10,"DNAME":"ACCOUNTING","EMPLOYEES":[{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","SAL":2450.45},{"EMPNO":783
           9,"ENAME":"KING","JOB":"PRESIDENT","SAL":5000.15},{"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","SAL":1300}]}

        20 {"DEPTNO":20,"DNAME":"RESEARCH","EMPLOYEES":[{"EMPNO":7902,"ENAME":"FORD","JOB":"ANALYST","SAL":3000.6},{"EMPNO":7876,"E
           NAME":"ADAMS","JOB":"CLERK","SAL":1100.01},{"EMPNO":7369,"ENAME":"SMITH","JOB":"CLERK","SAL":800.75},{"EMPNO":7566,"ENAM
           E":"JONES","JOB":"MANAGER","SAL":2975.5},{"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST","SAL":3000.55}]}

        30 {"DEPTNO":30,"DNAME":"SALES","EMPLOYEES":[{"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMAN","SAL":1250},{"EMPNO":7698,"ENAME
           ":"BLAKE","JOB":"MANAGER","SAL":2850.3},{"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALESMAN","SAL":1600.9},{"EMPNO":7900,"ENAM
           E":"JAMES","JOB":"CLERK","SAL":950.99},{"EMPNO":7844,"ENAME":"TURNER","JOB":"SALESMAN","SAL":1500.95},{"EMPNO":7654,"ENA
           ME":"MARTIN","JOB":"SALESMAN","SAL":1250.05}]}

Related Posts 
Labels: ,

5 comments:

  1. DB Exam StudyOctober 27, 2020 at 10:22 AM

    Nice Post! Thanks for sharing such an amazing article, really informative, it helps me a lot.
    All Database Certifications

    ReplyDelete
  2. UnnatieducationsNovember 8, 2020 at 4:01 PM

    Helpful post you have shared. Check some information about IGNOU MBA Admission

    ReplyDelete
  3. India's super printing storeDecember 4, 2020 at 10:37 AM

    Nice Blog! www.printland.in Make personalised calendar by choosing from our templates or create unique photo calendars by uploading your pictures online.

    ReplyDelete
  4. AnonymousSeptember 6, 2023 at 4:33 AM

    How can we stop from department not repeating with each rows?

    ReplyDelete
  5. machine learning engineerNovember 22, 2023 at 11:02 PM

    Good information.

    ReplyDelete

Subscribe to: Post Comments (Atom)