Relational to Oracle JSON

This post is a continuation of my prior blogs in Oracle Jason, which gave us an insight on Oracle Jason - Storing Jason data and querying it. Please check Oracle Jason for comprehensive lookup.

Lets now take it a bit forward to converting our relational stuff to JASON with SQL.

Oracle introduced some functions to deal with it without the use of libraries

• JSON_OBJECT – single-row function, creates an object for each row.

• JSON_ARRAY – single-row function, creates an array for each row.

• JSON_OBJECTAGG – aggregate function, creates an object based on groups of rows.

• JSON_ARRAYAGG – aggregate function, creates an array based on groups of rows.

select json_object (
         'department' value d.department_name,
         'employees' value json_arrayagg (
           json_object (
             'name'     value last_name || ', ' || first_name, 
             'job'      value job_title,
             'hireDate' value hire_date,
             'loc'      Value CITY
           )
         )
       )
from   departments d, employees e, jobs j, locations l
where  d.department_id = e.department_id
AND     e.job_id = j.job_id
AND     l.location_id = d.location_id
AND  d.department_id = 110
group  by d.department_name;
-----------------------------------------------------------------------
{"department":"Accounting","employees":[{"name":"Higgins, Shelley","job":"Accounting Manager","hireDate":"2002-06-07T00:00:00","loc":"Seattle"},{"name":"Gietz, William","job":"Public Accountant","hireDate":"2002-06-07T00:00:00","loc":"Seattle"}]}

At fist we are creating a JASON object then we are combining this into an array for each department.

Oracle 18 c has come up with a bit advancement as these functions mentioned above previously supported only number, varchar and date it now supports all data types of oracle. Oracle 19c has improved it still furthure by allowing to pass just an '*' to get the results.

select json_object ( * ) jdoc
from   employees
where  employee_id = 110;
-----------------------------------------------------------------------
{"EMPLOYEE_ID":110,"FIRST_NAME":"John","LAST_NAME":"Chen","EMAIL":"JCHEN","PHONE_NUMBER":"515.124.4269","HIRE_DATE":"2005-09-28T00:00:00","JOB_ID":"FI_ACCOUNT","SALARY":9200,"COMMISSION_PCT":null,"MANAGER_ID":108,"DEPARTMENT_ID":100}

These functions are very helpful in creating JASON doc from the trelational stuff. We have already seen JASON to relational using JASON_TABLE in my previous blog on oracle jason.

#ORACLE18c #ORACLE 12c # OracleJason #PLSQL #ORACLEDEVELOPER #Plsqlprogramming