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: ,

6 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
  6. Jelly JohnDecember 5, 2024 at 4:06 PM

    Great post! I would never think of SQL in Oracle could be so useful for creating nested JSON, but this document covers all of it. These examples provided were quite helpful in relating with the procedure that was being explained fully. By the way, if you are tired of wearing boring things while working with a database, it will be helpful to try aviator jackets for men. Not only that, but they are incredibly sturdy, which is great for when you get lost in long lines of code in the cold! Thank you for sharing.

    ReplyDelete

Subscribe to: Post Comments (Atom)