Previous Next JavaScript must be enabled to correctly display this content

## 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 Fetch from the airline baggage tracking data, the passenger name where the flight time is greater than 2019-02-01T16:00:00Z

    SELECT fullname, $flights.flightDate
    FROM BaggageInfo bag, bag.bagInfo[0].flightLegs AS $flights
    WHERE $flights.flightDate >any "2019-02-01T16:00:00Z"

Explanation: You fetch the full name and the flight dates of all passengers whose departure time is greater than the given value. Here the operand on the left hand of the ">" operator, that is,`bag.bagInfo[0].flightLegs.flightDate` is a sequence of values. If you try using the regular comparison operator instead of the sequence operator, you get an error as shown below:

    Error handling command SELECT fullname, $flights.flightDate
    FROM BaggageInfo bag, bag.bagInfo[0].flightLegs AS $flights
    WHERE $flights.flightDate > "2019-02-01T16:00:00Z": Error: at (3, 6) The left operand of comparison operator > is a sequence with more than one items. Comparison operators cannot operate on sequences of more than one items.

Instead, you must use a sequence operator `any` along with the value comparison operator ">" here.

You use only the first element of the `bagInfo` JSON array to avoid duplication of results for passengers carrying more than one bag. The departure time for the transit flights is also included in the output. Here, you can compare the string-formatted dates in ISO-8601 format due to the natural sorting order of strings without having to cast them into timestamp data types.

Output:

    {"fullname":"Michelle Payne","flightDate":["2019-02-02T12:00:00Z","2019-02-02T13:10:00Z","2019-02-02T12:10:00Z"]}
    {"fullname":"Zulema Martindale","flightDate":["2019-02-25T10:00:00Z","2019-02-25T10:23:00Z"]}
    {"fullname":"Zina Christenson","flightDate":["2019-02-04T00:00:00Z","2019-02-04T00:12:00Z"]}
    {"fullname":"Elane Lemons","flightDate":["2019-03-15T08:00:00Z","2019-03-15T08:13:00Z","2019-03-15T08:13:00Z"]}
    {"fullname":"Omar Harvey","flightDate":["2019-03-01T12:00:00Z","2019-03-01T12:20:00Z"]}
    {"fullname":"Henry Jenkins","flightDate":["2019-03-02T05:00:00Z","2019-03-02T05:24:00Z"]}
    {"fullname":"Rosalia Triplett","flightDate":["2019-02-11T01:00:00Z","2019-02-11T02:12:00Z"]}
    {"fullname":"Kendal Biddle","flightDate":["2019-03-04T06:00:00Z","2019-03-04T07:02:00Z"]}
    {"fullname":"Mary Watson","flightDate":["2019-03-13T00:00:00Z","2019-03-13T00:21:00Z"]}
    {"fullname":"Gerard Greene","flightDate":["2019-03-07T04:00:00Z","2019-03-07T05:08:00Z","2019-03-07T04:08:00Z"]}
    {"fullname":"Joanne Diaz","flightDate":["2019-02-16T06:00:00Z","2019-02-16T06:24:00Z"]}
    {"fullname":"Adelaide Willard","flightDate":["2019-02-15T01:00:00Z","2019-02-15T01:21:00Z"]}
    {"fullname":"Fallon Clements","flightDate":["2019-02-21T12:00:00Z","2019-02-21T12:19:00Z","2019-02-21T12:19:00Z"]}
    {"fullname":"Teena Colley","flightDate":["2019-02-13T07:00:00Z","2019-02-13T07:17:00Z"]}
    {"fullname":"Lorenzo Phil","flightDate":["2019-03-12T03:00:00Z","2019-03-12T10:11:00Z","2019-03-12T14:11:00Z"]}
    {"fullname":"Lucinda Beckman","flightDate":["2019-03-12T03:00:00Z","2019-03-12T04:11:00Z","2019-03-12T03:11:00Z"]}
    {"fullname":"Doris Martin","flightDate":["2019-03-22T07:00:00Z","2019-03-22T07:23:00Z","2019-03-22T07:23:00Z"]}
    {"fullname":"Raymond Griffin","flightDate":["2019-02-03T04:00:00Z","2019-02-03T04:21:00Z"]}
    {"fullname":"Lisbeth Wampler","flightDate":["2019-02-09T06:00:00Z","2019-02-09T20:01:00Z"]}
    {"fullname":"Dierdre Amador","flightDate":"2019-03-07T07:00:00Z"}

Example 6-68 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-69 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-70 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-71 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-72 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"
    }