SQL> select
  2    json_array(department_id, department_name) depts
  3  from hr.departments
  4  where department_id > 200;

DEPTS
--------------------------------------------------------
[210,"IT Support"]
[220,"NOC"]
[230,"IT Helpdesk"]
[240,"Government Sales"]
[250,"Retail Sales"]
[260,"Recruiting"]
[270,"Payroll"]

7 rows selected.


SQL> select
  2    json_object(
  3      'DeptID' is department_id,
  4      'Name'   is department_name) depts
  5  from hr.departments
  6  where department_id > 200;

DEPTS
------------------------------------------------
{"DeptID":210,"Name":"IT Support"}
{"DeptID":220,"Name":"NOC"}
{"DeptID":230,"Name":"IT Helpdesk"}
{"DeptID":240,"Name":"Government Sales"}
{"DeptID":250,"Name":"Retail Sales"}
{"DeptID":260,"Name":"Recruiting"}
{"DeptID":270,"Name":"Payroll"}

7 rows selected.


SQL>  select json_object(
  2               'department' value d.department_name,
  3               'employees' value json_arrayagg(
  4                 json_object(
  5                   'name' value first_name||','||last_name,
  6                   'job' value job_title
  7             )) returning varchar2) j
  8      from   hr.departments d, hr.employees e, hr.jobs j
  9      where  d.department_id = e.department_id
 10     and    e.job_id = j.job_id
 11     and    d.department_id = 100
 12     group  by d.department_name;

J
----------------------------------------------------------------------------------------------------
{"department":"Finance","employees":[{"name":"Nancy,Greenberg","job":"Finance Manager"},{"name":"Lui
s,Popp","job":"Accountant"},{"name":"Jose Manuel,Urman","job":"Accountant"},{"name":"Ismael,Sciarra"
,"job":"Accountant"},{"name":"John,Chen","job":"Accountant"},{"name":"Daniel,Faviet","job":"Accounta
nt"}]}