Example query that generates JSON for a department in the HR schema of Oracle Database. Uses the returning clause to specify data types.
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment