Conditional expressions (CASE)
Generic conditional expressions can be expressed in Cypher® using the CASE
construct.
Two variants of CASE
exist within Cypher: the simple form, to compare a single expression against multiple values, and the generic form, to express multiple conditional statements.
Example graph
The following graph is used for the examples below:
To recreate the graph, run the following query against an empty Neo4j database:
CREATE
(alice:Person {name:'Alice', age: 38, eyes: 'brown'}),
(bob:Person {name: 'Bob', age: 25, eyes: 'blue'}),
(charlie:Person {name: 'Charlie', age: 53, eyes: 'green'}),
(daniel:Person {name: 'Daniel', eyes: 'brown'}),
(eskil:Person {name: 'Eskil', age: 41, eyes: 'blue'}),
(alice)-[:KNOWS]->(bob),
(alice)-[:KNOWS]->(charlie),
(bob)-[:KNOWS]->(daniel),
(charlie)-[:KNOWS]->(daniel),
(bob)-[:MARRIED]->(eskil)
Simple CASE
The simple CASE
form is used to compare a single expression against multiple values, and is analogous to the switch
construct of programming languages.
The expressions are evaluated by the WHEN
operator until a match is found.
If no match is found, the expression in the ELSE
operator is returned.
If there is no ELSE
case and no match is found, null
will be returned.
Syntax
CASE test
WHEN value [, value]* THEN result
[WHEN ...]
[ELSE default]
END
Arguments:
Name | Description |
---|---|
|
An expression. |
|
An expression whose result will be compared to |
|
The expression returned as output if |
|
The expression to return if no value matches the test expression. |
Extended Simple CASE
The extended simple CASE
form allows the comparison operator to be specified explicitly. The simple CASE
uses an
implied equals (=
) comparator.
The supported comparators are:
-
Regular Comparison Operators:
=
,<>
,<
,>
,<=
,>=
-
IS NULL
Operator:IS [NOT] NULL
-
Type Predicate Expression:
IS [NOT] TYPED <TYPE>
(Note that the formIS [NOT] :: <TYPE>
is not accepted) -
Normalization Predicate Expression:
IS [NOT] NORMALIZED
-
String Comparison Operators:
STARTS WITH
,ENDS WITH
,=~
(regex matching)
Syntax
CASE test
WHEN [comparisonOperator] value [, [comparisonOperator] value ]* THEN result
[WHEN ...]
[ELSE default]
END
Arguments:
Name | Description |
---|---|
|
An expression. |
|
One of the supported comparison operators. |
|
An expression whose result is compared to |
|
The expression returned as output if |
|
The expression to return if no value matches the test expression. |
Example
MATCH (n:Person)
RETURN n.name,
CASE n.age
WHEN IS NULL, IS NOT TYPED INTEGER | FLOAT THEN "Unknown"
WHEN = 0, = 1, = 2 THEN "Baby"
WHEN <= 13 THEN "Child"
WHEN < 20 THEN "Teenager"
WHEN < 30 THEN "Young Adult"
WHEN > 1000 THEN "Immortal"
ELSE "Adult"
END AS result
n.name | result |
---|---|
|
|
|
|
|
|
|
|
|
|
Rows: 5 |
Generic CASE
The generic CASE
expression supports multiple conditional statements, and is analogous to the if-elseif-else
construct of programming languages.
Each row is evaluated in order until a true
value is found.
If no match is found, the expression in the ELSE
operator is returned.
If there is no ELSE
case and no match is found, null
will be returned.
Syntax
CASE
WHEN predicate THEN result
[WHEN ...]
[ELSE default]
END
Arguments:
Name | Description |
---|---|
|
A predicate is an expression that evaluates to a |
|
The expression returned as output if |
|
If no match is found, |
CASE
with null
values
When working with null
values, you may be forced to use the generic CASE
form.
The two examples below use the age
property of the Daniel
node (which has a null
value for that property) to clarify the difference.
CASE
MATCH (n:Person)
RETURN n.name,
CASE n.age (1)
WHEN null THEN -1 (2)
ELSE n.age - 10 (3)
END AS age_10_years_ago
1 | n.age is the expression being evaluated. Note that the node Daniel has a null value as age. |
2 | This branch is skipped, because null does not equal any other value, including null itself. |
3 | The execution takes the ELSE branch, which outputs null because n.age - 10 equals null . |
n.name | age_10_years_ago |
---|---|
|
|
|
|
|
|
|
|
|
|
Rows: 5 |
CASE
MATCH (n:Person)
RETURN n.name,
CASE (1)
WHEN n.age IS NULL THEN -1 (2)
ELSE n.age - 10
END AS age_10_years_ago
1 | If no expression is provided after CASE , it acts in its generic form, supporting predicate expressions in each branch. |
2 | This predicate expression evaluates to true for the node Daniel , so the result from this branch is returned. |
n.name | age_10_years_ago |
---|---|
|
|
|
|
|
|
|
|
|
|
Rows: 5 |
For more information about null
, see Working with null
.
CASE
expressions and succeeding clauses
The results of a CASE
expression can be used to set properties on a node or relationship.
MATCH (n:Person)
WITH n,
CASE n.eyes
WHEN 'blue' THEN 1
WHEN 'brown' THEN 2
ELSE 3
END AS colorCode
SET n.colorCode = colorCode
RETURN n.name, n.colorCode
n.name | n.colorCode |
---|---|
|
|
|
|
|
|
|
|
|
|
Rows: 5 |
For more information about using the SET
clause, see SET.
Further considerations
CASE
result branches are statically checked prior to execution.
This means that if a branch is not semantically correct, it will still throw an exception, even if that branch may never be executed during runtime.
In the following example, date
is statically known to be a STRING
value, and therefore would fail if treated as a DATE
value.
WITH "2024-08-05" AS date, "string" AS type
RETURN CASE type
WHEN "string" THEN datetime(date)
WHEN "date" THEN datetime({year: date.year, month: date.month, day: date.day})
ELSE datetime(date)
END AS dateTime
Type mismatch: expected Map, Node, Relationship, Point, Duration, Date, Time, LocalTime, LocalDateTime or DateTime but was String (line 4, column 38 (offset: 136))
" WHEN 'date' THEN datetime({year: date.year, month: date.month, day: date.day})"
^