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

   -- Set JSON content type header
   owa_util.mime_header('application/json', TRUE);

    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;