Nov 26, 2016

Oracle Introduced SQL/JSON Functions in Oracle Database 12c Release 2 (12.2)


    Oracle introduces SQL/JSON functions in Oracle Database 12c Release 2 (12.2). This guide will walk you through the basic examples of the SQL/JSON functions and how they works.






    Setup

    The examples mentioned in this article use the following tables.
    CREATE TABLE DEPT (
      DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
      DNAME VARCHAR2(14),
      LOC VARCHAR2(13)
    ) ;
    
    CREATE TABLE EMP (
      EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
      ENAME VARCHAR2(10),
      JOB VARCHAR2(9),
      MGR NUMBER(4),
      HIREDATE DATE,
      SAL NUMBER(7,2),
      COMM NUMBER(7,2),
      DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
    );
    
    INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
    INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
    INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
    INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
    
    INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
    INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
    INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
    INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
    INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
    INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
    INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
    INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
    INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
    INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
    INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
    INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
    INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
    INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
    COMMIT;

    JSON_OBJECT

    The JSON_OBJECT function converts a comma-separated list of key-value pairs into object members within a JSON object.
    SELECT JSON_OBJECT (
             KEY 'department-number' VALUE d.deptno,
             KEY 'department-name' VALUE d.dname,
             KEY 'location' VALUE d.loc
           ) AS departments
    FROM   dept d
    ORDER BY d.deptno;
    
    DEPARTMENTS
    --------------------------------------------------------------------------------
    {"department-number":10,"department-name":"ACCOUNTING","location":"NEW YORK"}
    {"department-number":20,"department-name":"RESEARCH","location":"DALLAS"}
    {"department-number":30,"department-name":"SALES","location":"CHICAGO"}
    {"department-number":40,"department-name":"OPERATIONS","location":"BOSTON"}
    
    SQL>

    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.
    SELECT JSON_OBJECTAGG (
             KEY d.dname VALUE d.deptno
           ) AS departments
    FROM   dept d
    ORDER BY d.deptno;
    
    DEPARTMENTS
    --------------------------------------------------------------------------------
    {"ACCOUNTING":10,"RESEARCH":20,"SALES":30,"OPERATIONS":40}
    
    SQL>


    JSON_ARRAY

    The JSON_ARRAY function converts a comma-separated list of expressions into a JSON array of JSON values.
    SELECT JSON_ARRAY(
             ROWNUM,
             JSON_OBJECT(KEY 'department_no' VALUE d.deptno),
             JSON_OBJECT(KEY 'department_name' VALUE d.dname)
           ) AS department_json_array
    FROM   dept d;
    
    DEPARTMENT_JSON_ARRAY
    --------------------------------------------------------------------------------
    [1,{"department_no":10},{"department_name":"ACCOUNTING"}]
    [2,{"department_no":20},{"department_name":"RESEARCH"}]
    [3,{"department_no":30},{"department_name":"SALES"}]
    [4,{"department_no":40},{"department_name":"OPERATIONS"}]
    
    SQL>


    JSON_ARRAYAGG

    The JSON_ARRAYAGG aggregate function, similar to the LISTAGG function, aggregates an expression from each row into a single JSON array.
    SELECT JSON_ARRAYAGG(e.ename) employee_array
    FROM   emp e
    WHERE  e.deptno = 20;
    
    EMPLOYEE_ARRAY
    --------------------------------------------------------------------------------
    ["SMITH","JONES","SCOTT","ADAMS","FORD"]
    
    SQL>
    
    
    SELECT JSON_ARRAYAGG(e.ename ORDER BY e.ename) employee_array
    FROM   emp e
    WHERE  e.deptno = 20;
    
    EMPLOYEE_ARRAY
    --------------------------------------------------------------------------------
    ["ADAMS","FORD","JONES","SCOTT","SMITH"]
    
    SQL>


    Complex JSON Objects

    Each function call can itself be an expression, so they can easily be combined to create complex JSON objects.
    SELECT JSON_OBJECT (
             KEY 'departments' VALUE
               JSON_ARRAY(
                 (SELECT JSON_OBJECTAGG (
                           KEY 'department' VALUE
                             JSON_OBJECT(
                               KEY 'department_name' VALUE d.dname,
                               KEY 'department_no' VALUE d.deptno,
                               KEY 'employees' VALUE
                                   (SELECT JSON_ARRAYAGG(
                                             JSON_OBJECT(
                                               KEY 'employee_number' VALUE e.empno,
                                               KEY 'employee_name' VALUE e.ename
                                             )
                                           )
                                    FROM   emp e
                                    WHERE  e.deptno = d.deptno
                                   )
                             )
                       )
                  FROM   dept d
                 )
              )
          )
    FROM  dual;
    
    JSON_OBJECT(KEY'DEPARTMENTS'VALUEJSON_ARRAY((SELECTJSON_OBJECTAGG(KEY'DEPARTMENT'VALUEJSON_OBJECT(KEY'DEPARTMENT_NAME'VALUED.DNAME,KEY'DEPARTMENT_NO'VALUED.DEPT
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------
    {"departments":[{"department":{"department_name":"ACCOUNTING","department_no":10,"employees":[{"employee_number":7782,"employee_name":"CLARK"},{"employee_number
    ":7839,"employee_name":"KING"},{"employee_number":7934,"employee_name":"MILLER"}]},"department":{"department_name":"RESEARCH","department_no":20,"employees":[{"
    employee_number":7369,"employee_name":"SMITH"},{"employee_number":7566,"employee_name":"JONES"},{"employee_number":7788,"employee_name":"SCOTT"},{"employee_numb
    er":7876,"employee_name":"ADAMS"},{"employee_number":7902,"employee_name":"FORD"}]},"department":{"department_name":"SALES","department_no":30,"employees":[{"em
    ployee_number":7499,"employee_name":"ALLEN"},{"employee_number":7521,"employee_name":"WARD"},{"employee_number":7654,"employee_name":"MARTIN"},{"employee_number
    ":7698,"employee_name":"BLAKE"},{"employee_number":7844,"employee_name":"TURNER"},{"employee_number":7900,"employee_name":"JAMES"}]},"department":{"department_n
    ame":"OPERATIONS","department_no":40,"employees":null}}]}
    
    SQL>

    If we run this through a JSON Formatter, we can see the structure better.
    {
       "departments":[
          {
             "department":{
                "department_name":"ACCOUNTING",
                "department_no":10,
                "employees":[
                   {
                      "employee_number":7782,
                      "employee_name":"CLARK"
                   },
                   {
                      "employee_number":7839,
                      "employee_name":"KING"
                   },
                   {
                      "employee_number":7934,
                      "employee_name":"MILLER"
                   }
                ]
             },
             "department":{
                "department_name":"RESEARCH",
                "department_no":20,
                "employees":[
                   {
                      "employee_number":7369,
                      "employee_name":"SMITH"
                   },
                   {
                      "employee_number":7566,
                      "employee_name":"JONES"
                   },
                   {
                      "employee_number":7788,
                      "employee_name":"SCOTT"
                   },
                   {
                      "employee_number":7876,
                      "employee_name":"ADAMS"
                   },
                   {
                      "employee_number":7902,
                      "employee_name":"FORD"
                   }
                ]
             },
             "department":{
                "department_name":"SALES",
                "department_no":30,
                "employees":[
                   {
                      "employee_number":7499,
                      "employee_name":"ALLEN"
                   },
                   {
                      "employee_number":7521,
                      "employee_name":"WARD"
                   },
                   {
                      "employee_number":7654,
                      "employee_name":"MARTIN"
                   },
                   {
                      "employee_number":7698,
                      "employee_name":"BLAKE"
                   },
                   {
                      "employee_number":7844,
                      "employee_name":"TURNER"
                   },
                   {
                      "employee_number":7900,
                      "employee_name":"JAMES"
                   }
                ]
             },
             "department":{
                "department_name":"OPERATIONS",
                "department_no":40,
                "employees":null
             }
          }
       ]
    }


    Handling NULLs

    All of the SQL/JSON functions have the ability determine how null values are handled. The default is NULL ON NULL, but this can be altered to ABSENT ON NULL.
    -- Default NULL handling.
    SELECT JSON_OBJECT(
             KEY 'employee_name' VALUE e.ename,
             KEY 'commission' VALUE e.comm
           ) AS employees
    FROM   emp e
    WHERE  e.deptno = 10;
    
    EMPLOYEES
    --------------------------------------------------------------------------------
    {"employee_name":"CLARK","commission":null}
    {"employee_name":"KING","commission":null}
    {"employee_name":"MILLER","commission":null}
    
    SQL>
    
    
    -- Explicit NULL ON NULL.
    SELECT JSON_OBJECT(
             KEY 'employee_name' VALUE e.ename,
             KEY 'commission' VALUE e.comm
             NULL ON NULL
           ) AS employees
    FROM   emp e
    WHERE  e.deptno = 10;
    
    EMPLOYEES
    --------------------------------------------------------------------------------
    {"employee_name":"CLARK","commission":null}
    {"employee_name":"KING","commission":null}
    {"employee_name":"MILLER","commission":null}
    
    SQL>
    
    
    -- ABSENT ON NULL.
    SELECT JSON_OBJECT(
             KEY 'employee_name' VALUE e.ename,
             KEY 'commission' VALUE e.comm
             ABSENT ON NULL
           ) AS employees
    FROM   emp e
    WHERE  e.deptno = 10;
    
    EMPLOYEES
    --------------------------------------------------------------------------------
    {"employee_name":"CLARK"}
    {"employee_name":"KING"}
    {"employee_name":"MILLER"}
    
    SQL>


    RETURNING Clause

    The SQL/JSON functions can optionally include a RETURNING clause to specify how the value is returned. All are capable of returning a VARCHAR2 value of varying size specified using either BYTE or CHAR. All except the JSON_OBJECT function can optionally return their output in CLOB format.
    SELECT JSON_OBJECTAGG (
    KEY d.dname VALUE d.deptno RETURNING VARCHAR2(32767 BYTE) ) AS departments FROM dept d ORDER BY d.deptno; SELECT JSON_OBJECTAGG ( KEY d.dname VALUE d.deptno RETURNING VARCHAR2(50 CHAR) ) AS departments FROM dept d ORDER BY d.deptno; SELECT JSON_OBJECTAGG ( KEY d.dname VALUE d.deptno RETURNING CLOB ) AS departments FROM dept d ORDER BY d.deptno;
    The documentation states the default return type is VARCHAR2(4000).


    FORMAT JSON Clause

    The FORMAT JSON clause is optional and is provided for "semantic clarity". I don't understand what this means, but it does seem to have an effect on quoting.
    -- Default. The job is quoted.
    SELECT JSON_OBJECTAGG ( KEY e.ename VALUE e.job ) AS employees FROM emp e WHERE e.deptno = 10; EMPLOYEES -------------------------------------------------------------------------------- {"CLARK":"MANAGER","KING":"PRESIDENT","MILLER":"CLERK"} SQL> -- Explicit FORMAT JSON. Notice lack of quotes on job. SELECT JSON_OBJECTAGG ( KEY e.ename VALUE e.job FORMAT JSON ) AS employees FROM emp e WHERE e.deptno = 10; EMPLOYEES -------------------------------------------------------------------------------- {"CLARK":MANAGER,"KING":PRESIDENT,"MILLER":CLERK} SQL>


    Using Numerics as Keys

    The SQL/JSON functions don't accept numeric as keys.
    SELECT JSON_OBJECTAGG (
             KEY e.empno VALUE e.ename
           ) AS employees
    FROM   emp e
    WHERE  e.deptno = 10;
             KEY e.empno VALUE e.ename
                 *
    ERROR at line 2:
    ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
    
    SQL>
    If you need to force their use, simply use the TO_CHAR function to convert them to strings.

    SELECT JSON_OBJECTAGG (
             KEY TO_CHAR(e.empno) VALUE e.ename
           ) AS employees
    FROM   emp e
    WHERE  e.deptno = 10;
    
    EMPLOYEES
    --------------------------------------------------------------------------------
    {"7782":"CLARK","7839":"KING","7934":"MILLER"}
    
    SQL>





    Post a Comment

     
    TECH SUPPORT © 2012 - Designed by INFOSBIRD