Oracle 12C Json - How to set missing attributes to NULL while enforcing all other ERROR ON ERROR conditions?

user_1391717 0
Viewed 1625
CREATE TABLE t (
    contents CLOB
)

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

I have a query like the following:

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,
    )

What I want to do is allow the array to set default values for missing attributes to NULL, while still enforcing all other error cases. Notably, if the value in foo or bar is greater than 4000, I want the query to fail.

However, ERROR ON ERROR will cause the query to fail if the JSON object is missing foo or bar. The error code for this is ORA-40462: JSON_VALUE evaluated to no value.

I could set NULL ON ERROR, however, this will cause any attribute greater than 4000 to be set to NULL, instead of causing the query to fail.

Is there any way to get Oracle to set missing attributes to NULL while still enforcing all other ERROR ON ERROR conditions?

I'm using Oracle 12CR1

oracle12c oracle
1 Answers

The 12CR2 will have 'ON EMPTY' clause exactly for this purpose, example:

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