A newer version of this documentation is available.

View Latest

Conditional Operators

  • reference
February 16, 2025
+ 12

Case expressions evaluate conditional logic in an expression.

case-expr ::= simple-case-expr | searched-case-expr
Syntax diagram

Simple Case Expressions

simple-case-expr ::= 'CASE' expr ('WHEN' expr 'THEN' expr)+ ('ELSE' expr)? 'END'
Syntax diagram

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-expr ::= 'CASE' ('WHEN' cond 'THEN' expr)+ ('ELSE' expr)? 'END'
Syntax diagram

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

  • The first WHEN condition 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.

Example

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
n1ql
SELECT CASE WHEN `shipped-on` IS NOT NULL THEN `shipped-on` ELSE "not-shipped-yet" END AS shipped FROM orders
Result
json
{ "shipped": "2013/01/02" }, { "shipped": "2013/01/12" }, { "shipped": "not-shipped-yet" },