case.adoc

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.

Note	CASE can only be used as part of clauses/return.adoc or clauses/with.adoc if you want to use the result in a subsequent clause.

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
test	An expression.
value	An expression whose result will be compared to test.
result	The expression returned as output if value matches test
default	The expression to return if no value matches the test expression.

Example

MATCH (n:Person)
RETURN
CASE n.eyes
  WHEN 'blue'  THEN 1
  WHEN 'brown', 'hazel' THEN 2
  ELSE 3
END AS result, n.eyes

result	n.eyes
2	"brown"
1	"blue"
3	"green"
2	"brown"
1	"blue"
Rows: 5

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 form IS [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
test	An expression.
comparisonOperator	One of the supported comparison operators.
value	An expression whose result is compared to test using the given comparison operator.
result	The expression returned as output if value matches test.
default	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
"Alice"	"Adult"
"Bob"	"Young Adult"
"Charlie"	"Adult"
"Daniel"	"Unknown"
"Eskil"	"Adult"
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
predicate	A predicate is an expression that evaluates to a BOOLEAN value. In this case, the predicate is tested to find a valid alternative.
result	The expression returned as output if predicate evaluates to true.
default	If no match is found, default is returned.

Example

MATCH (n:Person)
RETURN
CASE
  WHEN n.eyes = 'blue' THEN 1
  WHEN n.age < 40      THEN 2
  ELSE 3
END AS result, n.eyes, n.age

result	n.eyes	n.age
2	"brown"	38
1	"blue"	25
3	"green"	53
3	"brown"	null
1	"blue"	41
Rows: 5

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.

Simple 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
"Alice"	28
"Bob"	15
"Charlie"	43
"Daniel"	null
"Eskil"	31
Rows: 5

Generic 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
"Alice"	28
"Bob"	15
"Charlie"	43
"Daniel"	-1
"Eskil"	31
Rows: 5

For more information about null, see values-and-types/working-with-null.adoc.

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
"Alice"	2
"Bob"	1
"Charlie"	3
"Daniel"	2
"Eskil"	1
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.

Not allowed

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

Error message

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})"
                                      ^