Skip to content

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: April 22, 2021