CASE expressions¶

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

The CASE expression goes through conditions and returns a result when the first condition is met. Then 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 following graph is used for the examples in this topic.

Example graph for CASE expressions

The simple form of CASE expressions¶

Syntax¶

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

CAUTION: Always remember to end a CASE expression with END.

Parameters	Description
`comparer`	A value or a valid expression that outputs a value. This value is used to compare with `value`.
`value`	It will be compared with `comparer`. If they match, then this condition is met.
`result`	It is returned by the `CASE` expression if `value` matches `comparer`.
`default`	It 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

Parameters	Description
`condition`	If `condition` is evaluated as true, `result` is returned by the `CASE` expression.
`result`	It is returned by the `CASE` expression if `condition` is evaluated as true.
`default`	It 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 age is above 35. However, in this example, when the player 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 true. It is a boolean.

The values of $$.player.age and $$.player.age > 35 do not match. This condition is not met and "No" is returned.

Last update: March 17, 2021