Oracle 12C Json - 如何将缺失的属性设置为 NULL，同时强制执行所有其他 ERROR ON ERROR 条件？

Question

CREATE TABLE t (
    contents CLOB
)

INSERT INTO t (contents) VALUES ('[{"foo": "a"}, {"foo": "a", "bar": "b"}]');

我有一个如下查询:

SELECT foo, 
       bar
FROM t,
    json_table(contents, '$[*]' COLUMNS (
        foo VARCHAR2(4000) PATH '$.foo' ERROR ON ERROR,
        bar VARCHAR2(4000) PATH '$.bar' ERROR ON ERROR,
    )

我想要做的是允许数组将缺失属性的默认值设置为NULL，同时仍然强制执行所有其他错误情况。值得注意的是，如果 foo 或 bar 中的值大于 4000，我希望查询失败。

但是，如果 JSON 对象缺少 foo 或 bar，ERROR ON ERROR 将导致查询失败。错误代码为ORA-40462:JSON_VALUE评估为无值。

我可以设置NULL ON ERROR，但是，这将导致任何大于 4000 的属性被设置为 NULL，而不是导致查询失败。

是否有任何方法可以让 Oracle 将缺失的属性设置为 NULL，同时仍强制执行所有其他 ERROR ON ERROR 条件？

我使用的是 Oracle 12CR1

Answer 1

12CR2 将具有专门用于此目的的“ON EMPTY”子句，例如:

foo VARCHAR2(4000) PATH '$.foo' ERROR ON ERROR NULL ON EMPTY,