JSON_EXISTS Condition

The JSON_EXISTS condition 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, or FALSE if there are no JSON values that match. If applied to a JSON null value, the condition returns TRUE.

Syntax

JSON_EXISTS (expr, JSON_basic_path_expression [JSON_passing_clause] 
[JSON_exists_on_error_clause] [JSON_exists_on_empty_clause] [TYPE {STRICT|LAX}])

expr

expr::= CHAR|NCHAR|VARCHAR2|NVARCHAR2|BLOB|CLOB|NCLOB|JSON

This is the first of two required arguments to the JSON_EXISTS condition.

The argument is a SQL expression that returns an instance of a SQL data type that contains JSON data. The SQL expression accepts as input data types that include the following: CHAR, NCHAR, VARCHAR2, NVARCHAR2, BLOB, CLOB, NCLOB, and JSON.

If expr is null, then JSON_EXISTS returns SQL NULL. If expr is not a text literal of well-formed JSON data using strict or lax syntax, then the function returns SQL NULL by default. You can use the JSON_exists_on_error_clause to override this default behavior.

JSON_basic_path_expression

This is the second of two required arguments to the JSON_EXISTS condition.

TimesTen provides SQL access to JSON data using SQL/JSON path expressions. The JSON_EXISTS condition uses the path expression to evaluate expr and to determine if a JSON value that either matches or satisfies the path expression exists. The JSON_EXISTS condition returns TRUE if at least one value matches the path expression or false if no value matches the expression. The path expression must be a text literal, but it can contain variables whose values are passed to the path expression by the JSON_passing_clause.

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 JSON_passing_clause. The use of SQL bind variables can improve performance by avoiding query recompilation when the value of the variables changes.

The syntax is the following:
JSON_basic_path_expression
json_absolute_path_expr | json_relative_path_expr
   JSON_absolute_path_expression
   $ [ json_nonfunction_steps ] [ json_function_step ]

   JSON_relative_path_expression 
   @ [ json_nonfunction_steps ] [ json_function_step ]

      JSON_nonfunction_steps
      { { json_object_step | json_array_step | json_descendent_step } [ json_filter_expr ] }

         JSON_object_step
         . { * | json_field_name }

         JSON_array_step
         [
          * |
          { { json_array_index | json_array_index to json_array_index }
            [ , { json_array_index | json_array_index to json_array_index } ]... }]

         JSON_descendent_step
         ..{json_field_name}

         JSON_filter_expr
         ? ( json_cond )

            JSON_cond
            json_disjunction | json_conjunction | json_negation | 
            ( json_cond ) | json_comparison | json_exists_cond | json_in_cond | 
            json_like_cond | json_like_regex_cond | json_eq_regex_cond | json_has_substring_cond | 
            json_starts_with_cond

         JSON_function_step 
         . json_item_method ( )

            JSON_item_method
            { abs | avg | binary | boolean | booleanOnly | ceiling | count | date | 
            double | dsInterval | float | floor | length | lower | MaxNumber | MaxString | MinNumber | 
            MinString | number | numberOnly | size | string | stringOnly | sum | timestamp | type | 
            upper | ymInterval }

For complete details about the syntax and semantics, see SQL/JSON Path Expressions and JSON_EXISTS Condition in the Oracle TimesTen In-Memory Database JSON Developer's Guide.

JSON_passing_clause

PASSING expr AS identifier [,expr AS identifier]...

Use this optional clause to pass values to the path expression. The result of evaluating expr is bound to the corresponding identifier in JSON_basic_path_expression.

JSON_exists_on_error_clause

{ERROR | TRUE | FALSE} ON ERROR

Use this optional clause to determine the behavior of errors. The error handler takes effect when any error occurs, but typically an error occurs when the given JSON data is not well-formed.

You can specify one of the following clauses or take the default:

  • ERROR ON ERROR: In case of error, return the error.

  • TRUE ON ERROR: In case of error, return TRUE.

  • FALSE ON ERROR: In case of error, return FALSE. This is the default.

JSON_exists_on_empty_clause

{ERROR | TRUE | FALSE} ON EMPTY

Use this optional clause to specify the value returned by this function if no match is found when the JSON data is evaluated using the SQL/JSON path expression.

You can specify one of the following clauses or take the default:

  • ERROR ON EMPTY: In no match is found, return the error.

  • TRUE ON EMPTY: In no match if found, return TRUE.

  • FALSE ON EMPTY: If no match is found, return FALSE. This is the default.

This clause allows you to specify a different outcome for this type of error than the outcome specified with the JSON_exists_on_error_clause. For example, if there is a no match found error, and you specify both the JSON_exists_on_empty_clause and the JSON_exists_on_error_clause, JSON_EXISTS uses the JSON_exists_on_empty_clause. Otherwise, JSON_EXISTS uses the JSON_exists_on_error_clause.

TYPE {STRICT|LAX}

Use this optional clause to determine if JSON syntax should be STRICT or LAX.

See Also