Example query that generates JSON for a department in the HR schema of Oracle Database. Uses the returning clause to specify data types.

department_json.sql

select json_object(
  'id' is department_id,
  'name' is department_name,
  'location' is (
    select json_object(
      'id' is location_id,
      'streetAddress' is street_address,
      'postalCode' is postal_code,
      'country' is (
        select json_object(
          'id' is country_id,
          'name' is country_name,
          'regionId' is region_id 
        )
        from countries
        where country_id = loc.country_id
      )
    )
    from locations loc
    where location_id = dept.location_id
  ),
  'manager' is (
    select json_object(
      'id' is employee_id,
      'name' is first_name || ' ' || last_name,
      'salary' is salary,
      'job' is (
        select json_object(
          'id' is job_id,
          'title' is job_title,
          'minSalary' is min_salary,
          'maxSalary' is max_salary
        )
        from jobs
        where job_id = man.job_id
      )
    )
    from employees man
    where employee_id = dept.manager_id
  ),
  'employees' is (
    select json_arrayagg(
      json_object(
        'id' is employee_id,
        'name' is first_name || ' ' || last_name,
        'isSenior' is 
          case when emp.hire_date < to_date('01-01-2005', 'dd-mm-yyyy')
            then 'true'
            else 'false'
          end format json,
        'commissionPct' is commission_pct,
        'jobHistory' is (
          select json_arrayagg(
            json_object(
              'id' is job_id,
              'departmentId' is department_id,
              'startDate' is to_char(start_date, 'DD-MON-YYYY'),
              'endDate' is to_char(end_date, 'DD-MON-YYYY')
            )
          )
          from job_history
          where employee_id = emp.employee_id
        )
      )
    returning varchar2(32767))
    from employees emp
    where department_id = dept.department_id
  )
returning varchar2(32767)) as department
from departments dept
where department_id = :department_id