Oracle 19c生成嵌套JSON遇ORA-00935错误求助

错误原因 #

ORA-00935是因为Oracle 19c不允许聚合函数直接嵌套——比如你不能在JSON_OBJECTAGG里直接嵌套JSON_ARRAYAGG，反之亦然，这违反了聚合函数的语法规则。而分组重复问题通常是因为分组键设置不合理，导致同一层级的数据被重复聚合。

解决方案：分层聚合（CTE/子查询） #

核心思路是拆分嵌套逻辑，先用子查询或CTE处理内层的JSON聚合，再在外层进行上层的JSON构造，彻底避免聚合函数嵌套。同时明确每个层级的分组键，从根源解决数据重复问题。

示例修正步骤 #

假设你的原始错误查询类似这种嵌套聚合写法：

SELECT
  JSON_OBJECTAGG(
    KEY dept_name VALUE JSON_ARRAYAGG(
      JSON_OBJECT(
        'emp_id' VALUE emp_id,
        'emp_name' VALUE emp_name
      )
    )
  ) AS dept_emps
FROM employees
GROUP BY dept_id;

这种写法直接嵌套聚合函数，必然触发ORA-00935。

修正后用CTE分层处理：

WITH emp_json AS (
  -- 第一层：按部门聚合生成员工JSON数组
  SELECT
    dept_id,
    dept_name,
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'emp_id' VALUE emp_id,
        'emp_name' VALUE emp_name
      ) ORDER BY emp_id
    ) AS emp_list
  FROM employees
  GROUP BY dept_id, dept_name -- 明确分组键，避免重复聚合
)
-- 第二层：聚合部门级的JSON对象
SELECT
  JSON_OBJECTAGG(
    KEY dept_name VALUE emp_list ORDER BY dept_name
  ) AS dept_emps
FROM emp_json;

关键要点 #

• 分层聚合：把嵌套的聚合逻辑拆成多个独立步骤，每一步仅使用一层聚合函数，彻底规避嵌套限制。
• 明确分组键：每个GROUP BY子句必须包含所有非聚合列，确保分组的唯一性，解决重复聚合问题。
• 添加ORDER BY：在JSON_ARRAYAGG/JSON_OBJECTAGG中加入ORDER BY，既保证JSON结构的顺序稳定，也能避免潜在的重复聚合风险。

额外注意事项 #

如果数据中存在重复的分组键（比如同一部门名对应多个部门ID），需要先处理数据唯一性——比如用DISTINCT筛选，或调整分组键为唯一标识（如dept_id），再构造JSON的键名。

内容的提问来源于stack exchange，提问作者Rajiv A