Oracle APEX GET API创建报错：SQL查询在SQL Workshop正常运行

我编写的SQL查询在SQL Workshop中运行正常，生成的JSON格式也验证无误，但创建GET类型Rest API时出现报错。

SQL查询语句：

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;

报错信息：
ORA-00904: "D"."DEPARTMENT_ID": invalid identifier，即内层子查询无法识别外层表的别名d。

该问题源于Oracle REST Data Services (ORDS) 对嵌套JSON子查询的别名解析存在上下文限制，可通过以下两种方式修复：

方式1：用WITH子句重构查询，明确层级关系 #

将部门与员工的关联查询提前定义，避免深层嵌套导致的别名识别问题：

WITH dept_emps AS (
    SELECT 
        d.DEPARTMENT_ID,
        d.DEPARTMENT_NAME,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                KEY 'employee_number' VALUE e.EMPLOYEE_ID,
                KEY 'employee_name' VALUE e.FIRST_NAME
            )
        ) AS employees
    FROM OEHR_DEPARTMENTS d
    LEFT JOIN OEHR_EMPLOYEES e ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
    WHERE d.DEPARTMENT_ID IN (10,20,30)
    GROUP BY d.DEPARTMENT_ID, d.DEPARTMENT_NAME
)
SELECT 
    'application/json' AS content_type,
    JSON_OBJECT(
        KEY 'departments' VALUE JSON_ARRAYAGG(
            JSON_OBJECT(
                KEY 'department_name' VALUE DEPARTMENT_NAME,
                KEY 'department_no' VALUE DEPARTMENT_ID,
                KEY 'employees' VALUE employees
            )
        )
    ) AS departments
FROM dept_emps;

方式2：强化子查询的关联条件声明 #

在员工子查询中添加显式条件，确保外层表别名能被正确识别：

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
                        AND d.DEPARTMENT_ID IS NOT NULL
                      )
                    )
                  )
           FROM   OEHR_DEPARTMENTS d
           where d.DEPARTMENT_ID in (10,20,30)
         )
       ) AS departments
FROM   dual;

额外说明 #

ORDS对嵌套子查询的JSON生成支持不如SQL Workshop灵活，采用JOIN+GROUP BY的WITH子句写法，既能避免别名解析问题，还能提升查询性能，更适合作为REST API的数据源。

内容的提问来源于stack exchange，提问作者Mohammad Ubaid