# CASE expressions¶

The CASE expression uses conditions to filter the result of an nGQL query statement. It is usually used in the YIELD and RETURN clauses. nGQL provides two forms of CASE expressions just like openCypher: the simple form and the generic form.

The CASE expression will traverse all the conditions. When the first condition is met, the CASE expression stops reading the conditions and returns the result. If no conditions are met, it returns the result in the ELSE clause. If there is no ELSE clause and no conditions are met, it returns NULL.

## The simple form of CASE expressions¶

### Syntax¶

CASE <comparer>
WHEN <value> THEN <result>
[WHEN ...]
[ELSE <default>]
END


Caution

Always remember to end the CASE expression with an END.

Parameter Description
comparer A value or a valid expression that outputs a value. This value is used to compare with the value.
value It will be compared with the comparer. If the value matches the comparer, then this condition is met.
result The result is returned by the CASE expression if the value matches the comparer.
default The default is returned by the CASE expression if no conditions are met.

### Examples¶

nebula> RETURN \
CASE 2+3 \
WHEN 4 THEN 0 \
WHEN 5 THEN 1 \
ELSE -1 \
END \
AS result;
+--------+
| result |
+--------+
| 1      |
+--------+

nebula> GO FROM "player100" OVER follow \
YIELD $$.player.name AS Name, \ CASE$$.player.age > 35 \
WHEN true THEN "Yes" \
WHEN false THEN "No" \
ELSE "Nah" \
END \
AS Age_above_35;
+---------------------+--------------+
| Name                | Age_above_35 |
+---------------------+--------------+
| "Tony Parker"       | "Yes"        |
+---------------------+--------------+
| "LaMarcus Aldridge" | "No"         |
+---------------------+--------------+


## The generic form of CASE expressions¶

### Syntax¶

CASE
WHEN <condition> THEN <result>
[WHEN ...]
[ELSE <default>]
END

Parameter Description
condition If the condition is evaluated as true, the result is returned by the CASE expression.
result The result is returned by the CASE expression if the condition is evaluated as true.
default The default is returned by the CASE expression if no conditions are met.

### Examples¶

nebula> YIELD \
CASE WHEN 4 > 5 THEN 0 \
WHEN 3+4==7 THEN 1 \
ELSE 2 \
END \
AS result;
+--------+
| result |
+--------+
| 1      |
+--------+

nebula> MATCH (v:player) WHERE v.age > 30 \
RETURN v.name AS Name,  \
CASE \
WHEN v.name STARTS WITH "T" THEN "Yes" \
ELSE "No" \
END \
AS Starts_with_T;
+---------------------+---------------+
| Name                | Starts_with_T |
+---------------------+---------------+
| "Tim"               | "Yes"         |
+---------------------+---------------+
| "LaMarcus Aldridge" | "No"          |
+---------------------+---------------+
| "Tony Parker"       | "Yes"         |
+---------------------+---------------+


## Differences between the simple form and the generic form¶

To avoid the misuse of the simple form and the generic form, it is important to understand their differences. The following example can help explain them.

nebula> GO FROM "player100" OVER follow \
YIELD $$.player.name AS Name,$$.player.age AS Age, \
CASE $$.player.age \ WHEN$$.player.age > 35 THEN "Yes" \
ELSE "No" \
END \
AS Age_above_35;
+---------------------+-----+--------------+
| Name                | Age | Age_above_35 |
+---------------------+-----+--------------+
| "Tony Parker"       | 36  | "No"         |
+---------------------+-----+--------------+
| "LaMarcus Aldridge" | 33  | "No"         |
+---------------------+-----+--------------+


The preceding GO query is intended to output Yes when the player's age is above 35. However, in this example, when the player's age is 36, the actual output is not as expected: It is No instead of Yes.

This is because the query uses the CASE expression in the simple form, and a comparison between the values of $$.player.age and $$.player.age > 35 is made. When the player age is 36:

• The value of $$.player.age is 36. It is an integer. • $$.player.age > 35 is evaluated to be true. It is a boolean.

The values of $$.player.age and $$.player.age > 35 do not match. Therefore, the condition is not met and No is returned.

Last update: July 19, 2021