Comparison Expressions

Logical Operators: AND, OR, and NOT

Logical operators combine multiple expressions and define how those combinations are evaluated, enabling advanced data querying.

Oracle NoSQL Database supports AND, OR, and NOT operators.

NOT: Negates the condition it precedes.
AND: Combines conditions, both must be true.
OR: Combines conditions, at least one must be true.

For example, you can fetch the id, first name, last name, and age for persons who are not from the Beloit city, are either John or David, and have food expenses greater than 1500 using the following query:

SELECT id, firstName, lastName 
FROM Persons 
WHERE NOT Persons.address.city = "Beloit" AND firstName = "John" OR firstName = "David" AND Persons.expenses.food > 1500;

You use path expression to navigate inside hierarchically structured data and select the value of a field from records, maps, and JSON objects. In the query above, Persons.address.city refers to the city field within the address JSON object of the Persons table. For more information, see Working with complex data.

You can expect the following output:

{"id":3,"firstName":"John","lastName":"Morgan"}

The logical operators follow standard Boolean logic precedence, which determines how complex logical expressions are evaluated when multiple operators are used. The NOT operator has the highest precedence, followed by AND, and then OR.

You can use parentheses to change the order of evaluation. Expressions inside parentheses are evaluated first.

IS NULL and IS NOT NULL Operators

The IS NULL and IS NOT NULL operators test whether input expressions contain NULL values.

The IS NULL operator returns true only if the value computed by the input expression is NULL. If the result is empty, IS NULL returns false.

IS NOT NULL is equivalent to NOT (IS NULL cond_expr).

Note:

To test for NULLs, use IS NULL or IS NOT NULL operators. Comparison such as field = NULL in the NULL filtering predicate does not evaluate to TRUE, therefore, the WHERE clause does not fetch the expected rows.

Handling SQL NULLs:

A SQL NULL value indicates either the absence of a value in a field or an expression evaluates to an unknown/not applicable value.

For example, you can check the lastLogin field for NULL values as follows:

SELECT id, firstName, lastName, lastLogin FROM Persons WHERE lastLogin IS NULL;

You get the following output:

{"id":6,"firstName":"Anita","lastName":"Scotts","lastLogin":null}

Using IS NOT NULL in the same expression, returns all other rows:

SELECT id, firstName, lastName, lastLogin FROM Persons WHERE lastLogin IS NOT NULL;

{"id":2,"firstName":"John","lastName":"Anderson","lastLogin":"2016-11-28T13:01:11.2088Z"}
{"id":3,"firstName":"John","lastName":"Morgan","lastLogin":"2016-11-29T08:21:35.4971Z"}
{"id":1,"firstName":"David","lastName":"Morrison","lastLogin":"2016-10-29T18:43:59.8319Z"}
{"id":4,"firstName":"Peter","lastName":"Smith","lastLogin":"2016-10-19T09:18:05.5555Z"}
{"id":5,"firstName":"Dana","lastName":"Scully","lastLogin":"2016-11-08T09:16:46.3929Z"}

Handling JSON NULLs:

A JSON NULL indicates that the field exists in a JSON document and is explicitly set to NULL to represent no value.

Note:

The null values are mapped to JSON NULLs depending on the way the input is supplied. For example, If you supply input text as "myvalue" : null, this is mapped as a JSON NULL. Whereas, supplying "myvalue" : "null" sets the string value to the text "null".

For example, you can check the address record for NULL values in the city field as follows:

SELECT id, firstName, lastName FROM Persons p WHERE p.address.city IS NULL;

You get the following output:

{"id":6,"firstName":"Anita","lastName":"Scotts","city":null}

Using IS NOT NULL in the same expression, returns all other rows:

SELECT id, firstName, lastName FROM Persons p WHERE p.address.city IS NOT NULL;

{"id":2,"firstName":"John","lastName":"Anderson"}
{"id":1,"firstName":"David","lastName":"Morrison"}
{"id":5,"firstName":"Dana","lastName":"Scully"}
{"id":3,"firstName":"John","lastName":"Morgan"}
{"id":4,"firstName":"Peter","lastName":"Smith"}

Value Comparison Operators

The value comparison operator allows you to compare the values of two operands in queries to filter data. The operands can be column values, constants (literals), or expressions.

The result of a comparison is a Boolean value. If either operand returns NULL, the result of the comparison expression is also NULL. For more details on Boolean value computation, see the Value Comparison Operators topic in SQL Reference Guide.

The following operators are supported:

Operator	Description	Sample usage
=	Equal to	`Persons.address.state = "NJ"`
!=	Not equal to	`Persons.address.phones.areacode != 201`
>	Greater than	`age > 50`
>=	Greater than or equal to	`income >= 50000`
<	Less than	`lastLogin < "2016-10-29T18:43:59.8319"`
<=	Less than or equal to	`persons.expenses.food <= 1000`

For example, you can fetch the id, first name, and last name of all persons who are not from the city "FL" and whose last login time was after November 25, 2016.

SELECT id, firstName, lastName 
FROM Persons p 
WHERE p.address.city != "FL" AND lastLogin >= "2016-11-25T00:00:00";

You get the following output:

{"id":2,"firstName":"John","lastName":"Anderson"}
{"id":3,"firstName":"John","lastName":"Morgan"}

In the query above, you can compare the dates in ISO-8601 format as strings due to their natural sorting, without needing to cast them to timestamp data types.

Sequence Comparison Operators

The sequence comparison operator allows you to compare sequences of values. This is helpful in determining the relational order or equality between two sequence values, such as arrays or nested tables. Both input sequences are compared element by element, in order, using value comparison operators.

Sequence comparison operators use the keyword "any" along with a value comparison operator. The following operators are supported:

Operator	Description	Sample usage
=any	Equal to	`Persons.address.phones.number =any 3213267` The sequence comparison operator `=any` compares each `number` field within the `phones` array with the value 3213267 and returns the rows where at least one phone number matches the given value.
!=any	Not equal to	`Persons.address.phones.type !=any "work"` The sequence comparison operator `!any` compares each `type` field within the `phones` array with the string "work" and returns the rows where none of the phone types within a person’s address is "work".
>any	Greater than	`Persons.address.phones.areacode >any 201` The sequence comparison operator `>any` compares each `areacode` field within the `phones` array with the value 201 and returns the rows where at least one area code value is greater than 201.
>=any	Greater than or equal to	`Persons.connections >=any 5` The sequence comparison operator >=any compares each element in the `connections` array with the value 5 and returns the rows where at least one connection value is greater than or equal to 5.
<any	Less than	`Persons.connections <any 3` The sequence comparison operator `<any` compares each element in the `connections` array with the value 3 and returns the rows where at least one connection value is less than 3.
<=any	Less than or equal to	`Persons.connections <=any 1` The sequence comparison operator `<=any` compares each element in the `connections` array with the value 1 and returns the rows where at least one connection value is less than or equal to 1.

For example, you can fetch the id, first name, last name, and connections of all persons who are connected with ids 1 and 3.

SELECT id, firstName, lastName, connections 
FROM Persons p 
WHERE p.connections[] =any 1 AND p.connections[] =any 3;

You get the following output:

{"id":5,"firstName":"Dana","lastName":"Scully","connections":[2,4,1,3]}
{"id":2,"firstName":"John","lastName":"Anderson","connections":[1,3]}
{"id":6,"firstName":"Anita","lastName":"Scotts","connections":[2,4,1,3]}
{"id":4,"firstName":"Peter","lastName":"Smith","connections":[3,5,1,2]}

BETWEEN Operator

The BETWEEN operator checks if the input expression values fall between the specified lower and the higher expressions, inclusive of the boundary values. This is useful for filtering results based on a range.

The operation returns true if both expressions return true. If either expression returns false, the operation returns false. If either expression is NULL or evaluates to NULL, the result of the operation is also NULL.

For example, you can fetch the id, first name, last name, and expenses of all persons whose food expenses fall within the range of 1000 to 1500.

SELECT id, firstName, lastName, expenses 
FROM Persons p 
WHERE p.expenses.food BETWEEN 1000 and 1500;

You get the following output:

{"id":1,"firstName":"David","lastName":"Morrison","expenses":{"food":1000,"gas":180}}

IN Operator

The IN operator allows you to filter results based on whether or not the value of an expression matches any value in a specified list. This is useful in queries that check for multiple possible values, as the IN operation is equivalent to several OR-ed equality conditions.

For example, you can fetch the details of persons who are 25, 35, or 45 years old:

SELECT id, firstName, lastName, age 
FROM Persons p 
WHERE age IN (25, 35, 45);

You get the following output:

{"id":2,"firstName":"John","lastName":"Anderson","age":35}
{"id":1,"firstName":"David","lastName":"Morrison","age":25}

Regular expressions

The regular expressions function allows you to match patterns within an input string. The input string and the pattern are specified by the first and second arguments, respectively. An optional third argument provides flags that affect how the matching is performed. The regular expression function returns true if the pattern matches the input string, otherwise, it returns false. If any of the arguments returns NULL, the function returns NULL

The syntax for the pattern string is a subset of the Java Pattern class syntax. Each character in a regular expression is either a literal character, which matches itself (for example, the pattern string x matches the character 'x'), or a metacharacter, which defines a special construct with a specific meaning. For details on the supported flags and metacharacters, see the Regular Expressions topic in SQL Reference Guide.

For example, you can fetch the details of persons whose street address includes the string "hill" as follows:

SELECT id, firstName, lastName, address 
FROM Persons p 
WHERE regex_like(p.address.street,".*hill.*", "i");

In the query above, you use the regex_like function with the required pattern and the "i" flag to enable matching that is not case-sensitive. The period metacharacter (.) and the greedy quantifier (* ) match zero or more occurrences of any character in the street field.

You get the following output:

{"id":2,"firstName":"John","lastName":"Anderson","address":{"street":"187 Hill Street","city":"Beloit","state":"WI","phones":[{"type":"home","areacode":339,"number":1684972}]}}

Exists Operator

The Exists operator allows you to check for the existence of records in the sequence returned by the input expression. The Exists operator returns true if the input expression returns one or more rows, otherwise, it returns false. It returns NULL if the input expression returns NULL.

For example, you can check the table for records with area code 423 and fetch them as follows:

SELECT id, firstName, lastName, address 
FROM Persons p 
WHERE EXISTS p.address.phones[$element.areacode =423];

You get the following output:

{"id":1,"firstName":"David","lastName":"Morrison","address":{"street":"150Route2","city":"Antioch","state":"TN","phones":[{"type":"home","areacode":423,"number":8634379}]}}

For a JSON usage example, see Using WHERE EXISTS with JSON.

Is-Of-Type Operator

The is-of-type operator allows you to determine whether or not an input value or sequence matches the specified type.

It returns true in the following cases, otherwise, it returns false.

Note:

If conditions are satisfied but the input sequence contains a NULL, the result of the is-of-type operator is NULL.

The quantifier specifies how many items are expected in the input sequence. Your input must match the required number of items as defined by the quantifier in the type specification. Refer to the table Quantifiers in Is-Of-Type operator for the supported quantifiers and examples demonstrating their usage.

Note:

If you are using the is-of-type operator on an array, you must unbox the array into a sequence of array elements using [ ] in the WHERE clause. This allows is-of-type to iterate over the sequence, checking the type of each element.

Table 7-1 Quantifiers in Is-Of-Type operator

Quantifier	Allowed	Example
*(asterisk)	Zero or more items	`SELECT id, connections FROM Persons p WHERE p.connections[] IS OF TYPE (INTEGER*);` Returns the record if the connections array includes zero or more integer types. `{"id":8,"connections":[]} {"id":2,"connections":[1,3]} {"id":1,"connections":[2,3]} {"id":5,"connections":[2,4,1,3]} {"id":3,"connections":[1,4,2]} {"id":6,"connections":[2,4,1,3]} {"id":7,"connections":[2]} {"id":4,"connections":[3,5,1,2]}`
+ (plus)	One or more items	`SELECT id, connections FROM Persons p WHERE p.connections[] IS OF TYPE (INTEGER+);` Returns the record if the connections array includes one or more integer types. `{"id":5,"connections":[2,4,1,3]} {"id":2,"connections":[1,3]} {"id":1,"connections":[2,3]} {"id":4,"connections":[3,5,1,2]} {"id":3,"connections":[1,4,2]} {"id":6,"connections":[2,4,1,3]} {"id":7,"connections":[2]}`
? (question mark)	Zero or one item	`SELECT id, connections FROM persons p WHERE p.connections[] IS OF TYPE (INTEGER?)` Returns the record if the connections array includes zero or one integer types. `{"id":8,"connections":[]} {"id":7,"connections":[2]}`
No quantifier	Exactly one item is required	`SELECT id, connections FROM FROM persons p WHERE p.connections[] IS OF TYPE (INTEGER)` Returns the record if the connections array includes exactly one integer type. `{"id":7,"connections":[2]}`

Every item in your input sequence must be of the specified type or a subtype of the target type. For a sample, see Examining Data Types JSON Columns.

Add additional rows

INSERT into Persons VALUES (6, "Anita", "Scotts", 17, 4000, NULL, {"street":"157 Linden Avenue",

             "city": NULL,
             "state":"NJ",
             "phones":[{"type":"work", "areacode":201, 
                        "number":3213287},
                      {"type":"work", "areacode":201, 
                       "number":8765412},
                      {"type":"home", "areacode":339, 
                       "number":3414587}
                     ]
           },
[2, 4, 1, 3],
{"food":900, "shoes":1500, "clothes":2500}
);


INSERT into Persons VALUES (7, "Mark", "Loren", 17, 4000, "2016-11-10T09:22:46.3929", {"street":"157 Hawkins Avenue",
             "city": "Monroe Township",
             "state":"NJ",
             "phones":[{"type":"work", "areacode":201, 
                        "number":3213287},
                      {"type":"work", "areacode":201, 
                       "number":8765412},
                      {"type":"home", "areacode":339, 
                       "number":3414587}
                     ]
           },
[2],
{"food":1500, "shoes":1500, "clothes":1500}
);


INSERT into Persons VALUES (8, "Angela", "Freeman", 27, 14000, "2016-11-10T09:22:46.3929", {"street":"157 Hawkins Avenue",
             "city": "Monroe Township",
             "state":"NJ",
             "phones":[{"type":"work", "areacode":201, 
                        "number":3213287},
                      {"type":"work", "areacode":201, 
                       "number":8765412},
                      {"type":"home", "areacode":339, 
                       "number":3414587}
                     ]
           },
[],
{"food":2500, "shoes":2500, "clothes":2500}
);