CASE
The CASE
clause allows you to perform conditional expressions in Cypher queries,
similar to switch or if-else statements in other languages. This clause is
useful for transforming query results based on conditions.
Syntax
There are two forms of the CASE
clause:
1. Simple CASE Expression
A simple form is used to compare an expression against multiple
predicates. For the first matched predicate result of the expression provided
after the THEN
keyword is returned. If no expression is matched value
following ELSE
is returned is provided, or null
if ELSE
is not used:
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
[ELSE default_result]
END
2. Generic CASE Expression
In generic form, you don’t need to provide an expression whose value is compared
to predicates, but you can list multiple predicates and the first one that
evaluates to true
is matched:
CASE
WHEN predicate1 THEN result1
WHEN predicate2 THEN result2
...
[ELSE default_result]
END
If no conditions match and no ELSE
is provided, null
is returned.
Examples
Example dataset setup
To follow along with the examples, you can create a sample dataset using the following queries:
CREATE (n1:Person {name: "Alice", currency: "DOLLAR", height: 165, age: 25}),
(n2:Person {name: "Bob", currency: "EURO", height: 175}),
(n3:Person {name: "Charlie", currency: "POUND", height: 290, age: 15}),
(n4:Person {name: "Diana", currency: null, height: null, age: null});
This dataset creates four Person
nodes with different property combinations,
including null
values.
1. Simple CASE expression
You can transform a property into a more readable format using the simple CASE
form:
MATCH (n:Person)
RETURN
n.name,
n.currency,
CASE n.currency
WHEN "DOLLAR" THEN "$"
WHEN "EURO" THEN "€"
ELSE "UNKNOWN"
END AS currency_symbol;
Result:
name | currency | currency_symbol |
---|---|---|
Alice | DOLLAR | $ |
Bob | EURO | € |
Charlie | POUND | UNKNOWN |
Diana | null | UNKNOWN |
2. Generic CASE expression
The generic CASE
expression evaluates multiple conditions and returns the
result of the first condition that is true
:
MATCH (n:Person)
RETURN
n.name,
n.height,
CASE
WHEN n.height < 150 THEN "short"
WHEN n.height > 250 THEN "tall"
ELSE "average"
END AS height_category;
Result:
name | height | height_category |
---|---|---|
Alice | 165 | average |
Bob | 175 | average |
Charlie | 290 | tall |
Diana | null | average |
Note: null
values in comparisons are treated as false
, so Diana
falls
through to the ELSE
clause.
3. Handling null
values explicitly
To handle null
values explicitly, you can add a specific condition using IS NULL
:
MATCH (n:Person)
RETURN
n.name,
n.age,
CASE
WHEN n.age IS NULL THEN "Age unknown"
WHEN n.age < 18 THEN "Minor"
WHEN n.age >= 18 THEN "Adult"
END AS age_category;
Result:
name | age | age_category |
---|---|---|
Alice | 25 | Adult |
Bob | null | Age unknown |
Charlie | 15 | Minor |
Diana | null | Age unknown |
This approach ensures that null
values are handled clearly and avoid being
interpreted as false
by default.