Using Operators in SQL

If you want to follow along with the examples, see Sample data to run queries to view a sample data and learn how to use OCI console to create the example tables and load data using JSON files.

Related Topics

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"]}

Logical operators

The operators AND and OR are binary and the NOT operator is unary. The operands of the logical operators are conditional expressions, which must have a type BOOLEAN. An empty result from an operand is treated as a false value. If an operand returns NULL( either SQL NULL or JSON NULL), then:
  • The AND operator returns false if the other operand returns false; otherwise, it returns NULL.
  • The OR operator returns true if the other operand returns true; otherwise, it returns NULL.
  • The NOT operator returns NULL.
Example 1: Select the details of the passenger and their bags for a trip with ticket number 1762311547917 or confirmation number KN4D1L.
SELECT fullName,bag.ticketNo, bag.confNo, 
bag.bagInfo[].tagNum, bag.bagInfo[].routing
FROM BaggageInfo bag 
WHERE bag.ticketNo=1762311547917 OR bag.confNo="KN4D1L"

Explanation: You fetch the details of passengers satisfying one of the two filter criteria. You do this with the OR clause. You fetch the full name, tag number, ticket number, reservation code, and routing details of passengers satisfying a particular ticket number or a particular reservation code (confNo).

Output:
{"fullName":"Rosalia Triplett","ticketNo":1762311547917,"confNo":"FH7G1W","tagNum":"17657806215913","routing":"JFK/IST/VIE"}
{"fullName":"Mary Watson","ticketNo":1762340683564,"confNo":"KN4D1L","tagNum":"17657806299833","routing":"YYZ/HKG/BLR"}
Example 2: Select baggage details of passengers traveling between MIA and MEL.
SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing 
FROM BaggageInfo bag
WHERE bag.bagInfo[].flightLegs[].fltRouteSrc =any "MIA" AND
bag.bagInfo[].flightLegs[].fltRouteDest=any "MEL"

Explanation: You fetch the details of the passengers traveling between MIA and MEL. Since you need to match 2 conditions here, the flight source and the flight destination, you are using an AND operator. Here the flight source could be the starting point of the flight or any transit airport. Similarly, the flight destination could be a transit airport or a final destination.

Output: 
{"fullName":"Zulema Martindale","tagNum":"17657806288937","routing":"MIA/LAX/MEL"}
{"fullName":"Adam Phillips","tagNum":"17657806255240","routing":"MIA/LAX/MEL"}
{"fullName":"Joanne Diaz","tagNum":"17657806292518","routing":"MIA/LAX/MEL"}
{"fullName":"Zina Christenson","tagNum":"17657806228676","routing":"MIA/LAX/MEL"}
Example 3: Select details of those bags which does not originate from MIA/pass through MIA.
SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing,
bag.bagInfo[].flightLegs[].fltRouteSrc
FROM BaggageInfo bag 
WHERE NOT bag.bagInfo[].flightLegs[].fltRouteSrc=any "MIA"

Explanation: You fetch the details of passengers not originating from a particular source. To fetch these details, you are using the NOT operator here. You want to fetch details of bags which did not start/go through MIA.

Output: 
{"fullName":"Kendal Biddle","tagNum":"17657806296887","routing":"JFK/IST/VIE","fltRouteSrc":"JFK"}
{"fullName":"Lucinda Beckman","tagNum":"17657806240001","routing":"SFO/IST/ATH/JTR","fltRouteSrc":"SFO"}
{"fullName":"Adelaide Willard","tagNum":"17657806224224","routing":"GRU/ORD/SEA","fltRouteSrc":"GRU"}
{"fullName":"Raymond Griffin","tagNum":"17657806243578","routing":"MSQ/FRA/HKG","fltRouteSrc":"MSQ"}
{"fullName":"Elane Lemons","tagNum":"1765780623244","routing":"MXP/CDG/SLC/BZN","fltRouteSrc":"MXP"}
{"fullName":"Dierdre Amador","tagNum":"17657806240229","routing":"JFK/MAD","fltRouteSrc":"JFK"}
{"fullName":"Henry Jenkins","tagNum":"17657806216554","routing":"SFO/ORD/FRA","fltRouteSrc":"SFO"}
{"fullName":"Rosalia Triplett","tagNum":"17657806215913","routing":"JFK/IST/VIE","fltRouteSrc":"JFK"}
{"fullName":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"],"routing":["SFO/IST/ATH/JTR","SFO/IST/ATH/JTR"],"fltRouteSrc":["SFO","SFO"]}
{"fullName":"Gerard Greene","tagNum":"1765780626568","routing":"SFO/IST/ATH/JTR","fltRouteSrc":"SFO"}
{"fullName":"Doris Martin","tagNum":"17657806232501","routing":"BZN/SEA/CDG/MXP","fltRouteSrc":"BZN"}
{"fullName":"Omar Harvey","tagNum":"17657806234185","routing":"MEL/LAX/MIA","fltRouteSrc":"MEL"}
{"fullName":"Fallon Clements","tagNum":"17657806255507","routing":"MXP/CDG/SLC/BZN","fltRouteSrc":"MXP"}
{"fullName":"Lisbeth Wampler","tagNum":"17657806292229","routing":"LAX/TPE/SGN","fltRouteSrc":"LAX"}
{"fullName":"Teena Colley","tagNum":"17657806255823","routing":"MSQ/FRA/HKG","fltRouteSrc":"MSQ"}
{"fullName":"Michelle Payne","tagNum":"17657806247861","routing":"SFO/IST/ATH/JTR","fltRouteSrc":"SFO"}
{"fullName":"Mary Watson","tagNum":"17657806299833","routing":"YYZ/HKG/BLR","fltRouteSrc":"YYZ"}

NULL operators

The IS NULL operator tests whether the result of its input expression(either SQL expression or JSON object) is NULL. If the input expression returns more than one item, an error is raised. If the result of the input expression is empty, IS NULL returns false. Otherwise, IS NULL returns true if and only if the single item computed by the input expression is NULL. The IS NOT NULL operator is equivalent to NOT (IS NULL cond_expr).

Example 1: Fetch ticket number of passengers whose baggage details are available and is NOT NULL.
SELECT ticketNo,fullname FROM BaggageInfo bagdet 
WHERE bagdet.bagInfo is NOT NULL
Explanation: You fetch the details of passengers who have baggage, which means bagInfo JSON is not null.
Output:
{"ticketNo":1762357254392,"fullname":"Teena Colley"}
{"ticketNo":1762330498104,"fullname":"Michelle Payne"}
{"ticketNo":1762340683564,"fullname":"Mary Watson"}
{"ticketNo":1762377974281,"fullname":"Kendal Biddle"}
{"ticketNo":1762320569757,"fullname":"Lucinda Beckman"}
{"ticketNo":1762392135540,"fullname":"Adelaide Willard"}
{"ticketNo":1762399766476,"fullname":"Raymond Griffin"}
{"ticketNo":1762324912391,"fullname":"Elane Lemons"}
{"ticketNo":1762390789239,"fullname":"Zina Christenson"}
{"ticketNo":1762340579411,"fullname":"Zulema Martindale"}
{"ticketNo":1762376407826,"fullname":"Dierdre Amador"}
{"ticketNo":176234463813,"fullname":"Henry Jenkins"}
{"ticketNo":1762311547917,"fullname":"Rosalia Triplett"}
{"ticketNo":1762320369957,"fullname":"Lorenzo Phil"}
{"ticketNo":1762341772625,"fullname":"Gerard Greene"}
{"ticketNo":1762344493810,"fullname":"Adam Phillips"}
{"ticketNo":1762355527825,"fullname":"Doris Martin"}
{"ticketNo":1762383911861,"fullname":"Joanne Diaz"}
{"ticketNo":1762348904343,"fullname":"Omar Harvey"}
{"ticketNo":1762350390409,"fullname":"Fallon Clements"}
{"ticketNo":1762355854464,"fullname":"Lisbeth Wampler"}
Example 2: Fetch ticket number of passengers whose baggage details are not available or IS NULL
SELECT ticketNo, fullname FROM BaggageInfo bagdet 
WHERE bagdet.bagInfo is NULL
0 row returned

Value Comparison Operators

Value comparison operators are primarily used to compare 2 values, one produced by the left operand and another from the right operand. If any operand returns more than one item, an error is raised. If both operands return the empty sequence, the operands are considered equal (true will be returned if the operator is =, <=, or >=). If only one of the operands returns empty, the result of the comparison is false unless the operator is !=. If an operand returns NULL, the result of the comparison expression is also NULL. Otherwise, the result is a boolean value.

Example 1: Select the full name and routing of all male passengers.
SELECT fullname, bag.bagInfo[].routing 
FROM BaggageInfo bag 
WHERE gender="M"

Explanation: Here the data is filtered based on gender. The value comparison operator "=" is used to filter the data.

Output:
{"fullname":"Lucinda Beckman","routing":"SFO/IST/ATH/JTR"}
{"fullname":"Adelaide Willard","routing":"GRU/ORD/SEA"}
{"fullname":"Raymond Griffin","routing":"MSQ/FRA/HKG"}
{"fullname":"Zina Christenson","routing":"MIA/LAX/MEL"}
{"fullname":"Dierdre Amador","routing":"JFK/MAD"}
{"fullname":"Birgit Naquin","routing":"JFK/MAD"}
{"fullname":"Lorenzo Phil","routing":["SFO/IST/ATH/JTR","SFO/IST/ATH/JTR"]}
{"fullname":"Gerard Greene","routing":"SFO/IST/ATH/JTR"}
{"fullname":"Adam Phillips","routing":"MIA/LAX/MEL"}
{"fullname":"Fallon Clements","routing":"MXP/CDG/SLC/BZN"}
{"fullname":"Lisbeth Wampler","routing":"LAX/TPE/SGN"}
{"fullname":"Teena Colley","routing":"MSQ/FRA/HKG"}
You can rewrite this query with a "!=" comparison operator. To get the details of all male passengers, your query can filter data where gender is not "F". This is valid only with the assumption that there can only be two values in the column gender which is "F" and "M".
SELECT fullname,bag.bagInfo[].routing 
FROM BaggageInfo bag 
WHERE gender!="F";
Example 2: Fetch the passenger name and routing details of passengers with ticket numbers greater than 1762360000000.
SELECT fullname, ticketNo, 
bag.bagInfo[].tagNum,bag.bagInfo[].routing
FROM BaggageInfo bag 
WHERE ticketNo > 1762360000000

Explanation: You need the details of passengers whose ticket number is greater than the given value. You use the ">" operator to filter the data.

Output:
{"fullname":"Adelaide Willard","ticketNo":1762392135540,"tagNum":"17657806224224","routing":"GRU/ORD/SEA"}
{"fullname":"Raymond Griffin","ticketNo":1762399766476,"tagNum":17657806243578,"routing":"MSQ/FRA/HKG"}
{"fullname":"Zina Christenson","ticketNo":1762390789239,"tagNum":"17657806228676","routing":"MIA/LAX/MEL"}
{"fullname":"Bonnie Williams","ticketNo":1762397286805,"tagNum":"17657806216554","routing":"SFO/ORD/FRA"}
{"fullname":"Joanne Diaz","ticketNo":1762383911861,"tagNum":"17657806292518","routing":"MIA/LAX/MEL"}
{"fullname":"Kendal Biddle","ticketNo":1762377974281,"tagNum":"17657806296887","routing":"JFK/IST/VIE"}
{"fullname":"Dierdre Amador","ticketNo":1762376407826,"tagNum":"17657806240229","routing":"JFK/MAD"}
{"fullname":"Birgit Naquin","ticketNo":1762392196147,"tagNum":"17657806240229","routing":"JFK/MAD"}
Example 3: Select all bag tag numbers originating from SFO/transit through SFO.
SELECT bag.bagInfo[].tagNum,
bag.bagInfo[].flightLegs[].fltRouteSrc 
FROM BaggageInfo bag
WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"
Explanation: You fetch the tag number of bags that either originate from SFO or pass through SFO. Though you are using the value comparison operator =, since the flightLegs is an array, the left operand of comparison operator = is a sequence with more than one item. That is the reason to use the sequence operator any in addition to the value comparison operator =. Else you get the following error.
Error handling command SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc 
FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].fltRouteSrc= "SFO": 
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.
Output: 
{"tagNum":"17657806240001","fltRouteSrc":"SFO"}
{"tagNum":"17657806216554","fltRouteSrc":"SFO"}
{"tagNum":["17657806240001","17657806340001"],"fltRouteSrc":["SFO","SFO"]}
{"tagNum":"1765780626568","fltRouteSrc":"SFO"}
{"tagNum":"17657806247861","fltRouteSrc":"SFO"}
Example 4: Select all bag tag numbers which did not originate from JFK.
SELECT bag.bagInfo[].tagNum,
bag.bagInfo[].flightLegs[0].fltRouteSrc 
FROM BaggageInfo bag
WHERE bag.bagInfo.flightLegs[0].fltRouteSrc!=ANY "JFK"
Explanation: The assumption here is that the first record of the flightLegs array has the details of the source location. You fetch the tag number of bags that did not originate from JFK and so using a != operator here. Though you are using the value comparison operator !=, since the flightLegs is an array, the left operand of the comparison operator != is a sequence with more than one item. That is the reason to use the sequence operator any in addition to the value comparison operator !=. Else you get the following error.
Error handling command SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[0].fltRouteSrc 
FROM BaggageInfo bag WHERE bag.bagInfo.flightLegs[0].fltRouteSrc!="JFK": 
Failed to display result set: Error: at (2, 0) 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:
{"tagNum":"17657806240001","fltRouteSrc":["SFO","IST","ATH"]}
{"tagNum":"17657806224224","fltRouteSrc":["GRU","ORD"]}
{"tagNum":"17657806243578","fltRouteSrc":["MSQ","FRA"]}
{"tagNum":"1765780623244","fltRouteSrc":["MXP","CDG","SLC"]}
{"tagNum":"17657806228676","fltRouteSrc":["MIA","LAX"]}
{"tagNum":"17657806234185","fltRouteSrc":["MEL","LAX"]}
{"tagNum":"17657806255507","fltRouteSrc":["MXP","CDG","SLC"]}
{"tagNum":"17657806292229","fltRouteSrc":["LAX","TPE"]}
{"tagNum":"17657806255823","fltRouteSrc":["MSQ","FRA"]}
{"tagNum":"17657806247861","fltRouteSrc":["SFO","IST","ATH"]}
{"tagNum":"17657806299833","fltRouteSrc":["YYZ","HKG"]}
{"tagNum":"17657806288937","fltRouteSrc":["MIA","LAX"]}
{"tagNum":"17657806216554","fltRouteSrc":["SFO","ORD"]}
{"tagNum":["17657806240001","17657806340001"],"fltRouteSrc":["SFO","IST","ATH","SFO","IST","ATH"]}
{"tagNum":"1765780626568","fltRouteSrc":["SFO","IST","ATH"]}
{"tagNum":"17657806255240","fltRouteSrc":["MIA","LAX"]}
{"tagNum":"17657806232501","fltRouteSrc":["BZN","SEA","CDG"]}
{"tagNum":"17657806292518","fltRouteSrc":["MIA","LAX"]}

IN Operator

The IN operator is essentially a compact alternative to a number of OR-ed equality conditions. This operator allows you to specify multiple values in a WHERE clause.

Example: Fetch tag number for the customers "Lucinda Beckman", "Adam Phillips","Zina Christenson","Fallon Clements".
SELECT bagdet.fullName, bagdet.bagInfo[].tagNum 
FROM BaggageInfo bagdet 
WHERE bagdet.fullName IN 
("Lucinda Beckman", "Adam Phillips","Zina Christenson","Fallon Clements")

Explanation: You fetch the tag numbers of a list of passengers. The list of passengers to be fetched can be given inside an IN clause.

Output:
{"fullName":"Lucinda Beckman","tagNum":"17657806240001"}
{"fullName":"Zina Christenson","tagNum":"17657806228676"}
{"fullName":"Adam Phillips","tagNum":"17657806255240"}
{"fullName":"Fallon Clements","tagNum":"17657806255507"}

Regular Expression Conditions

A regular expression is a pattern that the regular expression engine attempts to match with an input string. The regex_like function performs regular expression matching. The regex_like function provides functionality similar to the LIKE operator in standard SQL, that is, it can be used to check if an input string matches a given pattern. The input string and the pattern are computed by the first and second arguments, respectively. A third, optional, argument specifies a set of flags that affect how the matching is done.

The pattern string is the regular expression against which the input text is matched. The period (.) is a meta-character that matches every character except a new line. The greedy quantifier (*) is a meta-character that indicates zero or more occurrences of the preceding element. For example, the regex "D.*" matches any string that starts with the character 'D' and is followed by zero or more characters.

Example 1: Fetch baggage information of passengers whose names start with 'Z'.
SELECT bag.fullname,bag.bagInfo[].tagNum 
FROM BaggageInfo bag
WHERE regex_like(fullName, "Z.*")

Explanation: You fetch the full name and tag numbers of passengers whose full name starts with Z. You use a regular expression and specify that the first character in the full name should be "Z" and the rest can be anything else.

Output:
{"fullname":"Zina Christenson","tagNum":"17657806228676"}
{"fullname":"Zulema Martindale","tagNum":"17657806288937"}

Example 2: Fetch baggage information of passengers whose flight source location has an "M" in it.

Option 1:
SELECT bag.fullname,bag.bagInfo[].tagNum, 
bag.bagInfo[].flightLegs[0].fltRouteSrc 
FROM BaggageInfo bag 
WHERE regex_like(bag.bagInfo.flightLegs[0].fltRouteSrc, ".*M.*")

Explanation: The assumption here is that the first record of the flightLegs array has the details of the source location. You fetch the full name and tag numbers of passengers whose flight source has an "M" in it. You use a regular expression and specify that one of the characters in the source field should be "M" and the rest can be anything else.

You can also use different approaches to write queries to solve the above problem.

Option 2: Instead of hard coding the index of the flightLegs array, you use the regex_like function to determine the correct index.
SELECT bag.fullname,bag.bagInfo[].tagNum, 
bag.bagInfo[].flightLegs[].fltRouteSrc 
FROM BaggageInfo bag
WHERE EXISTS (bag.bagInfo.flightLegs[regex_like($element.fltRouteSrc, ".*M.*")])
Option 3: You use the substring of the "routing" field to extract the source and then use regex_like function to search the letter M in the source.
SELECT bag.fullname,bag.bagInfo[].tagNum,
substring(bag.bagInfo[].routing,0,3) 
FROM BaggageInfo bag WHERE
regex_like(substring(bag.bagInfo[].routing,0,3), ".*M.*")
Output: 
{"fullname":"Raymond Griffin","tagNum":"17657806243578","fltRouteSrc":"MSQ"}
{"fullname":"Elane Lemons","tagNum":"1765780623244","fltRouteSrc":"MXP"}
{"fullname":"Zina Christenson","tagNum":"17657806228676","fltRouteSrc":"MIA"}
{"fullname":"Zulema Martindale","tagNum":"17657806288937","fltRouteSrc":"MIA"}
{"fullname":"Adam Phillips","tagNum":"17657806255240","fltRouteSrc":"MIA"}
{"fullname":"Joanne Diaz","tagNum":"17657806292518","fltRouteSrc":"MIA"}
{"fullname":"Teena Colley","tagNum":"17657806255823","fltRouteSrc":"MSQ"}
{"fullname":"Omar Harvey","tagNum":"17657806234185","fltRouteSrc":"MEL"}
{"fullname":"Fallon Clements","tagNum":"17657806255507","fltRouteSrc":"MXP"}

EXISTS Operator

The EXISTS operator checks whether the sequence returned by its input expression is empty or not, and returns false or true, respectively. A special case is when the input expression returns NULL. In this case, EXISTS will also return NULL.

Example 1: Select passenger details and baggage information for those passengers who have three flight segments.
SELECT fullName, bag.bagInfo[].tagNum, 
bag.bagInfo[].routing
FROM BaggageInfo bag 
WHERE EXISTS bag.bagInfo[].flightLegs[2]

Explanation: You fetch the details of the passengers who have three flight segments. You determine this by evaluating if the third element of the flight legs array is present using the EXISTS operator.

Output:
{"fullName":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"],"routing":["SFO/IST/ATH/JTR","SFO/IST/ATH/JTR"]}
{"fullName":"Gerard Greene","tagNum":"1765780626568","routing":"SFO/IST/ATH/JTR"}
{"fullName":"Doris Martin","tagNum":"17657806232501","routing":"BZN/SEA/CDG/MXP"}
{"fullName":"Fallon Clements","tagNum":"17657806255507","routing":"MXP/CDG/SLC/BZN"}
{"fullName":"Michelle Payne","tagNum":"17657806247861","routing":"SFO/IST/ATH/JTR"}
{"fullName":"Lucinda Beckman","tagNum":"17657806240001","routing":"SFO/IST/ATH/JTR"}
{"fullName":"Elane Lemons","tagNum":"1765780623244","routing":"MXP/CDG/SLC/BZN"}
Example 2: Fetch the full name and tag number for all customer baggage shipped after 2019. 
SELECT fullName, bag.ticketNo 
FROM BaggageInfo bag WHERE
EXISTS bag.bagInfo[$element.bagArrivalDate >="2019-01-01T00:00:00"]

Explanation: The bag arrival date value for every bag should be greater than the year 2019. Here the "$element" is bound to the context row ( every bag of the customer). The EXISTS operator checks whether the sequence returned by its input expression is empty or not. The sequence returned by the comparison operator ">=" is non-empty for all bags which arrived after 2019.

Output: 
{"fullName":"Lucinda Beckman","ticketNo":1762320569757}
{"fullName":"Adelaide Willard","ticketNo":1762392135540}
{"fullName":"Raymond Griffin","ticketNo":1762399766476}
{"fullName":"Elane Lemons","ticketNo":1762324912391}
{"fullName":"Zina Christenson","ticketNo":1762390789239}
{"fullName":"Zulema Martindale","ticketNo":1762340579411}
{"fullName":"Dierdre Amador","ticketNo":1762376407826}
{"fullName":"Henry Jenkins","ticketNo":176234463813}
{"fullName":"Rosalia Triplett","ticketNo":1762311547917}
{"fullName":"Lorenzo Phil","ticketNo":1762320369957}
{"fullName":"Gerard Greene","ticketNo":1762341772625}
{"fullName":"Adam Phillips","ticketNo":1762344493810}
{"fullName":"Doris Martin","ticketNo":1762355527825}
{"fullName":"Joanne Diaz","ticketNo":1762383911861}
{"fullName":"Omar Harvey","ticketNo":1762348904343}
{"fullName":"Fallon Clements","ticketNo":1762350390409}
{"fullName":"Lisbeth Wampler","ticketNo":1762355854464}
{"fullName":"Teena Colley","ticketNo":1762357254392}
{"fullName":"Michelle Payne","ticketNo":1762330498104}
{"fullName":"Mary Watson","ticketNo":1762340683564}
{"fullName":"Kendal Biddle","ticketNo":1762377974281}

Is-Of-Type Operator

The is-of-type operator checks the sequence type of its input sequence against one or more target sequence types. If the number N of the target types is greater than one, the expression is equivalent to OR-ing N is-of-type expressions, each having one target type.

Example: Fetch the names of the passengers whose baggage tags contain only numbers and not a STRING.
SELECT fullname,bag.bagInfo.tagNum 
FROM  BaggageInfo bag 
WHERE bag.bagInfo.tagNum is of type (NUMBER)

Explanation: The tagNum in the bagInfo schema is a STRING data type. But the application could take in a NUMBER value as tagNum by mistake. The query captures the passengers for whom the tagNum column has only numbers.

Output:
{"fullname":"Raymond Griffin","tagNum":17657806243578}
If you query the bagInfo schema for the above tagNum as STRING, no rows are displayed.
SELECT * FROM BaggageInfo bag WHERE tagnum = "17657806232501"
0 row returned
You can also fetch the names of the passengers whose baggage tags contain only STRING.
SELECT fullname,bag.bagInfo.tagNum 
FROM BaggageInfo bag 
WHERE  bag.bagInfo.tagNum is of type (STRING)

SQL Operators examples using QueryRequest API

You can use QueryRequest API and filter data from a NoSQL table using SQL operators.

To execute your query, you use the NoSQLHandle.query() API.

Download the full code SQLOperators.java from the examples here.
 //Fetch rows from the table
private static void fetchRows(NoSQLHandle handle,String sqlstmt) throws Exception {
   try (
      QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
      QueryIterableResult results = handle.queryIterable(queryRequest)){
      for (MapValue res : results) {
         System.out.println("\t" + res);
      }
   }
} 

String seq_comp_ope="SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any \"SFO\"";
System.out.println("Using Sequence Comparison operator ");
fetchRows(handle,seq_comp_ope);
String logical_ope="SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing,bag.bagInfo[].flightLegs[].fltRouteSrc FROM BaggageInfo bag WHERE NOT bag.bagInfo[].flightLegs[].fltRouteSrc=any \"SFO\"";
System.out.println("Using Logical operator ");
fetchRows(handle,logical_ope);
String value_comp_ope="SELECT fullname, bag.bagInfo[].routing FROM BaggageInfo bag WHERE gender=\"M\"";
System.out.println("Using Value Comparison operator ");
fetchRows(handle,value_comp_ope);
String in_ope="SELECT bagdet.fullName, bagdet.bagInfo[].tagNum FROM BaggageInfo bagdet WHERE bagdet.fullName IN (\"Lucinda Beckman\", \"Adam Phillips\",\"Dierdre Amador\",\"Fallon Clements\")";System.out.println("Using IN operator ");fetchRows(handle,in_ope);
String exists_ope="SELECT fullName, bag.ticketNo FROM BaggageInfo bag WHERE EXISTS bag.bagInfo[$element.bagArrivalDate >=\"2019-03-01T00:00:00\"]";
System.out.println("Using EXISTS operator ");
fetchRows(handle,exists_ope);

To execute your query use the borneo.NoSQLHandle.query() method.

Download the full code SQLOperators.py from the examples here.
# Fetch data from the table
def fetch_data(handle,sqlstmt):
   request = QueryRequest().set_statement(sqlstmt)
   print('Query results for: ' + sqlstmt)
   result = handle.query(request)
   for r in result.get_results():
      print('\t' + str(r))  
seqcomp_stmt = '''SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc
                     FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"'''
print('Using Sequence Comparison operator:')
fetch_data(handle,seqcomp_stmt)
logope_stmt = '''SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing,
                 bag.bagInfo[].flightLegs[].fltRouteSrc
                 FROM BaggageInfo bag
                 WHERE NOT bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"'''
print('Using Logical operator:')
fetch_data(handle,logope_stmt)
valcomp_stmt = '''SELECT fullname, bag.bagInfo[].routing
                  FROM BaggageInfo bag WHERE gender="M"'''
print('Using Value Comparison operator:')
fetch_data(handle,valcomp_stmt)
inope_stmt = '''SELECT bagdet.fullName, bagdet.bagInfo[].tagNum
                FROM BaggageInfo bagdet WHERE bagdet.fullName IN
               ("Lucinda Beckman", "Adam Phillips","Dierdre Amador","Fallon Clements")'''
print('Using IN operator:')
fetch_data(handle,inope_stmt)
existsope_stmt = '''SELECT fullName, bag.ticketNo FROM BaggageInfo bag WHERE
                    EXISTS bag.bagInfo[$element.bagArrivalDate >="2019-03-01T00:00:00"]'''
print('Using EXISTS operator:')
fetch_data(handle,existsope_stmt)

To execute a query use the Client.Query function.

Download the full code SQLOperators.go from the examples here.
 //fetch data from the table
func fetchData(client *nosqldb.Client, err error, tableName string, querystmt string)(){
   prepReq := &nosqldb.PrepareRequest{
		Statement: querystmt,
   }
   prepRes, err := client.Prepare(prepReq)
   if err != nil {
      fmt.Printf("Prepare failed: %v\n", err)
      return
   }
   queryReq := &nosqldb.QueryRequest{
		 PreparedStatement: &prepRes.PreparedStatement,   }
   var results []*types.MapValue
   for {
      queryRes, err := client.Query(queryReq)
      if err != nil {
         fmt.Printf("Query failed: %v\n", err)
  	return
      }
      res, err := queryRes.GetResults()
      if err != nil {
         fmt.Printf("GetResults() failed: %v\n", err)
  	return
      }
      results = append(results, res...)
      if queryReq.IsDone() {
         break
      }
   }
   for i, r := range results {
      fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
   }
} 
seqcomp_stmt := `SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc
                    FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"`
fmt.Printf("Using Sequence Comparison operator:\n"
fetchData(client, err,tableName,seqcomp_stmt)

logope_stmt := `SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing,
                bag.bagInfo[].flightLegs[].fltRouteSrc
                FROM BaggageInfo bag
                WHERE NOT bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"`
fmt.Printf("Using Logical operator:\n")
fetchData(client, err,tableName,logope_stmt)

valcomp_stmt := `SELECT fullname, bag.bagInfo[].routing FROM BaggageInfo bag WHERE gender="M"`
fmt.Printf("Using Value Comparison operator:\n")
fetchData(client, err,tableName,valcomp_stmt)

inope_stmt := `SELECT bagdet.fullName, bagdet.bagInfo[].tagNum FROM BaggageInfo bagdet
	        WHERE bagdet.fullName IN ("Lucinda Beckman", "Adam Phillips","Dierdre Amador","Fallon Clements")`
fmt.Printf("Using IN operator:\n")
fetchData(client, err,tableName,inope_stmt)

existsope_stmt := `SELECT fullName, bag.ticketNo FROM BaggageInfo bag WHERE
                   EXISTS bag.bagInfo[$element.bagArrivalDate >="2019-03-01T00:00:00"]`
fmt.Printf("Using EXISTS operator:\n")
fetchData(client, err,tableName,existsope_stmt)

To execute a query use query method.

JavaScript: Download the full code SQLOperators.js from the examples here.
  //fetches data from the table
async function fetchData(handle,querystmt) {
   const opt = {};
   try {
      do {
         const result = await handle.query(querystmt, opt);
         for(let row of result.rows) {
            console.log('  %O', row);
         }
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}
TypeScript: Download the full code SQLOperators.ts from the examples here.
interface StreamInt {
   acct_Id: Integer;
   profile_name: String;
   account_expiry: TIMESTAMP;
   acct_data: JSON;
}
/* fetches data from the table */
async function fetchData(handle: NoSQLClient,querystmt: string) {
   const opt = {};
   try {
      do {
         const result = await handle.query<StreamInt>(querystmt, opt);
         for(let row of result.rows) {
            console.log('  %O', row);
         }
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}

const seqcomp_stmt = `SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc
                      FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"`
console.log("Using Sequence Comparison operator");
await fetchData(handle,seqcomp_stmt);

const logope_stmt = `SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing,
                     bag.bagInfo[].flightLegs[].fltRouteSrc
                     FROM BaggageInfo bag
                     WHERE NOT bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"`
console.log("Using Logical operator");
await fetchData(handle,logope_stmt);

const valcomp_stmt = `SELECT fullname, bag.bagInfo[].routing FROM BaggageInfo bag WHERE gender="M"`
console.log("Using Value Comparison operator");
await fetchData(handle,valcomp_stmt);

const inope_stmt = `SELECT bagdet.fullName, bagdet.bagInfo[].tagNum
                    FROM BaggageInfo bagdet WHERE bagdet.fullName IN
                    ("Lucinda Beckman", "Adam Phillips","Dierdre Amador","Fallon Clements")`
console.log("Using IN operator");
await fetchData(handle,inope_stmt);

const existsope_stmt = `SELECT fullName, bag.ticketNo FROM BaggageInfo bag WHERE
                        EXISTS bag.bagInfo[$element.bagArrivalDate >="2019-03-01T00:00:00"]`
console.log("Using EXISTS operator");
await fetchData(handle,existsope_stmt);

To execute a query, you may call QueryAsync method or call GetQueryAsyncEnumerable method and iterate over the resulting async enumerable.

Download the full code SQLOperators.cs from the examples here.
private static async Task fetchData(NoSQLClient client,String querystmt){
   var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
   await DoQuery(queryEnumerable);
}

private static async Task DoQuery(IAsyncEnumerable<QueryResult<RecordValue>> queryEnumerable){
   Console.WriteLine("  Query results:");
   await foreach (var result in queryEnumerable) {
      foreach (var row in result.Rows)
      {
         Console.WriteLine();
         Console.WriteLine(row.ToJsonString());
      }
   }
} 
private const string seqcomp_stmt =@"SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc
                                           FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any ""SFO""";
Console.WriteLine("\nUsing Sequence Comparison operator!");
await fetchData(client,seqcomp_stmt);

private const string logope_stmt =@"SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing,
                                           bag.bagInfo[].flightLegs[].fltRouteSrc
                                           FROM BaggageInfo bag
                                           WHERE NOT bag.bagInfo[].flightLegs[].fltRouteSrc=any ""SFO""";
Console.WriteLine("\nUsing Logical operator!");
await fetchData(client,logope_stmt);

private const string valcomp_stmt =@"SELECT fullname, bag.bagInfo[].routing FROM BaggageInfo bag WHERE gender=""M""" ;
Console.WriteLine("\nUsing Value Comparison operator!");
await fetchData(client,valcomp_stmt);

private const string inope_stmt =@"SELECT bagdet.fullName, bagdet.bagInfo[].tagNum
                                          FROM BaggageInfo bagdet WHERE bagdet.fullName IN
                                          (""Lucinda Beckman"", ""Adam Phillips"",""Dierdre Amador"",""Fallon Clements"")";
Console.WriteLine("\nUsing IN operator!");
await fetchData(client,inope_stmt);

private const string existsope_stmt =@"SELECT fullName, bag.ticketNo FROM BaggageInfo bag WHERE
                                             EXISTS bag.bagInfo[$element.bagArrivalDate >=""2019-03-01T00:00:00""]";
Console.WriteLine("\nUsing EXISTS operator!");
await fetchData(client,existsope_stmt);