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}