Last active
October 11, 2018 21:12
-
-
Save dmcghan/127cdeaf770203d467f8354deb31a598 to your computer and use it in GitHub Desktop.
Example query that generates JSON for a department in the HR schema of Oracle Database. Uses the returning clause to specify data types.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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