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 properties($$).name AS Name, \
        CASE properties($$).age > 35 \
        WHEN true THEN "Yes" \
        WHEN false THEN "No" \
        ELSE "Nah" \
        END \
        AS Age_above_35;
+-----------------+--------------+
| Name            | Age_above_35 |
+-----------------+--------------+
| "Tony Parker"   | "Yes"        |
| "Manu Ginobili" | "Yes"        |
+-----------------+--------------+

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.player.age > 30 \
        RETURN v.player.name AS Name,  \
        CASE \
        WHEN v.player.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 properties($$).name AS Name, properties($$).age AS Age, \
        CASE properties($$).age \
        WHEN properties($$).age > 35 THEN "Yes" \
        ELSE "No" \
        END \
        AS Age_above_35;
+-----------------+-----+--------------+
| Name            | Age | Age_above_35 |
+-----------------+-----+--------------+
| "Tony Parker"   | 36  | "No"         |
| "Manu Ginobili" | 41  | "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: March 8, 2022