在返回JSON_OBJECT_T数组的SQL查询中添加OFFSET和FETCH NEXT报错求助

错误原因 #

ORA-00937错误是因为你直接在包含聚合函数JSON_ARRAYAGG的主查询中使用OFFSET和FETCH NEXT子句。JSON_ARRAYAGG属于分组聚合函数（默认将全表作为一个分组），而分页子句是针对单行数据的筛选操作，两者直接混用会破坏分组逻辑，导致报错。

解决方案 #

先通过子查询完成数据分页筛选，再对筛选后的结果执行JSON聚合操作，避免聚合函数与分页子句的逻辑冲突。

修改后的PL/SQL代码 #

DECLARE
    l_json_object JSON_OBJECT_T;
    l_json CLOB;
BEGIN
   SELECT
      JSON_OBJECT(
         KEY 'employees' VALUE 
            JSON_ARRAYAGG(
               JSON_OBJECT(KEY 'empno' VALUE id
                           ,KEY 'ename' VALUE name
                           ,KEY 'job' VALUE email_id)
               ORDER BY id
           RETURNING CLOB) 
        RETURNING CLOB)
     INTO l_json
     FROM (
         -- 先分页筛选需要的数据
         SELECT id, name, email_id
         FROM employee_tbl
         ORDER BY id DESC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY
     );
   -- 转换CLOB为JSON_OBJECT_T
   l_json_object := JSON_OBJECT_T.PARSE(l_json);

   -- 输出验证结果
   dbms_output.put_line(l_json_object.to_clob);
END;
/

示例表结构 #

CREATE TABLE employee_tbl 
(
    id, 
    name, 
    email_id
) AS
  SELECT 1065, 'Abu',  'email1@example.com' FROM DUAL UNION ALL
  SELECT 1066, 'Umar', 'email2@example.com' FROM DUAL

期望输出 #

{
    "employees":
        [
            { "empno": 1065,"ename": "Abu", "job": "email1@example.com" },
            { "empno": 1066, "ename": "Umar", "job": "email2@example.com" }
        ]
}

内容的提问来源于stack exchange，提问作者mu shaikh