Case Expressions

The searched CASE expression is similar to the if-then-else statements of traditional programming languages. It consists of a number of WHEN-THEN pairs, followed by an optional ELSE clause at the end. Each WHEN expression is a condition, i.e., it must return BOOLEAN. The THEN expressions as well as the ELSE expression may return any sequence of items. The CASE expression is evaluated by first evaluating the WHEN expressions from top to bottom until the first one that returns true. If it is the i-th WHEN expression that returns true, then the i-th THEN expression is evaluated and its result is the result of the whole CASE expression. If no WHEN expression returns true, then if there is an ELSE, its expression is evaluated and its result is the result of the whole CASE expression; Otherwise, the result of the CASE expression is the empty sequence.

Example:

SELECT
    fullName,
    CASE
        WHEN NOT exists bag.bagInfo.flightLegs[0]
        THEN "you have no bag info"
        WHEN NOT exists bag.bagInfo.flightLegs[1]
        THEN "you have one hop"
        WHEN NOT exists bag.bagInfo.flightLegs[2]
        THEN "you have two hops."
        ELSE "you have three hops."
    END AS NUMBER_HOPS
FROM BaggageInfo bag WHERE ticketNo=1762340683564

Explanation: You want to determine how many transits are there for the passenger bagInfo using a CASE statement. If the flightLegs array has no elements, then the passenger has no bag data. When the flightLegs array has only one element, then there is only one transit point. Similarly, if the flightLegs array has two elements, then there is two hops. Else there is three transit points. Here you assume that a bag can have at the most three transit points/hops.

Output:

{"fullName":"Mary Watson","NUMBER_HOPS":"you have two hops."}

Example 2: Write a query to alert the system to update the tagNum of passengers if the existing value is not a string.

SELECT bag.bagInfo[].tagNum,
CASE
   WHEN bag.bagInfo[0].tagNum is of type (NUMBER)
   THEN "Tagnumber is not a STRING. Update the data"
   ELSE "Tagnumber has correct datatype"
   END AS tag_NUM_TYPE
FROM BaggageInfo bag 

Explanation: The tagNum of passengers in the bagInfo schema is a STRING data type. But the application could take in a NUMBER value as the value of tagNum by mistake. The query uses "is of type" operator to capture this and prompts the system to update the tagNum if the existing value is not a string.

Output (only few rows are shown for brevity).

{"tagNum":"17657806240001","tag_NUM_TYPE":"Tagnumber has correct datatype"}
{"tagNum":"17657806224224","tag_NUM_TYPE":"Tagnumber has correct datatype"}
{"tagNum":17657806243578,"tag_NUM_TYPE":"Tagnumber is not a STRING. Update the data"}
{"tagNum":"1765780623244","tag_NUM_TYPE":"Tagnumber has correct datatype"}