18 Clauses Used in SQL Functions and Conditions for JSON

Clauses PASSING, RETURNING, wrapper, error, empty-field, on-mismatch and TYPE are described for SQL functions that use JSON data. Each clause is used in one or more of the SQL functions and conditions is json, is not json, json_array, json_arrayagg, json_equal, json_exists, json_mergepatch, json_query, json_object, json_objectagg, json_serialize, json_table, json_transform, and json_value.

• PASSING Clause for SQL Functions and Conditions
  Oracle SQL function json_transform, SQL/JSON functions json_value and json_query, and SQL/JSON condition json_exists accept an optional PASSING clause, which binds SQL values to SQL/JSON variables for use in path expressions.
• RETURNING Clause for SQL Functions
  SQL functions json_array, json_arrayagg, json_mergepatch, json_object, json_objectagg, json_query, json_serialize, json_transform, and json_value accept an optional RETURNING clause, which specifies the data type of the value returned by the function. This clause and the default behavior (no RETURNING clause) are described here.
• Wrapper Clause for SQL/JSON Query Functions JSON_QUERY and JSON_TABLE
  SQL/JSON query functions json_query and json_table accept an optional wrapper clause, which specifies the form of the value returned by json_query or used for the data in a json_table column. This clause and the default behavior (no wrapper clause) are described here. Examples are provided.
• Error Clause for SQL Functions and Conditions
  Some SQL query functions and conditions for JSON data accept an optional error clause, which specifies handling for a runtime error that is raised by the function or condition. This clause and the default behavior (no error clause) are summarized here.
• 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.
• ON MISMATCH Clause for SQL/JSON Query Functions
  You can use an ON MISMATCH clause with SQL/JSON functions json_value, json_query, and json_table, to handle type-matching exceptions. It specifies handling to use when a targeted JSON value does not match the specified SQL return value. This clause and its default behavior (no ON MISMATCH clause) are described here.
• TYPE Clause for SQL Functions and Conditions
  SQL condition json_exists and functions json_transform, json_value, json_query, and json_table each accept an optional TYPE clause, which specifies whether JSON values are compared strictly with respect to JSON-language type, that is, as if the relevant "only" data-type conversion item methods were applied to the data being compared.