SELECT 
    JSON_OBJECT
    (
         'departmentId' is d.DEPARTMENT_ID,
         'name' is d.DEPARTMENT_NAME,
         'employees' is 
         (
             SELECT JSON_ARRAYAGG
             (
                 JSON_OBJECT
                 (
                     'employeeId' is E.EMPLOYEE_ID,
                     'firstName' is E.FIRST_NAME,
                     'lastName' is E.LAST_NAME,
                     'emailAddress' is E.EMAIL
                  )
              )
              FROM HR.EMPLOYEES E
              WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
          )
      ) DEPT_WITH_EMPLOYEES
FROM HR.DEPARTMENTS D
WHERE D.DEPARTMENT_ID = 100 ;

select  '{"departmentId":' || d.department_id || ',"name":"' ||
        d.department_name || '","employees":[' ||
        replace(
                xmlcast(
                        xmlagg(xmlelement("e",
                                          '{"employeeId":'    || employee_id ||
                                          ',"firstName":"'    || first_name ||
                                          '","lastName":'     || last_name ||
                                          '","emailAddress":' || email || '"}'
                                         )
                               order by employee_id
                              )
                        as clob
                        ),
                '}{',
                '},{'
               ) || ']}' dept_with_employees
  from  hr.employees e,
        hr.departments d
  where d.department_id = e.department_id
    and d.department_id = 100
  group by d.department_id,
           d.department_name
/

DEPT_WITH_EMPLOYEES
--------------------------------------------------------------------------------
{"departmentId":100,"name":"Finance","employees":[{"employeeId":108,"firstName":
"Nancy","lastName":Greenberg","emailAddress":NGREENBE"},{"employeeId":109,"first
Name":"Daniel","lastName":Faviet","emailAddress":DFAVIET"},{"employeeId":110,"fi
rstName":"John","lastName":Chen","emailAddress":JCHEN"},{"employeeId":111,"first
Name":"Ismael","lastName":Sciarra","emailAddress":ISCIARRA"},{"employeeId":112,"
firstName":"Jose Manuel","lastName":Urman","emailAddress":JMURMAN"},{"employeeId
":113,"firstName":"Luis","lastName":Popp","emailAddress":LPOPP"}]}


SQL> 

select  '{"departmentId":' || d.department_id || ',"name":"' ||
        d.department_name || '","employees":[' ||
        replace(
                xmlcast(
                        xmlagg(xmlelement("e",
                                          '{"employeeId":'     || employee_id ||
                                          ',"firstName":"'     || first_name ||
                                          '","lastName":"'     || last_name ||
                                          '","emailAddress":"' || email || '"}'
                                         )
                               order by employee_id
                              )
                        as clob
                        ),
                '}{',
                '},{'
               ) || ']}' dept_with_employees
  from  hr.employees e,
        hr.departments d
  where d.department_id = e.department_id
    and d.department_id = 100
  group by d.department_id,
           d.department_name
/ 

SQL> select  banner
  2    from  v$version
  3  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> drop table tbl purge
  2  /

Table dropped.

SQL> create table tbl(
  2                   json_doc clob
  3                  )
  4  /

Table created.

SQL> alter table tbl
  2    add constraint validate_json
  3      check(
  4            json_doc is json
  5           )
  6  /

Table altered.

SQL> insert
  2    into tbl
  3    values('XYZ')
  4  /
insert
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.VALIDATE_JSON) violated


SQL> insert
  2    into tbl
  3    values('{"departmentId":100,"name":"Finance","employees":[{"employeeId":108,"firstName":"Nancy","lastName":"Greenberg","emailAddress":"NGREENBE"},{"employeeId":109,"firstName":"Daniel","lastName
":"Faviet","emailAddress":"DFAVIET"},{"employeeId":110,"firstName":"John","lastName":"Chen","emailAddress":"JCHEN"},{"employeeId":111,"firstName":"Ismael","lastName":"Sciarra","emailAddress":"ISCIARRA"
},{"employeeId":112,"firstName":"Jose Manuel","lastName":"Urman","emailAddress":"JMURMAN"},{"employeeId":113,"firstName":"Luis","lastName":"Popp","emailAddress":"LPOPP"}]}')
  4  /

1 row created.

SQL> select  department_id,
  2          department_name,
  3          employee_id,
  4          first_name,
  5          last_name,
  6          email
  7    from  tbl,
  8          json_table(
  9                     json_doc,
 10                     '$'
 11                     columns(
 12                             department_id   number path '$.departmentId',
 13                             department_name varchar2(10) path '$.name',
 14                             nested path '$.employees[*]'
 15                             columns(
 16                                     employee_id number path '$.employeeId',
 17                                     first_name  varchar2(15) path '$.firstName',
 18                                     last_name   varchar2(15) path '$.lastName',
 19                                     email       varchar2(15) path '$.emailAddress'
 20                                    )
 21                            )
 22                    )
 23  /

DEPARTMENT_ID DEPARTMENT EMPLOYEE_ID FIRST_NAME      LAST_NAME       EMAIL
------------- ---------- ----------- --------------- --------------- ---------------
          100 Finance            108 Nancy           Greenberg       NGREENBE
          100 Finance            109 Daniel          Faviet          DFAVIET
          100 Finance            110 John            Chen            JCHEN
          100 Finance            111 Ismael          Sciarra         ISCIARRA
          100 Finance            112 Jose Manuel     Urman           JMURMAN
          100 Finance            113 Luis            Popp            LPOPP

6 rows selected.

SQL>