WHERE Clause

The WHERE clause filters the rows coming from the FROM clause, returning the rows satisfying a given condition.

Syntax

where_clause ::= WHERE expression

Semantics

For each context row, the expression in the WHERE clause is evaluated. The result of this expression must have type BOOLEAN ?. If the result is false, or empty, or NULL, the row is skipped; otherwise the row is passed on to the next clause.

Example 6-7 Fetch the list of Female passengers from the airline application

SELECT
fullname,
ticketNo
FROM Baggageinfo
WHERE gender="F"

Explanation: In the above query, you list the name and ticket details of the female passengers from the Baggageinfo table. The WHERE clause filters the rows based on the gender field of each record. You fetch all the records that hold the entry 'F' in the gender field.

Output:

{"fullname":"Adelaide Willard","ticketNo":1762392135540}
{"fullname":"Elane Lemons","ticketNo":1762324912391}
{"fullname":"Michelle Payne","ticketNo":1762330498104}
{"fullname":"Doris Martin","ticketNo":1762355527825}
{"fullname":"Rosalia Triplett","ticketNo":1762311547917}
{"fullname":"Zulema Martindale","ticketNo":1762340579411}
{"fullname":"Joanne Diaz","ticketNo":1762383911861}
{"fullname":"Kendal Biddle","ticketNo":1762377974281}
{"fullname":"Mary Watson","ticketNo":1762340683564}

Example 6-8 Fetch the list of passengers from the airline application whose destination station is MEL

SELECT
fullname,
ticketNo,
s.bagInfo.routing[] AS ROUTING
FROM Baggageinfo s
WHERE regex_like(s.bagInfo.routing[], ".*/MEL")

Explanation: In an airline application, you can fetch the list of passengers bound toward specific destination stations. The routing field in the bagInfo array holds the routing details of the passengers in the Baggageinfo table. The routing information is stored in the format Source/Transit/Destination airport codes.

In this query, you fetch the details of the passengers whose destination station is MEL. You use the regex_like function to achieve a pattern match to the destination airport code, MEL. Since you are only looking for a specific destination station, use the combination of the period (. ) metacharacter and greedy quantifier (* ) to allow zero or more occurrences of any source and transit airport code. For more details on the regex_like function, see Regular Expression Conditions.

Output:

{"fullname":"Zulema Martindale","ticketNo":1762340579411,"ROUTING":"MIA/LAX/MEL"}
{"fullname":"Adam Phillips","ticketNo":1762344493810,"ROUTING":"MIA/LAX/MEL"}
{"fullname":"Zina Christenson","ticketNo":1762390789239,"ROUTING":"MIA/LAX/MEL"}
{"fullname":"Joanne Diaz","ticketNo":1762383911861,"ROUTING":"MIA/LAX/MEL"}

Example 6-9 Fetch the list of passengers carrying more than one piece of luggage in the airline application

SELECT
fullname,
ticketNo
FROM Baggageinfo s
WHERE EXISTS s.baginfo[1]

Explanation: In this query, you list the name and ticket details of the passengers traveling with more than one piece of luggage. The bagInfo array holds the information on all the luggage owned by a passenger. If a passenger owns more than one piece of luggage, there will be more than one element in the bagInfo array. You use the EXISTS operator in the WHERE clause to determine whether or not a second element exists in the baginfo array.

Output:

{"fullname":"Lorenzo Phil","ticketNo":1762320369957}

Alternatively, you can use the size() built-in function to determine the size of the baginfo array, which is the number of bags owned by a passenger. You then use the value comparison operator '>' to check if the return value exceeds 1. You will get the same output as above. For more details on the size() function, see Functions on Complex Values.

SELECT
fullname,
ticketNo
FROM Baggageinfo s
WHERE size(s.baginfo) > 1