Searched Case Expressions

CASE 
    WHEN <expr> THEN <expr> 
    (WHEN <expr> THEN <expr>)* 
    (ELSE <expr>)? 
    END;

The searched case expression consists of a number of when-then pairs, followed by an optional else clause at the end. Each when expression is a condition that must return boolean. The then expressions as well as the else expression may return any sequence of items.

The case expression is evaluated by:

  1. Evaluating the when expressions from top to bottom until the first one is discovered that returns true.

  2. The then expression for the previously identified when is evaluated. This result is returned as the result for the entire case expression.

  3. If no when expression returns true, but there is an else expression, then that expression is evaluated and its result is the result of the entire case expression.

  4. Otherwise, the result of the entire case expression is the empty sequence.

For example, construct a map using the map constructor ({}) in which the phones: element is either the contents of the phones column, or a string to indicate nothing was found in that column:

select {
    “last_name” : u.lastName,
    “phones” : case
               when exists u.address.phones then u.address.phones
               else “Phone info absent at the expected place” 
               end,
    “high_expenses” : [ u.expenses.keys($value > 5000) ]
  }
from Users u; 

For more examples of using searched case expressions, see Using Searched Case.