APEX_JSON is a PL/SQL API included with Oracle Application Express (APEX) 5.0 that provides utilities for parsing and generating JSON. While APEX_JSON was primarily intended to be used by APEX developers, there are some hooks that can allow it to be used as a standalone PL/SQL package.

Solution

The following solution uses APEX_JSON to create the JSON that represents a department in the HR schema. APEX_JSON basically writes JSON content to a buffer. By default, the buffer used is the HTP buffer in the database, as that's what APEX reads. But as you can see with line 27, it's possible to redirect the output to a CLOB buffer instead. Once we've redirected the output, we can make API calls to open/close objects and arrays and write values to them. When we're done writing out the JSON we can make a call to get_clob_output to get the JSON contents. I've highlighted some of the relevant lines...

create or replace function get_dept_apex_json(
   p_dept_id in departments.department_id%type
)

   return clob

is

   cursor manager_cur (
      p_manager_id in employees.employee_id%type
   )
   is
      select *
      from employees
      where employee_id = manager_cur.p_manager_id;

   l_date_format    constant varchar2(20) := 'DD-MON-YYYY';
   l_dept_rec       departments%rowtype;
   l_dept_json_clob clob;
   l_loc_rec        locations%rowtype;
   l_country_rec    countries%rowtype;
   l_manager_rec    manager_cur%rowtype;
   l_job_rec        jobs%rowtype;

begin

   apex_json.initialize_clob_output;

   select *
   into l_dept_rec
   from departments
   where department_id = get_dept_apex_json.p_dept_id;

   apex_json.open_object(); --department

   apex_json.write('id', l_dept_rec.department_id);
   apex_json.write('name', l_dept_rec.department_name);

   select *
   into l_loc_rec
   from locations
   where location_id = l_dept_rec.location_id;

   apex_json.open_object('location');

   apex_json.write('id', l_loc_rec.location_id);
   apex_json.write('streetAddress', l_loc_rec.street_address);
   apex_json.write('postalCode', l_loc_rec.postal_code);

   select *
   into l_country_rec
   from countries cou
   where cou.country_id = l_loc_rec.country_id;

   apex_json.open_object('country');

   apex_json.write('id', l_country_rec.country_id);
   apex_json.write('name', l_country_rec.country_name);
   apex_json.write('regionId', l_country_rec.region_id);

   apex_json.close_object(); --country

   apex_json.close_object(); --location

   open manager_cur(l_dept_rec.manager_id);
   fetch manager_cur into l_manager_rec;

   if manager_cur%found
   then
      apex_json.open_object('manager');

      apex_json.write('id', l_manager_rec.employee_id);
      apex_json.write('name', l_manager_rec.first_name || ' ' || l_manager_rec.last_name);
      apex_json.write('salary', l_manager_rec.salary);

      select *
      into l_job_rec
      from jobs job
      where job.job_id = l_manager_rec.job_id;

      apex_json.open_object('job');

      apex_json.write('id', l_job_rec.job_id);
      apex_json.write('title', l_job_rec.job_title);
      apex_json.write('minSalary', l_job_rec.min_salary);
      apex_json.write('maxSalary', l_job_rec.max_salary);

      apex_json.close_object(); --job

      apex_json.close_object(); --manager
   else
      apex_json.write('manager', '', p_write_null => true);
   end if;

   close manager_cur;

   apex_json.open_array('employees');

   for emp_rec in (
      select *
      from employees
      where department_id = l_dept_rec.department_id
   )
   loop
      apex_json.open_object(); --employee

      apex_json.write('id', emp_rec.employee_id);
      apex_json.write('name', emp_rec.first_name || ' ' || emp_rec.last_name);
      apex_json.write('isSenior', emp_rec.hire_date < to_date('01-jan-2005', 'dd-mon-yyyy'));
      apex_json.write('commissionPct', emp_rec.commission_pct, p_write_null => true);

      apex_json.open_array('jobHistory');

      for jh_rec in (
         select job_id,
            department_id,
            start_date,
            end_date
         from job_history
         where employee_id = emp_rec.employee_id
      )
      loop
          apex_json.open_object(); --job

          apex_json.write('id', jh_rec.job_id);
          apex_json.write('departmentId', jh_rec.department_id);
          apex_json.write('startDate', to_char(jh_rec.start_date, l_date_format));
          apex_json.write('endDate', to_char(jh_rec.end_date, l_date_format));

          apex_json.close_object(); --job
      end loop;

      apex_json.close_array(); --jobHistory

      apex_json.close_object(); --employee
   end loop;

   apex_json.close_array(); --employees
   apex_json.close_object(); --department

   l_dept_json_clob := apex_json.get_clob_output;

   apex_json.free_output;

   return l_dept_json_clob;

exception

   when others
   then      
      if manager_cur%isopen
      then
         close manager_cur;
      end if;

      raise;

end get_dept_apex_json;

Output

When passed a departmentId of 10 , the function returns a CLOB populated with JSON that matches the goal 100%.

{
  "id": 10,
  "name": "Administration",
  "location": {
    "id": 1700,
    "streetAddress": "2004 Charade Rd",
    "postalCode": "98199",
    "country": {
      "id": "US",
      "name": "United States of America",
      "regionId": 2
    }
  },
  "manager": {
    "id": 200,
    "name": "Jennifer Whalen",
    "salary": 4400,
    "job": {
      "id": "AD_ASST",
      "title": "Administration Assistant",
      "minSalary": 3000,
      "maxSalary": 6000
    }
  },
  "employees": [
    {
      "id": 200,
      "name": "Jennifer Whalen",
      "isSenior": true,
      "commissionPct": null,
      "jobHistory": [
        {
          "id": "AD_ASST",
          "departmentId": 90,
          "startDate": "17-SEP-1995",
          "endDate": "17-JUN-2001"
        },
        {
          "id": "AC_ACCOUNT",
          "departmentId": 90,
          "startDate": "01-JUL-2002",
          "endDate": "31-DEC-2006"
        }
      ]
    }
  ]
}

Summary

I really enjoyed working with APEX_JSON — it's my new "go-to" for PL/SQL based JSON generation. APEX_JSON has a very light footprint (it's just a single package) and it takes a minimalistic approach. Rather than compose objects as one would do with PL/JSON, you simply use the package to write JSON to a buffer.

This approach yields some performance benefits, as well. In a basic test where I generated the JSON for every department in the HR schema 100 times in a loop, the APEX_JSON-based solution finished at around 3.5 seconds whereas the PL/JSON based solution took around 17 seconds. That means APEX_JSON about 3.8 times faster than PL/JSON when it comes generating JSON and converting it to a CLOB.

Unfortunately, APEX_JSON is only included with APEX 5.0+. Upgrading your database's APEX instance seems a little extreme if all you want to do is work with JSON (though it is free and doesn't take too long), but if you already have APEX 5.0, then it's a very nice tool to be able to leverage.