Conditional Operators

Case expressions evaluate conditional logic in an expression.

case-expr:

simple-case-expression | searched-case-expression

Simple case expressions

simple-case-expr:

CASE expression  ( WHEN expression THEN expression)
     [ ( WHEN expression THEN expression) ]*
     [  ELSE expression ]  END

Simple case expressions allow for conditional matching within an expression. The evaluation process is as follows:

The first WHEN expression is evaluated. If it is equal to the search expression, the result of this expression is the THEN expression.
If it is not equal, subsequent WHEN clauses are evaluated in the same manner.
If none of the WHEN expressions are equal to the search expression, then the result of the CASE expression is the ELSE expression.
If no ELSE expression was provided, the result is NULL.

Searched case expressions

searched-case-expression:

CASE  ( WHEN  condition THEN expression)
       [( WHEN  condition THEN expression ) ]*
       [ ELSE  expression ] END

Searched case expressions allow for conditional logic within an expression. The evaluation process is as follows:

The first WHEN expression is evaluated.
If TRUE, the result of this expression is the THEN expression.
If not TRUE, subsequent WHEN clauses are evaluated in the same manner.
If none of the WHEN clauses evaluate to TRUE, then the result of the expression is the ELSE expression.
If no ELSE expression was provided, the result is NULL.

The following example uses a CASE clause to handle documents that do not have a ship date. This scans all orders. If an order has a shipped-on date, it is provided in the result set. If an order does not have a shipped-on date, default text appears.

Query:

SELECT
   CASE WHEN `shipped-on`
   IS NOT NULL THEN `shipped-on`
   ELSE "not-shipped-yet"
   END
   AS shipped
   FROM orders

Result:

{ "shipped": "2013/01/02" },
{ "shipped": "2013/01/12" },
{ "shipped": "not-shipped-yet" },