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.

CASE can only be used as part of RETURN or WITH if you want to use the result in a subsequent clause.

Example graph

The following graph is used for the examples below:

case graph

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:

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 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

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