Instantly share code, notes, and snippets.

Embed
What would you like to do?
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