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.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 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.ageis36. It is an integer.
$$.player.age > 35is evaluated to betrue. 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.