Sequence Comparison Operators

Comparisons between two sequences are done via a set of operators: =any, !=any, >any, >=any, <any, <=any. The result of 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 1: Find passenger name and tag number for all bags where the estimated arrival time is greater than 2019-03-01T13:00:00Z.

SELECT fullname, bag.bagInfo[].tagNum,
bag.bagInfo[].flightLegs[].estimatedArrival
FROM BaggageInfo bag 
WHERE bag.bagInfo[].flightLegs[].estimatedArrival >any "2019-03-01T13:00:00Z"

Explanation: You fetch the full name, and tag number of all passenger bags whose estimated arrival time is greater than the given value. Here the operand on the left hand of the ">" operator (bag.bagInfo[].flightLegs[].estimatedArrival) 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. That is the reason you need a sequence operator here.

SELECT fullname, bag.bagInfo[].tagNum,
bag.bagInfo[].flightLegs[].estimatedArrival 
FROM BaggageInfo bag
WHERE bag.bagInfo[].flightLegs[].estimatedArrival > "2019-03-01T13:00:00Z"

Output showing error:

Error handling command SELECT fullname, bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].estimatedArrival
FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].estimatedArrival > "2019-03-01T13:00:00Z":
Error: at (1, 107) 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.

Output (after using sequence operator):

{"fullname":"Lucinda Beckman","tagNum":"17657806240001","estimatedArrival":["2019-03-12T16:00:00Z","2019-03-13T03:14:00Z","2019-03-12T15:12:00Z"]}
{"fullname":"Elane Lemons","tagNum":"1765780623244","estimatedArrival":["2019-03-15T09:00:00Z","2019-03-15T10:14:00Z","2019-03-15T10:14:00Z"]}
{"fullname":"Dierdre Amador","tagNum":"17657806240229","estimatedArrival":"2019-03-07T14:00:00Z"}
{"fullname":"Henry Jenkins","tagNum":"17657806216554","estimatedArrival":["2019-03-02T09:00:00Z","2019-03-02T13:24:00Z"]}
{"fullname":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"],"estimatedArrival":["2019-03-12T16:00:00Z","2019-03-13T03:14:00Z",
"2019-03-12T15:12:00Z","2019-03-12T16:40:00Z","2019-03-13T03:18:00Z","2019-03-12T15:12:00Z"]}
{"fullname":"Gerard Greene","tagNum":"1765780626568","estimatedArrival":["2019-03-07T17:00:00Z","2019-03-08T04:10:00Z","2019-03-07T16:10:00Z"]}
{"fullname":"Doris Martin","tagNum":"17657806232501","estimatedArrival":["2019-03-22T09:00:00Z","2019-03-21T23:24:00Z","2019-03-22T10:24:00Z"]}
{"fullname":"Omar Harvey","tagNum":"17657806234185","estimatedArrival":["2019-03-02T02:00:00Z","2019-03-02T16:21:00Z"]}
{"fullname":"Mary Watson","tagNum":"17657806299833","estimatedArrival":["2019-03-13T15:00:00Z","2019-03-14T06:22:00Z"]}
{"fullname":"Kendal Biddle","tagNum":"17657806296887","estimatedArrival":["2019-03-04T22:00:00Z","2019-03-05T12:02:00Z"]}

Example 2: Find the tag number of passengers who fly from JFK/through JFK to any other location.

SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc 
FROM BaggageInfo bag
WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "JFK"

Explanation: You fetch the tag number of passengers whose flight source is JFK or the passengers who travel through JFK. The destination can be anything.

Output:

{"tagNum":"17657806240229","fltRouteSrc":"JFK"}
{"tagNum":"17657806215913","fltRouteSrc":["JFK","IST"]}
{"tagNum":"17657806296887","fltRouteSrc":["JFK","IST"]}