📋Steps in this guide1/3

JSON_ARRAY Using Subqueries in Oracle Database 23ai/26ai

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

oracle 23configurationintermediate

by OracleDba

14 views

Setup

The examples in this article rely on the following tables.

Code/Command (click line numbers to comment):

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'),
  (20,'RESEARCH','DALLAS'),
  (30,'SALES','CHICAGO'),
  (40,'OPERATIONS','BOSTON');

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values
  (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20),
  (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30),
  (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30),
  (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20),
  (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30),
  (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30),
  (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10),
  (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20),
  (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10),
  (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30),
  (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20),
  (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30),
  (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20),
  (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;

JSON_ARRAY in Previous Database Versions

The function was introduced in Oracle database 12.1, allowing us to convert a comma-separated list of expressions into a JSON array. The problem with the initial implementation of 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 to pretty the output, but it is not necessary. Now we want to add an array of employees for the department. Logically we would expect to use the function, but instead we are forced to use to achieve this. We have achieved the result we wanted, but we didn't use because of the limitations of its implementation.

Code/Command (click line numbers to comment):

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

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

SQL>

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>

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>

JSON_ARRAY in Oracle Database 23ai/26ai

From Oracle database 23ai/26ai onward the 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. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!