Oracle中JSON_ARRAYAGG的NULL ON NULL子句为何未生成NULL元素

你的判断完全正确，这是Oracle部分版本中JSON_ARRAYAGG聚合函数的已知bug。

语法逻辑说明 #

按照Oracle官方对SQL/JSON聚合函数的语义定义：

• JSON_ARRAYAGG(列名 NULL ON NULL)：聚合过程中遇到列值为NULL的记录时，需要将JSON格式的null作为元素写入最终生成的JSON数组，不能跳过该记录
• JSON_ARRAYAGG(列名 ABSENT ON NULL)：聚合过程中遇到列值为NULL的记录时，直接跳过该记录，不向数组中写入对应元素
  你构造的测试用例完全符合验证规则：CTE中一共4条记录，其中1条x字段为NULL，理论上两个字段的返回结果应该存在明确差异：XNN返回["foo","bar",null,"baz"]，XAN返回["foo","bar","baz"]，你实际得到的两个字段返回值完全一致，属于函数没有正确执行NULL ON NULL语义的bug表现。

影响范围与临时规避 #

该bug主要出现在Oracle 12cR2（12.2.0.1）早期补丁版本、18c初始版本中，后续的Release Update补丁以及19c及以上版本已经修复了该问题。
如果需要在受影响的版本中实现NULL ON NULL的预期效果，可以通过显式构造JSON类型null值的方式绕开bug，参考写法：

with t as (
   select 'foo' x from dual union all
   select 'bar' x from dual union all
   select  null x from dual union all
   select 'baz' x from dual
)
select
   json_arrayagg(
       case when x is null then json_value('{"n":null}', '$.n') else x end 
       null on null
   ) xnn,
   json_arrayagg(x absent on null) xan
from
   t;

执行上述语句即可得到符合语义预期的结果，XNN字段会正确保留null元素。

内容的提问来源于stack exchange，提问作者René Nyffenegger