Db2 JSON_VALUE ERROR ON EMPTY is not working as expected

Question

I'm trying to get Db2's JSON_VALUE ERROR ON EMPTY clause to work:

select json_value(json_object(key 'a' value 1), '$.b' error on empty)
from SYSIBM.DUAL

The above doesn't seem to behave as expected. I'm getting a null result:

1|
-|
 |

In Oracle, this works as expected:

select json_value(json_object(key 'a' value 1), '$.b' error on empty)
from DUAL

... producing

ORA-40462: JSON_VALUE evaluated to no value

What am I missing? I'm using Db2 LUW (DB2 v11.5.0.0).

Answer 1

It appears that Db2 defaults to NULL ON ERROR instead of ERROR ON ERROR like Oracle. This produces the expected error:

select json_value(json_object(key 'a' value 1), '$.b' error on empty error on error)
from SYSIBM.DUAL

Yielding

Result contains no SQL/JSON item.. SQLCODE=-16405, SQLSTATE=22035, DRIVER=4.7.85

According to the SQL:2016 standard, Db2 is compliant, and Oracle is not. From ISO/IEC 9075-2:2016(E) 6.27 <JSON value function>

4) If <JSON value empty behavior> is not specified, then NULL ON EMPTY is implicit.
5) If <JSON value error behavior> is not specified, then NULL ON ERROR is implicit.