Case Expressions

Syntax

case_expression ::= CASE 
   WHEN expression THEN expression 
   (WHEN expression THEN expression)* 
   [ELSE expression] 
   END

Semantics

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 6-88 For each user create a map with 3 fields recording the user's last name, their phone information, and the expense categories in which more than $5000 was spent

SELECT
{
    "last_name" : u.lastName,
    "phones" : CASE
        WHEN exists u.address.phones 
        THEN u.address.phones
        ELSE "Phone info absent or not at the expected place"
    END,
    "high_expenses" : [ u.expenses.keys($value > 5000) ]
}
FROM users u

The query is very similar to the one from array and map constructor. The only difference is in the use of a case expression to compute the value of the phones field. This guarantees that the phones field will always be present, even if the path expression u.address.phones return empty or NULL. Notice that wrapping the path expression with an explicit array constructor (as we did for the high_expenses field) would not be a good solution here, because in most cases u.address.phones will return an array, and we don't want to have construct an extra array containing just another array.

JSON collection table:

The following example applies CASE expression on a JSON collection table. Consider a sample row from the JSON collection table created for a shopping application:

{"contactPhone":"1917113999","address":{"city":"San Jose","number":501,"state":"San Francisco","street":"Maine","zip":95095},"cart":[{"item":"wallet","priceperunit":950,"quantity":2},{"item":"wall art","priceperunit":9500,"quantity":1}],"firstName":"Sharon","gender":"F","lastName":"Willard","notify":"yes","wishlist":[{"item":"Tshirt","priceperunit":500},{"item":"Jenga","priceperunit":850}]}

Example 6-89 Display promotional messages to shoppers from San Jose who have wallet or handbag items in their carts

SELECT concat("Hi ",s.firstName) AS Message,
    CASE
        WHEN s.cart.item =any "wallet"
        THEN "The prices on Wallets have dropped"
        WHEN s.cart.item =any "handbag"
        THEN "The prices on handbags have dropped"
        ELSE "Exciting offers on wallets and handbags"
    END AS Offer
FROM storeAcct s

WHERE s.address.city =any "San Jose";

Explanation: You can use CASE statement to display a promotional message to the shoppers regarding the reduction in the prices if the shoppers have the items wallet or handbag in their cart. As the offers are only for shoppers from San Jose, you specify the city in the WHERE clause.

Output:

{"Message":"Hi Sharon","Offer":"The prices on Wallets have
    dropped"}