Skip to content
Advertisement

Oracle bug when using JSON_ARRAYAGG with CASE expression to emulate standard SQL FILTER

I’m using Oracle 18c (reproduces in 19c as well) and I’d like to emulate the standard SQL FILTER clause in the JSON_ARRAYAGG() aggregate function to filter out values from the aggregation. However, this produces an error:

select json_arrayagg(
  case when t.a < 2 then json_object(key 'a' value t.a) end
)
from (
  select 1 a
  from dual
  union all
  select 2 a
  from dual
) t

The error being:

ORA-40590: invalid format

dbfiddle here. What’s causing the error? Is it a bug in Oracle?

Advertisement

Answer

It does seem to be a bug, which has been fixed already in 19.10. Adding an explicit ELSE clause seems to resolve the issue:

select json_arrayagg(
  case 
    when t.a < 2 then json_object(key 'a' value t.a) 
    else null
  end
)
from (
  select 1 a
  from dual
  union all
  select 2 a
  from dual
) t

This now produces the expected result:

[{"a":1}]

dbfiddle here.

10 People found this is helpful
Advertisement