Oracle JSON_OBJECT NULL ON NULL clause not working

Question

I'm trying to get Oracle to produce JSON null values on SQL NULL data, as follows:

select 
  json_object(key 'a' value 1, key 'b' value null null on null)   c1, 
  json_object(key 'a' value 1, key 'b' value null absent on null) c2
from dual;

Or also:

select 
  json_object(key 'a' value a, key 'b' value b null on null)   c1, 
  json_object(key 'a' value a, key 'b' value b absent on null) c2
from (
  select 1 a, null b
  from dual
) t;

Unfortunately, both queries result in:

|C1        |C2        |
|----------|----------|
|{"a":1}   |{"a":1}   |

I would have expected this, instead:

|C1                 |C2        |
|-------------------|----------|
|{"a":1,"b":null}   |{"a":1}   |

What am I missing? I'm using Oracle XE 18c

score 3 · Accepted Answer · answered Mar 10 '20 at 15:32

3

There appears to be a bug on how Oracle handles JSON_OBJECT and it will take the parameter from the last JSON_on_null_clause in the statement and apply it to all of the JSON_OBJECT expressions:

CREATE TABLE t ( a,b ) AS
  SELECT 1, null FROM DUAL UNION ALL
  SELECT 2, 'bb' FROM DUAL;

If you do this:

select json_object(
         KEY 'a' VALUE a,
         KEY 'b' VALUE b null on null
       ) c1,
       json_object(
         KEY 'a' VALUE a,
         KEY 'b' VALUE b absent on null
       ) c2
from   t;

Then the output is:

C1               | C2              
:--------------- | :---------------
{"a":1}          | {"a":1}         
{"a":2,"b":"bb"} | {"a":2,"b":"bb"}

If you perform the same query with the expressions reversed:

select json_object(
         KEY 'a' VALUE a,
         KEY 'b' VALUE b absent on null
       ) c2,
       json_object(
         KEY 'a' VALUE a,
         KEY 'b' VALUE b null on null
       ) c1
from   t;

Then the output is:

C2               | C1              
:--------------- | :---------------
{"a":1,"b":null} | {"a":1,"b":null}
{"a":2,"b":"bb"} | {"a":2,"b":"bb"}

db<>fiddle here

answered Mar 10 '20 at 15:32

MT0

143,790
11
59
117

1

@LukasEder Just to add, it is not the first time I saw this pattern(i.e taking first/last occurence as execution template: https://stackoverflow.com/questions/52533487/string-agg-not-behaving-as-expected) – Lukasz Szozda Mar 10 '20 at 16:48
@LukaszSzozda: I have seen it before, too, e.g. in HSQLDB or MariaDB, not yet in Oracle. – Lukas Eder Mar 10 '20 at 18:00
1

This is a bug, it's been filed (31013529, though it's not currently public) – Chris Saxon Mar 10 '20 at 18:02
Simple `+0` is typical and well-known workaround for such bugs (such type of bugs is pretty old) – Sayan Malakshinov Jul 29 '20 at 15:20
https://gist.github.com/xtender/afa5ffc01753d9473afead56da4e557a – Sayan Malakshinov Jul 29 '20 at 15:23

score 3 · Answer 2 · answered Mar 10 '20 at 21:12

3

you found a bug. I have filed Bug 31013529 - TWO JSON_OBJECT WITH DIFFERENT ON NULL HANDLER RETURN WRONG RESULTS

We'll fix it asap and include it in the bundle patches. Let us know if you need a one off patch

thanks b

answered Mar 10 '20 at 21:12

Beda Hammerschmidt

416
3
6

I installed oracle 19 a month ago but still see this issue. Could post what patch is needed ? – Puresoft Mar 17 '21 at 14:14

Lukasz Szozda · Answer 3 · 2020-03-10T16:54:31.797

It looks like a bug for me:

select 
  json_object(key 'a' value 1, key 'b' value null null on null)   c1
  ,(SELECT json_object(key 'a' value 1, key 'b' value null absent on null) FROM dual) c2
from dual

Output:

+-------------------+---------+
|        C1         |   C2    |
+-------------------+---------+
| {"a":1,"b":null}  | {"a":1} |
+-------------------+---------+

db<>fiddle demo

It is one of these strange occurences when wrapping with (SELECT exp FROM dual) changes the resultset (https://stackoverflow.com/a/52561721/5070879).

I guess it is some optimization mechanism("common subexpression elimination") that causes the exp is evaluated once taking the first/last occurence. If you change the key it is working fine:

select 
  json_object(key 'a' value 1, key 'b' value null null on null)   c1, 
  json_object(key 'a' value 1, key 'c' value null absent on null) c2
from dual;

db<>fiddle demo

score 0 · Answer 4 · answered Mar 11 '20 at 09:26

0

Lukasz's answer provides a viable workaround using a correlated subquery.

For the record, other functions are also affected, e.g. JSON_ARRAYAGG:

select
  json_arrayagg(a null on null) c1,
  json_arrayagg(a absent on null) c2
from (
  select 1 a from dual union all select null a from dual
) t;

Yields:

|C1        |C2        |
|----------|----------|
|[1]       |[1]       |

Whereas this workaround...

select 
  cast(json_arrayagg((select a from dual) absent on null) as varchar2(10)) c2,
  cast(json_arrayagg((select a from dual) null on null) as varchar2(10)) c1
from (
  select 1 a from dual union all select null a from dual
) t;

Produces the correct result:

|C2        |C1        |
|----------|----------|
|[1]       |[1,null]  |

answered Mar 11 '20 at 09:26

Lukas Eder

211,314
129
689
1,509

Simple `+0` is typical and well-known workaround for such bugs (such type of bugs is pretty old) – Sayan Malakshinov Jul 29 '20 at 15:19
@SayanMalakshinov: That might work in ad-hoc cases, although 1) only with numbers or dates, not strings 2) only if it isn't added to each and every expression, or the same bug re-appears. My workaround seems more thorough, in this case. – Lukas Eder Jul 29 '20 at 15:29
1) `||''` for strings :) *2) only if it isn't added to each and every expression, or the same bug re-appears.* nope, you need just 1 minimum difference with other function calls. Maybe your workaround more thorough, but it also much harder - additional cpu load, latches, etc for each call – Sayan Malakshinov Jul 29 '20 at 15:34
+ returning instead of cast: `json_arrayagg((select a from dual) absent on null returning varchar2(10))` https://gist.github.com/xtender/e8e686cbfad1bae72467ed440b5e0253 – Sayan Malakshinov Jul 29 '20 at 15:40

score -1 · Answer 5 · answered Mar 10 '20 at 15:18

-1

you have a small change in order to get the values:

select 
 json_object(key 'a' value 1, key 'b' value null )   c1, 
 json_object(key 'a' value 1 ) c2
from dual;

The result is C1: C2: {"a":1,"b":null} {"a":1}

answered Mar 10 '20 at 15:18

user12467638

27
2

Oracle JSON_OBJECT NULL ON NULL clause not working

5 Answers5

Linked

Related