Tuesday, 24 October 2017

Generating JSON Objects in Oracle SQL using JSON Functions

Oracle 12c Release 2 has enhanced the JSON Functionality where we can generate the JSON Objects directly from the table data. The following are the functions available. 
  • JSON_OBJECT

    • The JSON_OBJECT function converts a comma-separated list of key-value pairs into object members within a JSON object. 
    1. SELECT json_object (  
    2.          KEY 'EmployeeId' VALUE empno,  
    3.          KEY 'EmployeeName' VALUE ename  
    4.        ) AS employee  
    5. FROM   emp   
    6. ORDER BY empno;  
    7.   
    8. Output  
    9. ------  
    10. {"EmployeeId":7369,"EmployeeName":"SMITH"}  
    11. {"EmployeeId":7499,"EmployeeName":"ALLEN"}  
    12. {"EmployeeId":7521,"EmployeeName":"WARD"}  
    13. {"EmployeeId":7566,"EmployeeName":"JONES"}  
    14. {"EmployeeId":7654,"EmployeeName":"MARTIN"}  
    15. {"EmployeeId":7698,"EmployeeName":"BLAKE"}  
    16. {"EmployeeId":7782,"EmployeeName":"CLARK"}  
    17. {"EmployeeId":7788,"EmployeeName":"SCOTT"}  
    18. {"EmployeeId":7839,"EmployeeName":"KING"}  
    19. {"EmployeeId":7844,"EmployeeName":"TURNER"}  
  • JSON_OBJECTAGG

    • The JSON_OBJECTAGG aggregate function creates a single JSON object containing a list of object members formed by aggregating a key-value pair from each row.
    1. SELECT JSON_OBJECTAGG(KEY dname VALUE total_count) FROM (  
    2. SELECT d.dname, count(1) total_count from emp e, dept d  
    3. WHERE e.deptno=d.deptno  
    4. group by d.dname);  
    5.   
    6. Output  
    7. ------  
    8. {"ACCOUNTING":3,"RESEARCH":5,"SALES":6}  

  • JSON_ARRAY

    • The JSON_ARRAY function converts a comma-separated list of expressions into a JSON array of JSON values. 

    1. SELECT json_array(  empno, ename, sal,mgr,e.deptno,dname,loc  ) json_array    
    2. FROM emp e, dept d WHERE e.deptno=d.deptno  
    3. ORDER BY empno;    
    4.   
    5. Output  
    6. ------  
    7. [7369,"SMITH",800,7902,20,"RESEARCH","DALLAS"]  
    8. [7499,"ALLEN",1600,7698,30,"SALES","CHICAGO"]  
    9. [7521,"WARD",1250,7698,30,"SALES","CHICAGO"]  
    10. [7566,"JONES",2975,7839,20,"RESEARCH","DALLAS"]  
    11. [7654,"MARTIN",1250,7698,30,"SALES","CHICAGO"]  
    12. [7698,"BLAKE",2850,7839,30,"SALES","CHICAGO"]  
    13. [7782,"CLARK",2450,7839,10,"ACCOUNTING","NEW YORK"]  
    14. [7788,"SCOTT",3000,7566,20,"RESEARCH","DALLAS"]  
    15. [7839,"KING",5000,10,"ACCOUNTING","NEW YORK"]  
    16. [7844,"TURNER",1500,7698,30,"SALES","CHICAGO"] 


    1. SELECT json_array( json_object('EmployeeId' VALUE empno, 'EmployeeName' VALUE ename  
    2. , 'Salary' VALUE sal, 'Manager' VALUE mgr),  
    3. json_object('DepartmentId' VALUE d.deptno ,'DepartmentName' VALUE d.dname,  
    4. 'Location' VALUE d.loc)  
    5. ) json_array  
    6. FROM emp e, dept d   
    7. WHERE e.deptno=d.deptno   
    8. ORDER BY empno;  
    9.   
    10. Output  
    11. ------  
    12. [{"EmployeeId":7369,"EmployeeName":"SMITH","Salary":800,"Manager":7902},{"DepartmentId":20,"DepartmentName":"RESEARCH","Location":"DALLAS"}]  
    13. [{"EmployeeId":7499,"EmployeeName":"ALLEN","Salary":1600,"Manager":7698},{"DepartmentId":30,"DepartmentName":"SALES","Location":"CHICAGO"}]  
    14. [{"EmployeeId":7521,"EmployeeName":"WARD","Salary":1250,"Manager":7698},{"DepartmentId":30,"DepartmentName":"SALES","Location":"CHICAGO"}]  
    15. [{"EmployeeId":7566,"EmployeeName":"JONES","Salary":2975,"Manager":7839},{"DepartmentId":20,"DepartmentName":"RESEARCH","Location":"DALLAS"}]  
    16. [{"EmployeeId":7654,"EmployeeName":"MARTIN","Salary":1250,"Manager":7698},{"DepartmentId":30,"DepartmentName":"SALES","Location":"CHICAGO"}]  
    17. [{"EmployeeId":7698,"EmployeeName":"BLAKE","Salary":2850,"Manager":7839},{"DepartmentId":30,"DepartmentName":"SALES","Location":"CHICAGO"}]  
    18. [{"EmployeeId":7782,"EmployeeName":"CLARK","Salary":2450,"Manager":7839},{"DepartmentId":10,"DepartmentName":"ACCOUNTING","Location":"NEW YORK"}]  
    19. [{"EmployeeId":7788,"EmployeeName":"SCOTT","Salary":3000,"Manager":7566},{"DepartmentId":20,"DepartmentName":"RESEARCH","Location":"DALLAS"}]  
    20. [{"EmployeeId":7839,"EmployeeName":"KING","Salary":5000,"Manager":null},{"DepartmentId":10,"DepartmentName":"ACCOUNTING","Location":"NEW YORK"}]  
    21. [{"EmployeeId":7844,"EmployeeName":"TURNER","Salary":1500,"Manager":7698},{"DepartmentId":30,"DepartmentName":"SALES","Location":"CHICAGO"}]  

  • JSON_ARRAYAGG

    • The JSON_ARRAYAGG aggregate function, similar to the LISTAGG function, aggregates an expression from each row into a single JSON array. 

    1. SELECT JSON_ARRAYAGG(e.ename) employee_array  
    2. FROM   emp e  
    3. WHERE  e.deptno = 20;  
    4.   
    5. Output  
    6. ------  
    7. ["SMITH","JONES","SCOTT","ADAMS","FORD"]  

  • Complex Example

    • The Below Query gives a single JSON Array Object contains all the employee & department informations. 
    1. SELECT JSON_ARRAYAGG(json_object(KEY 'EmployeeDetails' VALUE   
    2.   json_array(json_object(KEY 'DepartmentInfo' VALUE json_object(  
    3.   KEY 'DepartmentId' VALUE department_id, KEY 'DepartmentName'   
    4.   VALUE department_name,KEY 'EmployeeInfo' VALUE   
    5.   (SELECT json_arrayagg(json_object(KEY 'EmployeeId' VALUE e.employee_id,  
    6.                 KEY 'FirstName' VALUE e.first_name   
    7.             ,   KEY 'LastName' VALUE e.last_name     
    8.             ,   KEY 'Email' VALUE e.email    
    9.             ,   KEY 'PhoneNumber' VALUE e.phone_number             
    10.                 ))  
    11.                 FROM employees e   
    12.                 WHERE e.department_id=d.department_id)  
    13.   )))) ) json FROM departments d  
    14. WHERE ROWNUM<5;  
    15. Output  
    16. ------  
    17. [  
    18.    {  
    19.       "EmployeeDetails": [  
    20.          {  
    21.             "DepartmentInfo": {  
    22.                "DepartmentId": 10,  
    23.                "DepartmentName": "Administration",  
    24.                "EmployeeInfo": [  
    25.                   {  
    26.                      "EmployeeId": 200,  
    27.                      "FirstName": "Jennifer",  
    28.                      "LastName": "Whalen",  
    29.                      "Email": "JWHALEN",  
    30.                      "PhoneNumber": "515.123.4444"  
    31.                   }  
    32.                ]  
    33.             }  
    34.          }  
    35.       ]  
    36.    },  
    37.    {  
    38.       "EmployeeDetails": [  
    39.          {  
    40.             "DepartmentInfo": {  
    41.                "DepartmentId": 20,  
    42.                "DepartmentName": "Marketing",  
    43.                "EmployeeInfo": [  
    44.                   {  
    45.                      "EmployeeId": 201,  
    46.                      "FirstName": "Michael",  
    47.                      "LastName": "Hartstein",  
    48.                      "Email": "MHARTSTE",  
    49.                      "PhoneNumber": "515.123.5555"  
    50.                   },  
    51.                   {  
    52.                      "EmployeeId": 202,  
    53.                      "FirstName": "Pat",  
    54.                      "LastName": "Fay",  
    55.                      "Email": "PFAY",  
    56.                      "PhoneNumber": "603.123.6666"  
    57.                   }  
    58.                ]  
    59.             }  
    60.          }  
    61.       ]  
    62.    },  
    63.    {  
    64.       "EmployeeDetails": [  
    65.          {  
    66.             "DepartmentInfo": {  
    67.                "DepartmentId": 30,  
    68.                "DepartmentName": "Purchasing",  
    69.                "EmployeeInfo": [  
    70.                   {  
    71.                      "EmployeeId": 114,  
    72.                      "FirstName": "Den",  
    73.                      "LastName": "Raphaely",  
    74.                      "Email": "DRAPHEAL",  
    75.                      "PhoneNumber": "515.127.4561"  
    76.                   },  
    77.                   {  
    78.                      "EmployeeId": 115,  
    79.                      "FirstName": "Alexander",  
    80.                      "LastName": "Khoo",  
    81.                      "Email": "AKHOO",  
    82.                      "PhoneNumber": "515.127.4562"  
    83.                   },  
    84.                   {  
    85.                      "EmployeeId": 116,  
    86.                      "FirstName": "Shelli",  
    87.                      "LastName": "Baida",  
    88.                      "Email": "SBAIDA",  
    89.                      "PhoneNumber": "515.127.4563"  
    90.                   },  
    91.                   {  
    92.                      "EmployeeId": 117,  
    93.                      "FirstName": "Sigal",  
    94.                      "LastName": "Tobias",  
    95.                      "Email": "STOBIAS",  
    96.                      "PhoneNumber": "515.127.4564"  
    97.                   },  
    98.                   {  
    99.                      "EmployeeId": 118,  
    100.                      "FirstName": "Guy",  
    101.                      "LastName": "Himuro",  
    102.                      "Email": "GHIMURO",  
    103.                      "PhoneNumber": "515.127.4565"  
    104.                   },  
    105.                   {  
    106.                      "EmployeeId": 119,  
    107.                      "FirstName": "Karen",  
    108.                      "LastName": "Colmenares",  
    109.                      "Email": "KCOLMENA",  
    110.                      "PhoneNumber": "515.127.4566"  
    111.                   }  
    112.                ]  
    113.             }  
    114.          }  
    115.       ]  
    116.    },  
    117.    {  
    118.       "EmployeeDetails": [  
    119.          {  
    120.             "DepartmentInfo": {  
    121.                "DepartmentId": 40,  
    122.                "DepartmentName": "Human Resources",  
    123.                "EmployeeInfo": [  
    124.                   {  
    125.                      "EmployeeId": 203,  
    126.                      "FirstName": "Susan",  
    127.                      "LastName": "Mavris",  
    128.                      "Email": "SMAVRIS",  
    129.                      "PhoneNumber": "515.123.7777"  
    130.                   }  
    131.                ]  
    132.             }  
    133.          }  
    134.       ]  
    135.    }  
    136. ]  

    Another Example shown below is an SQL query which will return the Employee Details JSON Objects which contains a nested array object called EmployeeInfo. It contains all the employees for a particular department. 
    1. SELECT json_object(KEY 'EmployeeDetails' VALUE   
    2.   json_array(json_object(KEY 'DepartmentInfo' VALUE json_object(  
    3.   KEY 'DepartmentId' VALUE department_id, KEY 'DepartmentName'   
    4.   VALUE department_name,KEY 'EmployeeInfo' VALUE   
    5.   (SELECT json_arrayagg(json_object(KEY 'EmployeeId' VALUE e.employee_id,  
    6.                 KEY 'FirstName' VALUE e.first_name  
    7.             ,   KEY 'LastName' VALUE e.last_name       
    8.             ,   KEY 'Email' VALUE e.email   
    9.             ,   KEY 'PhoneNumber' VALUE phone_number               
    10.                 ))  
    11.                 FROM employees e   
    12.                 WHERE e.department_id=d.department_id)  
    13.   )))) json FROM departments d;  
    14.   
    15. Output  
    16. ------  
    17. {  
    18.    "EmployeeDetails": [  
    19.       {  
    20.          "DepartmentInfo": {  
    21.             "DepartmentId": 20,  
    22.             "DepartmentName": "Marketing",  
    23.             "EmployeeInfo": [  
    24.                {  
    25.                   "EmployeeId": 201,  
    26.                   "FirstName": "Michael",  
    27.                   "LastName": "Hartstein",  
    28.                   "Email": "MHARTSTE",  
    29.                   "PhoneNumber": "515.123.5555"  
    30.                },  
    31.                {  
    32.                   "EmployeeId": 202,  
    33.                   "FirstName": "Pat",  
    34.                   "LastName": "Fay",  
    35.                   "Email": "PFAY",  
    36.                   "PhoneNumber": "603.123.6666"  
    37.                }  
    38.             ]  
    39.          }  
    40.       }  
    41.    ]  
    42. }  
















    Posted by at No comments:
    Subscribe to: Posts (Atom)