Why does the NULL ON NULL clause in JSON_ARRAYAGG not produce a NULL element?

Viewed 50

The following query ...

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(x null   on null) xnn,
   json_arrayagg(x absent on null) xan
from
   t;

... returns

XNN                 XAN
------------------- -------------------
["foo","bar","baz"] ["foo","bar","baz"]

I believe this is a bug on Oracle's part and XNN should evaluate to ["foo","bar", null, "baz"].

Can someone confirm my suspicion?

1 Answers

Yes, this is a bug in an early version of Oracle 19 and is fixed in Oracle 19.15 (or possibly earlier).

Related