结合json_arrayagg、json_object与json_table查询返回首行结果异常

你碰到的这个问题确实是Oracle 12.2.0.1和19c版本里的一个优化器Bug——当你在查询里同时保留v_f_1000和v_f_2这两个标量子查询列时，优化器会错误地复用聚合后的JSON结果，导致json_data列返回了不属于当前分组的内容，甚至用NO_QUERY_TRANSFORMATION提示还触发了ORA-600，这完全是Oracle内部逻辑的问题。

问题重现SQL #

SELECT (SELECT v_f FROM json_table(R_P.json_data, '$[*]' columns (r_p_i NUMBER path '$.r_p_i', v_f path '$.v_f')) WHERE r_p_i = 1000 ) AS "v_f_1000",
(SELECT v_f FROM json_table(R_P.json_data, '$[*]' columns (r_p_i NUMBER path '$.r_p_i', v_f path '$.v_f')) WHERE r_p_i = 2 ) AS "v_f_2",
R_P.json_data, R_P.r_c, R_P.r_r
FROM (SELECT json_arrayagg(json_object(KEY 'r_p_i' value r_p_i, KEY 'v_f' value v_f, KEY 'u_i' value u_i ABSENT ON NULL) ORDER BY NULL) json_data, r_c, r_r
FROM (SELECT '26658' AS r_c, '00' AS r_r, 1000 AS r_p_i, 8.5 AS v_f, 'MM' AS u_i FROM dual UNION ALL
SELECT '26917' AS r_c, '00' AS r_r, 2 AS r_p_i, 10 AS v_f, 'R71' AS u_i FROM dual )
GROUP BY r_c, r_r ) R_P;

实际返回结果 #

预期返回结果 #

临时解决方案 #

针对这个Bug，你可以尝试以下几种绕过方法：

1. 改用JSON_VALUE直接提取值
   把外层的标量子查询替换为JSON_VALUE函数，利用JSON路径直接定位取值，能绕过优化器的错误转换逻辑：

   SELECT JSON_VALUE(R_P.json_data, '$[*]?(@.r_p_i == 1000).v_f') AS "v_f_1000",
          JSON_VALUE(R_P.json_data, '$[*]?(@.r_p_i == 2).v_f') AS "v_f_2",
          R_P.json_data, R_P.r_c, R_P.r_r
   FROM (SELECT json_arrayagg(json_object(KEY 'r_p_i' value r_p_i, KEY 'v_f' value v_f, KEY 'u_i' value u_i ABSENT ON NULL) ORDER BY NULL) json_data, r_c, r_r
         FROM (SELECT '26658' AS r_c, '00' AS r_r, 1000 AS r_p_i, 8.5 AS v_f, 'MM' AS u_i FROM dual UNION ALL
               SELECT '26917' AS r_c, '00' AS r_r, 2 AS r_p_i, 10 AS v_f, 'R71' AS u_i FROM dual )
         GROUP BY r_c, r_r ) R_P;
   

2. 添加唯一标识干扰优化器复用
   如果必须保留json_table的写法，可以在聚合的JSON对象中添加一个依赖分组列的冗余字段，确保每个分组的JSON结构唯一，避免优化器错误复用结果：

   SELECT (SELECT v_f FROM json_table(R_P.json_data, '$[*]' columns (r_p_i NUMBER path '$.r_p_i', v_f path '$.v_f')) WHERE r_p_i = 1000 ) AS "v_f_1000",
          (SELECT v_f FROM json_table(R_P.json_data, '$[*]' columns (r_p_i NUMBER path '$.r_p_i', v_f path '$.v_f')) WHERE r_p_i = 2 ) AS "v_f_2",
          R_P.json_data, R_P.r_c, R_P.r_r
   FROM (SELECT json_arrayagg(json_object(KEY 'r_p_i' value r_p_i, KEY 'v_f' value v_f, KEY 'u_i' value u_i ABSENT ON NULL, KEY 'dummy' value r_c) ORDER BY NULL) json_data, r_c, r_r
         FROM (SELECT '26658' AS r_c, '00' AS r_r, 1000 AS r_p_i, 8.5 AS v_f, 'MM' AS u_i FROM dual UNION ALL
               SELECT '26917' AS r_c, '00' AS r_r, 2 AS r_p_i, 10 AS v_f, 'R71' AS u_i FROM dual )
         GROUP BY r_c, r_r ) R_P;
   

3. 升级Oracle版本
   这个Bug在Oracle 21c及后续版本中已经被修复，如果业务允许，升级到更高版本可以彻底解决问题。

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