Oracle JSON_OBJECT NULL ON NULL子句不起作用

Question

我正试图让Oracle在SQLNULL数据上生成JSONnull值，如下所示：

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;

或者：

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;

不幸的是，这两个查询的结果都是：

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

我本以为会这样：

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

我错过了什么？我使用的是Oracle XE 18c

Answer 1

Oracle处理JSON_OBJECT的方式似乎有一个错误，它将从语句中的最后一个JSON_on_null_clause中获取参数，并将其应用于所有JSON_OBJECT表达式：

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

如果你这样做：

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;

则输出为：

C1|C2：---------------|：---------------{"a"：1}|{"a"：2，"b"："bb"}|{"a&"：2、"b&"："bb&"}

如果用相反的表达式执行相同的查询：

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;

则输出为：

C2|C1：---------------|：---------------｛"a"：1，"b"：null｝|{"a"：2，"b"："bb"}|{"a&"：2、"b&"："bb&"}

db<>小提琴这里

Answer 2

您发现了一个错误。我已提交Bug 31013529-两个空句柄上不同的JSON_OBJECT返回错误的结果

我们会尽快修复它，并将其包含在捆绑补丁中。如果您需要一次性补丁，请告诉我们

谢谢b

Answer 3

对我来说，这看起来像一个bug：

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

输出：

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

db<>小提琴演示

当用(SELECT exp FROM dual)包装更改结果集时，这是一种奇怪的情况(https://stackoverflow.com/a/52561721/5070879)。

---

我想是某种优化机制("公共子表达式消除"(导致exp在第一次/最后一次出现时被求值。如果你换了钥匙，它工作正常：

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<>小提琴演示

Answer 4

Lukasz的回答提供了一个使用相关子查询的可行解决方案。

对于记录，其他功能也会受到影响，例如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;

收益率：

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

而这个变通方法。。。

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;

产生正确的结果：

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

Answer 5

为了获得值，您有一个小的更改：

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

结果是C1:C2:｛"a"：1，"b"：null｝｛"a"：1｝