WHERE
Introduction
The WHERE
clause is not a clause in its own right — rather, it is part of the MATCH
, OPTIONAL MATCH
, and WITH
clauses.
When used with MATCH
and OPTIONAL MATCH
, WHERE
adds constraints to the patterns described.
It should not be seen as a filter after the matching is finished.
In the case of WITH
, however, WHERE
simply filters the results.
In the case of multiple MATCH
/ OPTIONAL MATCH
clauses, the predicate in WHERE
is always a part of the patterns in the directly preceding MATCH
/ OPTIONAL MATCH
.
Both results and performance may be impacted if WHERE
is put inside the wrong MATCH
clause.
Indexes may be used to optimize queries using WHERE
in a variety of cases.
Example graph
The following graph is used for the examples below:
To recreate the graph, run the following query in an empty Neo4j database:
CREATE
(andy:Swedish:Person {name: 'Andy', age: 36, belt: 'white'}),
(timothy:Person {name: 'Timothy', age: 25}),
(peter:Person {name: 'Peter', age: 35, email: 'peter_n@example.com'}),
(andy)-[:KNOWS {since: 2012}]->(timothy),
(andy)-[:KNOWS {since: 1999}]->(peter)
Basic usage
Node pattern predicates
WHERE
can appear inside a node pattern in a MATCH
clause or a pattern comprehension:
WITH 30 AS minAge
MATCH (a:Person WHERE a.name = 'Andy')-[:KNOWS]->(b:Person WHERE b.age > minAge)
RETURN b.name
b.name |
---|
|
|
When used this way, predicates in WHERE
can reference the node variable that the WHERE
clause belongs to, but not other elements of the MATCH
pattern.
The same rule applies to pattern comprehensions:
MATCH (a:Person {name: 'Andy'})
RETURN [(a)-->(b WHERE b:Person) | b.name] AS friends
friends |
---|
|
|
Boolean operations
The following boolean operators can be used with the WHERE
clause: AND
, OR
, XOR
, and NOT
.
For more information on how operators work with null
, see the chapter on Working with null.
MATCH (n:Person)
WHERE n.name = 'Peter' XOR (n.age < 30 AND n.name = 'Timothy') OR NOT (n.name = 'Timothy' OR n.name = 'Peter')
RETURN
n.name AS name,
n.age AS age
ORDER BY name
name | age |
---|---|
|
|
|
|
|
|
|
Filter on node label
To filter nodes by label, write a label predicate after the WHERE
keyword using WHERE n:foo
:
MATCH (n)
WHERE n:Swedish
RETURN n.name, n.age
The name
and age
values for Andy
are returned:
n.name | n.age |
---|---|
|
|
|
Filter on node property
To filter on a node property, write your clause after the WHERE
keyword:
MATCH (n:Person)
WHERE n.age < 30
RETURN n.name, n.age
The name
and age
values for Timothy
are returned because he is less than 30 years of age:
n.name | n.age |
---|---|
|
|
|
Filter on relationship property
To filter on a relationship property, write your clause after the WHERE
keyword:
MATCH (n:Person)-[k:KNOWS]->(f)
WHERE k.since < 2000
RETURN f.name, f.age, f.email
The name
, age
and email
values for Peter
are returned because Andy
has known him since before 2000:
f.name | f.age | f.email |
---|---|---|
|
|
|
|
Filter on dynamically-computed node property
To filter on a property using a dynamically computed name, use square bracket syntax:
WITH 'AGE' AS propname
MATCH (n:Person)
WHERE n[toLower(propname)] < 30
RETURN n.name, n.age
The name
and age
values for Timothy
are returned because he is less than 30 years of age:
n.name | n.age |
---|---|
|
|
|
Property existence checking
Use the IS NOT NULL
predicate to only include nodes or relationships in which a property exists:
MATCH (n:Person)
WHERE n.belt IS NOT NULL
RETURN n.name, n.belt
The name
and belt
values for Andy
are returned because he is the only one with a belt
property:
n.name | n.belt |
---|---|
|
|
|
Using WITH
As WHERE
is not considered a clause in its own right, its scope is not limited by a WITH
directly before it.
MATCH (n:Person)
WITH n.name as name
WHERE n.age = 25
RETURN name
name |
---|
|
|
The name for Timothy
is returned because the WHERE
clause still acts as a filter on the MATCH
.
WITH
reduces the scope for the rest of the query moving forward.
In this case, name
is now the only variable in scope for the RETURN
clause.
STRING
matching
The prefix and suffix of a STRING
can be matched using STARTS WITH
and ENDS WITH
.
To undertake a substring search (that is, match regardless of the location within a STRING
), use CONTAINS
.
The matching is case-sensitive.
Attempting to use these operators on values which are not STRING
values will return null
.
Prefix STRING
search using STARTS WITH
The STARTS WITH
operator is used to perform case-sensitive matching on the beginning of a STRING
:
MATCH (n:Person)
WHERE n.name STARTS WITH 'Pet'
RETURN n.name, n.age
The name
and age
values for Peter
are returned because his name starts with "Pet":
n.name | n.age |
---|---|
|
|
|
Suffix STRING
search using ENDS WITH
The ENDS WITH
operator is used to perform case-sensitive matching on the ending of a STRING
:
MATCH (n:Person)
WHERE n.name ENDS WITH 'ter'
RETURN n.name, n.age
The name
and age
values for Peter
are returned because his name ends with "ter":
n.name | n.age |
---|---|
|
|
|
Substring search using CONTAINS
The CONTAINS
operator is used to perform case-sensitive matching regardless of location within a STRING
:
MATCH (n:Person)
WHERE n.name CONTAINS 'ete'
RETURN n.name, n.age
The name
and age
for Peter
are are returned because his name contains "ete":
n.name | n.age |
---|---|
|
|
|
Checking if a STRING
IS NORMALIZED
The IS NORMALIZED
operator (introduced in Neo4j 5.17) is used to check whether the given STRING
is in the NFC
Unicode normalization form:
MATCH (n:Person)
WHERE n.name IS NORMALIZED
RETURN n.name AS normalizedNames
The given STRING
values contain only normalized Unicode characters, therefore all the matched name
properties are returned.
For more information, see the section about the normalization operator.
normalizedNames |
---|
|
|
|
Note that the IS NORMALIZED
operator returns null
when used on a non-STRING
value.
For example, RETURN 1 IS NORMALIZED
returns null
.
String matching negation
Use the NOT
keyword to exclude all matches on given STRING
from your result:
MATCH (n:Person)
WHERE NOT n.name ENDS WITH 'y'
RETURN n.name, n.age
The name
and age
values Peter
are returned because his name does not end with "y":
n.name | n.age |
---|---|
|
|
|
Regular expressions
Cypher® supports filtering using regular expressions.
The regular expression syntax is inherited from the Java regular expressions.
This includes support for flags that change how STRING
values are matched, including case-insensitive (?i)
, multiline (?m)
, and dotall (?s)
.
Flags are given at the beginning of the regular expression. For an example of a regular expression flag given at the beginning of a pattern, see the case-insensitive regular expression section.
Matching using regular expressions
To match on regular expressions, use =~ 'regexp'
:
MATCH (n:Person)
WHERE n.name =~ 'Tim.*'
RETURN n.name, n.age
The name
and age
values for Timothy
are returned because his name starts with "Tim".
n.name | n.age |
---|---|
|
|
|
Escaping in regular expressions
Characters like .
or *
have special meaning in a regular expression.
To use these as ordinary characters, without special meaning, escape them.
MATCH (n:Person)
WHERE n.email =~ '.*\\.com'
RETURN n.name, n.age, n.email
The name
, age
, and email
values for Peter
are returned because his email ends with ".com":
n.name | n.age | n.email |
---|---|---|
|
|
|
|
Note that the regular expression constructs in Java regular expressions are applied only after resolving the escaped character sequences in the given string literal. It is sometimes necessary to add additional backslashes to express regular expression constructs. This list clarifies the combination of these two definitions, containing the original escape sequence and the resulting character in the regular expression:
String literal sequence | Resulting Regex sequence | Regex match |
---|---|---|
|
Tab |
Tab |
|
|
Tab |
|
Backspace |
Backspace |
|
|
Word boundary |
|
Newline |
NewLine |
|
|
Newline |
|
Carriage return |
Carriage return |
|
|
Carriage return |
|
Form feed |
Form feed |
|
|
Form feed |
|
Single quote |
Single quote |
|
Double quote |
Double quote |
|
Backslash |
Backslash |
|
|
Backslash |
|
Unicode UTF-16 code point (4 hex digits must follow the |
Unicode UTF-16 code point (4 hex digits must follow the |
|
|
Unicode UTF-16 code point (4 hex digits must follow the |
Using regular expressions with unsanitized user input makes you vulnerable to Cypher injection. Consider using parameters instead. |
Case-insensitive regular expressions
By pre-pending a regular expression with (?i)
, the whole expression becomes case-insensitive:
MATCH (n:Person)
WHERE n.name =~ '(?i)AND.*'
RETURN n.name, n.age
The name
and age
for Andy
are returned because his name starts with 'AND' irrespective of casing:
n.name | n.age |
---|---|
|
|
|
Path pattern expressions
Similar to existential subqueries, path pattern expressions can be used to assert whether a specified path exists at least once in a graph. While existential subqueries are more powerful and capable of performing anything achievable with path pattern expressions, path pattern expressions are more concise.
Path pattern expressions have the following restrictions (use cases that require extended functionality should consider using existential subqueries instead):
-
Path pattern expressions may only use a subset of graph pattern semantics.
-
A path pattern expression must be a path pattern of length greater than zero. In other words, it must contain at least one relationship or variable-length relationship.
-
Path pattern expressions may not declare new variables. They can only reference existing variables.
-
Path pattern expressions may only be used in positions where a boolean expression is expected. The following sections will demonstrate how to use path pattern expressions in a
WHERE
clause.
Filter on patterns
MATCH
(timothy:Person {name: 'Timothy'}),
(other:Person)
WHERE (other)-->(timothy)
RETURN other.name, other.age
The name
and age
values for nodes that have an outgoing relationship to Timothy
are returned:
other.name | other.age |
---|---|
|
|
|
Filter on patterns using NOT
The NOT
operator can be used to exclude a pattern:
MATCH
(peter:Person {name: 'Peter'}),
(other:Person)
WHERE NOT (other)-->(peter)
RETURN other.name, other.age
The name
and age
values for nodes that do not have an outgoing relationship to Peter
are returned:
other.name | other.age |
---|---|
|
|
|
|
|
Filter on patterns with properties
Properties can also be added to patterns:
MATCH (other:Person)
WHERE (other)-[:KNOWS]-({name: 'Timothy'})
RETURN other.name, other.age
The name
and age
values are returned for nodes that have a relationship with the type KNOWS
connected to Timothy
:
other.name | other.age |
---|---|
|
|
|
Missing properties and values
Default to false
if property is missing
As missing properties evaluate to null
, the comparison in the example will evaluate to false
for nodes without the belt
property:
MATCH (n:Person)
WHERE n.belt = 'white'
RETURN n.name, n.age, n.belt
Only the name
, age
, and belt
values of nodes with white belts are returned:
n.name | n.age | n.belt |
---|---|---|
|
|
|
|
Default to true
if property is missing
To compare node or relationship properties against missing properties, use the IS NULL
operator:
MATCH (n:Person)
WHERE n.belt = 'white' OR n.belt IS NULL
RETURN n.name, n.age, n.belt
ORDER BY n.name
This returns all values for all nodes, even those without the belt
property:
n.name | n.age | n.belt |
---|---|---|
|
|
|
|
|
|
|
|
|
|
Filter on null
To test if a value or variable is null
, use the IS NULL
operator.
To test if a value or variable is not null
, use the IS NOT NULL
operator
NOT(IS NULL x)
also works.
MATCH (person:Person)
WHERE person.name = 'Peter' AND person.belt IS NULL
RETURN person.name, person.age, person.belt
The name
and age
values for nodes that have name Peter
but no belt
property are returned:
person.name | person.age | person.belt |
---|---|---|
|
|
|
|
Using ranges
Simple range
To check whether an element exists within a specific range, use the inequality operators <
, ⇐
, >=
, >
:
MATCH (a:Person)
WHERE a.name >= 'Peter'
RETURN a.name, a.age
The name
and age
values of nodes having a name
property lexicographically (i.e. using the dictionary order) greater than or equal to Peter
are returned:
a.name | a.age |
---|---|
|
|
|
|
|
Composite range
Several inequalities can be used to construct a range:
MATCH (a:Person)
WHERE a.name > 'Andy' AND a.name < 'Timothy'
RETURN a.name, a.age
The name
and age
values of nodes having a name
property lexicographically between Andy
and Timothy
are returned:
a.name | a.age |
---|---|
|
|
|
Pattern element predicates
WHERE
clauses can be added to pattern elements in order to specify additional constraints:
Relationship pattern predicates
WHERE
can also appear inside a relationship pattern in a MATCH
clause:
WITH 2000 AS minYear
MATCH (a:Person)-[r:KNOWS WHERE r.since < minYear]->(b:Person)
RETURN r.since
r.since |
---|
|
|
However, it cannot be used inside of variable length relationships, as this would lead to an error. For example:
WITH 2000 AS minYear
MATCH (a:Person)-[r:KNOWS*1..3 WHERE r.since > b.yearOfBirth]->(b:Person)
RETURN r.since
Relationship pattern predicates are not supported for variable-length relationships.
Putting predicates inside a relationship pattern can help with readability.
Note that it is strictly equivalent to using a standalone WHERE
sub-clause.
WITH 2000 AS minYear
MATCH (a:Person)-[r:KNOWS]->(b:Person)
WHERE r.since < minYear
RETURN r.since
r.since |
---|
|
|
Relationship pattern predicates can also be used inside pattern comprehensions, where the same caveats apply:
WITH 2000 AS minYear
MATCH (a:Person {name: 'Andy'})
RETURN [(a)-[r:KNOWS WHERE r.since < minYear]->(b:Person) | r.since] AS years
years |
---|
|
|