Fetching nested JSON from a Master-Detail Query in a Oracle ORDS Handler Definition
This is a really quick blog to demonstrate how to return nested JSON from a Master-Detail in one REST JSON GET Handler
Say for instance you have a parent-child relationship like Dept-Emp and you want to return Departments and all employees nested under those Departments in JSON, like in this format below
{
"departments": [
{
"deptno": 10,
"dname": "ACCOUNTING",
"employees": [
{
"empno": 7839,
"ename": "KING",
"job": "PRESIDENT"
]
},
{
"deptno": 20,
"dname": "RESEARCH",
"employees": [
{
"empno": 7566,
"ename": "JONES",
"job": "MANAGER"
}
]
},
{
"deptno": 30,
"dname": "SALES",
"employees": [
{
"empno": 7698,
"ename": "BLAKE",
"job": "MANAGER"
},
{
"empno": 7900,
"ename": "JAMES",
"job": "CLERK"
}
]
},
{
"deptno": 40,
"dname": "OPERATIONS",
"employees": [
{
"empno": null,
"ename": null,
"job": null
}
]
}
]
}
Then you need a query like this to produce the intended results
SELECT JSON_OBJECT (
'departments' VALUE JSON_ARRAYAGG (
JSON_OBJECT (
'deptno' VALUE d.deptno,
'dname' VALUE d.dname,
'employees' VALUE JSON_ARRAYAGG (
JSON_OBJECT (
'empno' VALUE e.empno,
'ename' VALUE e.ename,
'job' VALUE e.job
)
)
)
)
) AS result
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno, d.dname
Then in your ORDS GET Handler, set the Source Type to PLSQL
..then write out the results using htp.p like this...
BEGIN
FOR x IN (
SELECT JSON_OBJECT (
'departments' VALUE JSON_ARRAYAGG (
JSON_OBJECT (
'deptno' VALUE d.deptno,
'dname' VALUE d.dname,
'employees' VALUE JSON_ARRAYAGG (
JSON_OBJECT (
'empno' VALUE e.empno,
'ename' VALUE e.ename,
'job' VALUE e.job
)
)
)
)
) AS result
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno, d.dname )
LOOP
htp.p (x.result);
END LOOP;
END;
Give the URL a test in your browser...
... and... RESULT!