JSON_EXISTS Condition

The JSON_EXISTS SQL/JSON condition checks for the existence of a particular value within JSON data. It returns a SQL VARCHAR2(7) value: TRUE if the data it targets matches one or more JSON values, or FALSE if there are no JSON values that match. If applied to a JSON null value, the condition returns TRUE.

The JSON_EXISTS condition has two required arguments, and it accepts some optional clauses.

The first argument to JSON_EXISTS is a SQL expression that returns an instance of a SQL data type that contains JSON data, which can be any of these types: BLOB, CLOB, JSON, or VARCHAR2.
The second argument to JSON_EXISTS is a SQL/JSON path expression followed by optional clauses PASSING, ON ERROR, TYPE and ON EMPTY.

You can use one or more filter expressions in the path expression to select documents based on their content. Filters enable you to test for the existence of documents that contain fields that satisfy the specified conditions. If the path expression contains a filter, then the data that matches must also satisfy the filter in order for JSON_EXISTS to return TRUE.

The filter expression may refer to SQL/JSON variables, whose values are passed from SQL by binding them with the PASSING clause. The use of SQL bind variables can improve performance by avoiding query recompilation when the value of the variables changes (see Example 3-5). The following SQL data types are supported for such variables: BINARY_DOUBLE, DATE, NUMBER, TIMESTAMP, and VARCHAR2. An error is returned if, instead of a constant, you attempt to bind a column reference.

The ON ERROR clause determines errors behavior. The error handler takes effect when any error occurs, but typically an error occurs when the given JSON data is not well-formed (using lax syntax). In case of an error, the handler can be specified to:

return FALSE (default).
return TRUE.
throw an error.

You can use JSON_EXISTS in a CASE expression or the WHERE clause of a SELECT statement.

Using Filters with JSON_EXISTS

The path expression immediately preceding a filter defines the scope of the patterns used in it. An at sign (@) within a filter refers to the data targeted by that path, which is referred as the current item for the filter.

These are examples of JSON_EXISTS with and without filters in the path expression.

Example 3-6 Path Expression Without a Filter in JSON_EXISTS

This example selects JSON documents that have an UPCCode field in the Part object of the LineItems array.

SELECT po_document FROM j_purchaseorder 
  WHERE JSON_EXISTS(po_document, '$.LineItems.Part.UPCCode');

Example 3-7 Current Item and Scope in Filters in JSON_EXISTS

This example shows three equivalent ways to select JSON documents that have an UPCCode field with a value of 85391628927 in the Part field in an element of the LineItems array.

SELECT po_document FROM j_purchaseorder 
  WHERE JSON_EXISTS(po_document, '$?(@.LineItems.Part.UPCCode == 85391628927)');

SELECT po_document FROM j_purchaseorder 
  WHERE JSON_EXISTS(po_document, '$.LineItems?(@.Part.UPCCode == 85391628927)');

SELECT po_document FROM j_purchaseorder 
  WHERE JSON_EXISTS(po_document, '$.LineItems.Part?(@.UPCCode == 85391628927)');

In the first query, the scope of the filter is the context item. The @ refers to the context item.
In the second query, the scope of the filter is the LineItems array. The @ refers to an element of the array.
In the third query, the scope of the filter is the Part field of an element in the LineItems array. The @ refers to the Part field.

Example 3-8 Filter Conditions for the Current Item in JSON_EXISTS

This example selects JSON documents that have an UPCCode field with a value of 85391628927 in the Part field in an element of the LineItems array and a Quantity field with a value greater than 3 in an element of the same array. Each filter condition applies independently to the same document. The two conditions do not need to apply to same element of the array for filter to be satisfied.

SELECT po_document FROM j_purchaseorder 
  WHERE JSON_EXISTS(po_document, 
                    '$?(@.LineItems.Part.UPCCode == 85391628927 &&
                        @.LineItems.Quantity > 3)');

Example 3-9 Downscoping in Filters in JSON_EXISTS

This example selects JSON documents that have an UPCCode field with a value of 85391628927 in the Part field in an element of the LineItems array and a Quantity field with a value greater than 3 in the same element of the same array. In contrast with Example 3-8, the current item is not the context item but an element of the LineItems array. The same element of the array must satisfy both conditions.

SELECT po_document FROM j_purchaseorder 
  WHERE JSON_EXISTS(po_document, 
                    '$.LineItems[*]?(@.Part.UPCCode == 85391628927 &&
                                     @.Quantity > 3)');

Example 3-10 Using the exists Condition in JSON_EXISTS

This example selects JSON documents that have an User field with "ABULL" as value and an UPCCode field with a value of 85391628927 in the Part field in an element of the LineItems array and a Quantity field with a value greater than 3 in the same element of the same array.

The exists condition enables one part of the filter to downscope the LineItems array and another part to scope at context-item level.

SELECT po_document FROM j_purchaseorder
  WHERE JSON_EXISTS(po_document,
                    '$?(@.User == "ABULL" && 
                        exists(@.LineItems[*]?(@.Part.UPCCode == 85391628927 && 
                                               @.Quantity > 3)))');

JSON_EXISTS as JSON_TABLE

The JSON_EXISTS condition can be viewed as a special case of the JSON_TABLE function. In particular, if you use JSON_EXISTS more than once—or use it in a combination with JSON_VALUE or JSON_QUERY (which you can express using JSON_TABLE)—to access the same data, then a single use of JSON_TABLE has the advantage of parsing the data only once.

Example 3-11 JSON_EXISTS Expressed Using JSON_TABLE

The queries in this example are equivalent. Both SELECT statements have the same effect.

SELECT po.po_document.PONumber FROM j_purchaseorder po 
  WHERE JSON_EXISTS(po_document, '$..county' ERROR ON ERROR);

SELECT po.po_document.PONumber FROM j_purchaseorder po,
  JSON_TABLE(po.po_document, '$' ERROR ON ERROR
    COLUMNS("county" NUMBER EXISTS PATH '$..county')) AS "JT" 
  WHERE jt.county = 1;

Both queries return this output, given the JSON data in inserted into the j_purchaseorder table in Example 2-2.

< 1599 >
1 row found.