Sequence Comparison Operators

Syntax

any_comparison_operator ::= "=any" | "!=any" | ">any" | ">=any" | "<any" | "<=any"

Semantics

Comparisons between two sequences is done via another set of operators: =any, !=any, >any, >=any, <any, <=any. These any operators have existential semantics: the result of an any operator on two input sequences S1 and S2 is true if and only if there is a pair of items i1 and i2, where i1 belongs to S1, i2 belongs to S2, and i1 and i2 compare true via the corresponding value comparison operator. Otherwise, if any of the input sequences contains NULL, the result is NULL. Otherwise, the result is false.

Example 6-67 Select the id, lastName and address for users who are connected with the user with id 3.

SELECT id, lastName, address FROM users
WHERE connections[] =any 3

Notice the use of [] after connections: it is an array filter step (see Array-Filter Step Expressions), which returns all the elements of the connections array as a sequence (it is unnesting the array).

Example 6-68 Select the id and lastName for users who are connected with any users having id greater than 100

SELECT id, lastName FROM users
WHERE connections[] >any 100

Example 6-69 Select the id of each user who is connected with a user having id greater than 10 and is also connected with a user having id less than 100

SELECT id FROM users u
WHERE 10 <any u.connections[] 
    AND u.connections[] <any 100

Notice that the above query is not the same as the query: select the id of each user who is connected with a user having an id in the range between 10 and 100. In the first query, you are looking for some connection with id greater than 10 and another connection (which may or may not be the same as the 1st one) with id less than 100. In the second query you are looking for some connection whose id is between 10 and 100. To make the difference clear, consider a Users table with only 2 users (say with ids 200 and 500) having the following connections arrays respectively: [ 1, 3, 110, 120 ] and [1, 50, 130]. Both of these arrays satisfy the predicates in the first query, and as a result, both users will be selected. On the other hand, the second query will not select user 200, because the array [ 1, 3, 110, 120 ] does not contain any element in the range 10 to 100.

The second query can be written by a combination of an EXISTS operator and an array filtering step:

SELECT id FROM users u
WHERE EXISTS u.connections
    [10 < $element AND $element < 100]

and the first query, with the 2 <any operators, is equivalent to the following one:

SELECT id FROM users u
WHERE EXISTS u.connections[10 < $element] 
    AND EXISTS u.connections[$element < 100]

Example 6-70 Select the first and last name of all users who have a phone number with area code 650

Notice that although we could have used [] after phones in this query, it is not necessary to do so, because the phones array (if it is indeed an array) is unnested implicitly by the .area step that follows.

SELECT firstName, lastName FROM users u
WHERE u.address.phones.area =any 650

JSON collection table:

The following example applies the sequence comparison operator on a JSON collection table. Consider a sample row from the JSON collection table created for a shopping application:

{"contactPhone":"1517113582","address":{"city":"Houston","number":651,"state":"TX","street":"Tex Ave","zip":95085},"cart":null,"firstName":"Dierdre","lastName":"Amador","orders":[{"EstDelivery":"2023-11-01","item":"handbag","orderID":"201200","priceperunit":350},{"EstDelivery":"2023-11-01","item":"Lego","orderID":"201201","priceperunit":5500}]}

Example 6-71 Fetch the details from shoppers who have purchased a handbag and the stipulated delivery is after October 31st, 2023

SELECT contactPhone, firstName
FROM storeAcct s 
WHERE s.orders[].item =any "handbag" AND s.orders[].EstDelivery>=any "2023-10-31"

Explanation: To fetch the details from shoppers who have purchased a handbag that is expected to be delivered after October 31st, you compare the item and EstDelivery fields with the required values using the sequence comparison operator any. You use the AND operator to fetch the rows that match both conditions.

Here, you can compare the EstDelivery without casting into a timestamp data type as it is a string-formatted date in ISO-8601 format and the natural sorting order of strings applies.

Output:

{
  "contactPhone" : "1517113582",
  "firstName" : "Dierdre"
}