SQL/JSON Condition JSON

19 SQL/JSON Condition JSON_EXISTS

SQL/JSON condition json_exists checks for the existence of a particular value within JSON data. It returns true if the data it targets matches one or more JSON values. If no JSON values are matched then it returns false.

Condition json_exists lets you use a SQL/JSON path expression as a row filter, to select rows based on the content of JSON documents. You can use json_exists in a CASE expression or the WHERE clause of a SELECT statement.

If initialization parameter compatible has value 23 or greater then you can also use json_exists in the SELECT part of a query, to obtain its Boolean result as an explicit SQL BOOLEAN value. For example, this query returns the value TRUE, indicating that field a exists:

SELECT json_exists('{a : null}', '$.a') FROM DUAL;

Error handlers ERROR ON ERROR, FALSE ON ERROR, and TRUE ON ERROR apply. The default is FALSE ON ERROR. The handler takes effect when any error occurs, but typically an error occurs when the given JSON data is not well-formed (using lax syntax). Unlike the case for conditions is json and is not json, condition json_exists expects the data it examines to be well-formed JSON data.

The second argument to json_exists is a SQL/JSON path expression followed by an optional PASSING clause and an optional error clause.

For json_exists, the following have no effect in a path-expression array step: the order of indexes and ranges, multiple occurrences of an array index, and duplication of a specified position due to range overlaps. All that counts is the set of specified positions, not how they are specified, including the order or number of times they are specified. All that is checked is the existence of a match for at least one specified position.

The optional filter expression of a SQL/JSON path expression used with json_exists can refer to SQL/JSON variables, whose values are passed from SQL by binding them with the PASSING clause. The following SQL data types are supported for such variables: VARCHAR2, NUMBER, BINARY_DOUBLE, DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE.

Tip:

For queries that you use often, use a PASSING clause to define SQL bind variables, which you use as SQL/JSON variables in path expressions. This can improve performance by avoiding query recompilation when the (variable) values change.

For example, this query passes the value of bind variable v1 as SQL/JSON variable $v1:

SELECT po.data FROM j_purchaseorder po
  WHERE json_exists(po.data,                    
                    '$.LineItems.Part?(@.UPCCode == $v1)'
                    PASSING '85391628927' AS "v1");