Oracle APEX 22.2 REST API嵌套JSON输出不符预期求助

使用Oracle APEX 22.2创建REST API，数据库版本19c，编写了如下SQL代码，但未得到预期的嵌套JSON输出：

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

预期的嵌套JSON结构如下：

{
  "departments": [
    {
      "department_name": "Administration",
      "department_no": 10,
      "employees": [
        {
          "employee_number": 200,
          "employee_name": "Jennifer"
        }
      ]
    },
    {
      "department_name": "Marketing",
      "department_no": 20,
      "employees": [
        {
          "employee_number": 201,
          "employee_name": "Michael"
        },
        {
          "employee_number": 202,
          "employee_name": "Pat"
        }
      ]
    },
    {
      "department_name": "Purchasing",
      "department_no": 30,
      "employees": [
        {
          "employee_number": 114,
          "employee_name": "Den"
        },
        {
          "employee_number": 115,
          "employee_name": "Alexander"
        },
        {
          "employee_number": 116,
          "employee_name": "Shelli"
        },
        {
          "employee_number": 117,
          "employee_name": "Sigal"
        },
        {
          "employee_number": 118,
          "employee_name": "Guy"
        },
        {
          "employee_number": 119,
          "employee_name": "Karen"
        }
      ]
    }
  ]
}

调整后的SQL代码如下：

SELECT JSON_OBJECT(
    KEY 'departments' VALUE JSON_ARRAYAGG(
        JSON_OBJECT(
            KEY 'department_name' VALUE d.DEPARTMENT_NAME,
            KEY 'department_no' VALUE d.DEPARTMENT_ID,
            KEY 'employees' VALUE COALESCE(
                (SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                        KEY 'employee_number' VALUE e.EMPLOYEE_ID,
                        KEY 'employee_name' VALUE e.FIRST_NAME
                    ) ORDER BY e.EMPLOYEE_ID
                ) FROM OEHR_EMPLOYEES e WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID),
                JSON_ARRAY()
            )
        ) ORDER BY d.DEPARTMENT_ID
    )
) AS departments
FROM OEHR_DEPARTMENTS d
WHERE d.DEPARTMENT_ID IN (10,20,30);

修改说明 #

1. 移除了外层从dual查询的结构，直接生成包含departments根键的JSON对象，避免多余的content_type字段干扰输出结构
2. 使用COALESCE函数处理无员工的部门，返回空数组JSON_ARRAY()而非null，与预期结构完全匹配
3. 增加ORDER BY子句，让部门和员工的输出顺序保持稳定，符合预期的有序结构
4. 如果REST API需要直接返回部门数组而非包含根键的对象，可以简化为以下写法：

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        KEY 'department_name' VALUE d.DEPARTMENT_NAME,
        KEY 'department_no' VALUE d.DEPARTMENT_ID,
        KEY 'employees' VALUE COALESCE(
            (SELECT JSON_ARRAYAGG(
                JSON_OBJECT(
                    KEY 'employee_number' VALUE e.EMPLOYEE_ID,
                    KEY 'employee_name' VALUE e.FIRST_NAME
                ) ORDER BY e.EMPLOYEE_ID
            ) FROM OEHR_EMPLOYEES e WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID),
            JSON_ARRAY()
        )
    ) ORDER BY d.DEPARTMENT_ID
) AS departments
FROM OEHR_DEPARTMENTS d
WHERE d.DEPARTMENT_ID IN (10,20,30);

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