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-55 Case Expressions

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.