Oracle APEX REST API 嵌套 JSON 输出

问：

我编写了以下代码，该代码未显示我想要的输出。我想显示与屏幕截图中完全相同的输出。我在 Oracle APEX 版本 22.2 中创建了 REST API，数据库版本为 19c。

SELECT 'application/json' as content_type, JSON_OBJECT (
    KEY 'departments' VALUE (
        SELECT JSON_ARRAYAGG(
            JSON_OBJECT (
                KEY 'department_name' VALUE d.DEPARTMENT_NAME,
                KEY 'department_no' VALUE d.DEPARTMENT_ID,
                KEY 'employees' VALUE (
                    SELECT JSON_ARRAYAGG (
                        JSON_OBJECT (
                            KEY 'employee_number' VALUE e.EMPLOYEE_ID,
                            KEY 'employee_name' VALUE e.FIRST_NAME))
                    FROM   OEHR_EMPLOYEES e
                    WHERE  e.DEPARTMENT_ID = d.DEPARTMENT_ID)))
        FROM   OEHR_DEPARTMENTS d
        where d.DEPARTMENT_ID in (10,20,30)))
AS departments
FROM dual