Monday, May 1, 2023

JSON_ARRAY Using Subqueries in Oracle Database 23c

Oracle Database, Oracle Database Career, Oracle Database Skills, Oracle Database Jobs, Oracle Database Prep, Oracle Database Preparation, Oracle Database Guides, Oracle Database Learning

From Oracle database 23c onward the JSON_ARRAY function can accept a subquery as input, which brings it in line with the current SQL/JSON standard.

◉ Setup


The examples in this article rely on the following tables.

drop table if exists emp purge;
drop table if exists dept purge;

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_ARRAY in Previous Database Versions


The JSON_ARRAY function was introduced in Oracle database 12.1, allowing us to convert a comma-separated list of expressions into a JSON array.

select json_array(empno, ename)
from   emp
where deptno = 10;

JSON_ARRAY(EMPNO,ENAME)
--------------------------------------------------------------------------------
[7782,"CLARK"]
[7839,"KING"]
[7934,"MILLER"]

SQL>

The problem with the initial implementation of JSON_ARRAY was we were not able to supply a subquery as an argument, which limited its flexibility. Suppose we wanted to create a JSON document for a department. We might do the following. We've used JSON_SERIALIZE to pretty the output, but it is not necessary.

select json_serialize(
         json_object('department_number' : d.deptno,
                     'department_name' : d.dname)
         pretty) as output
from   dept d
where  d.deptno = 10;

OUTPUT
--------------------------------------------------------------------------------
{
  "department_number" : 10,
  "department_name" : "ACCOUNTING"
}

SQL>

Now we want to add an array of employees for the department. Logically we would expect to use the JSON_ARRAY function, but instead we are forced to use JSON_ARRAYAGG to achieve this.

select json_serialize(
         json_object('department_number' : d.deptno,
                     'department_name' : d.dname,
                     'employees' : (select json_arrayagg(json_object('employee_number' : e.empno,
                                                                     'employee_name' : e.ename))
                                    from   emp e
                                    where  e.deptno = d.deptno)
         )
       pretty) as output
from   dept d
where  d.deptno = 10;

OUTPUT
--------------------------------------------------------------------------------
{
  "department_number" : 10,
  "department_name" : "ACCOUNTING",
  "employees" :
  [
    {
      "employee_number" : 7782,
      "employee_name" : "CLARK"
    },
    {
      "employee_number" : 7839,
      "employee_name" : "KING"
    },
    {
      "employee_number" : 7934,
      "employee_name" : "MILLER"
    }
  ]
}

SQL>

We have achieved the result we wanted, but we didn't use JSON_ARRAY because of the limitations of its implementation.

◉ JSON_ARRAY in Oracle Database 23c


From Oracle database 23c onward the JSON_ARRAY function can accept a subquery as input, which brings it in line with the current SQL/JSON standard. Now we can rewrite the previous statement as follows.

select json_serialize(
         json_object('department-number' : d.deptno,
                     'department-name' : d.dname,
                     'employees' : json_array(select json_object('employee-number' : e.empno,
                                                                 'employee-name'   : e.ename)
                                              from emp e
                                              where e.deptno = d.deptno)
         )
        pretty) as output
from   dept d
where  d.deptno = 10;

OUTPUT
--------------------------------------------------------------------------------
{
  "department-number" : 10,
  "department-name" : "ACCOUNTING",
  "employees" :
  [
    {
      "employee-number" : 7782,
      "employee-name" : "CLARK"
    },
    {
      "employee-number" : 7839,
      "employee-name" : "KING"
    },
    {
      "employee-number" : 7934,
      "employee-name" : "MILLER"
    }
  ]
}

SQL>

Source: oracle-base.com

Related Posts

0 comments:

Post a Comment