18.5 Empty-Field Clause for SQL/JSON Query Functions
SQL/JSON query functions json_value, json_query, and json_table accept an optional ON EMPTY clause, which specifies the handling to use when a targeted JSON field is absent from the data queried. This clause and the default behavior (no ON EMPTY clause) are described here.
You generally handle errors for SQL/JSON functions and conditions using an error clause (ON ERROR). However, there is a special case where you might want different handling from this general error handling: when querying to match given JSON fields that are missing from the data. Sometimes you do not want to raise an error just because a field to be matched is absent. (A missing field is normally treated as an error.)
You typically use a NULL ON EMPTY clause in conjunction with an accompanying ON ERROR clause. This combination specifies that other errors are handled according to the ON ERROR clause, but the error of trying to match a missing field is handled by just returning NULL. If no ON EMPTY clause is present then an ON ERROR clause handles also the missing-field case.
In addition to NULL ON EMPTY there are ERROR ON EMPTY and DEFAULT ... ON EMPTY, which are analogous to the similarly named ON ERROR clauses.
If only an ON EMPTY clause is present (no ON ERROR clause) then missing-field behavior is specified by the ON EMPTY clause, and other errors are handled the same as if NULL ON ERROR were present (it is the ON ERROR default). If both clauses are absent then only NULL ON ERROR is used.
Note:
When SQL/JSON function json_value is used in PL/SQL code
with a RETURNING type that is a record type or an
index-table type, a NULL value cannot be returned,
because values of these types cannot be atomically NULL.
For this reason, clauses NULL ON MISMATCH and
NULL ON EMPTY cannot return a NULL value for
these collection types. Instead of returning NULL, a compile-time
error is raised. (There is no such exception for PL/SQL code with a
RETURNING type for SQL objects, varrays, or nested tables,
because values of these types can be atomically NULL.)
Use NULL ON EMPTY for an Index Created on JSON_VALUE
NULL ON EMPTY is especially useful for the case of a functional index created on a json_value expression. The clause has no effect on whether or when the index is picked up, but it is effective in allowing some data to be indexed that would otherwise not be because it is missing a field targeted by the json_value expression.
You generally want to use ERROR ON ERROR for the queries that populate the index, so that a query path expression that results in multiple values or complex values raises an error. But you sometimes do not want to raise an error just because the field targeted by a path expression is missing — you want that data to be indexed. Example 30-4 illustrates this use of NULL ON EMPTY when creating an index on a json_value expression.