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:

graph where clause

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:

Query
WITH 30 AS minAge
MATCH (a:Person WHERE a.name = 'Andy')-[:KNOWS]->(b:Person WHERE b.age > minAge)
RETURN b.name
Table 1. Result
b.name

"Peter"

Rows: 1

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:

Query
MATCH (a:Person {name: 'Andy'})
RETURN [(a)-->(b WHERE b:Person) | b.name] AS friends
Table 2. Result
friends

["Peter","Timothy"]

Rows: 1

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.

Query
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
Table 3. Result
name age

"Andy"

36

"Peter"

35

"Timothy"

25

Rows: 3

Filter on node label

To filter nodes by label, write a label predicate after the WHERE keyword using WHERE n:foo:

Query
MATCH (n)
WHERE n:Swedish
RETURN n.name, n.age

The name and age values for Andy are returned:

Table 4. Result
n.name n.age

"Andy"

36

Rows: 1

Filter on node property

To filter on a node property, write your clause after the WHERE keyword:

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

Table 5. Result
n.name n.age

"Timothy"

25

Rows: 1

Filter on relationship property

To filter on a relationship property, write your clause after the WHERE keyword:

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

Table 6. Result
f.name f.age f.email

"Peter"

35

"peter_n@example.com"

Rows: 1

Filter on dynamically-computed node property

To filter on a property using a dynamically computed name, use square bracket syntax:

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

Table 7. Result
n.name n.age

"Timothy"

25

Rows: 1

Property existence checking

Use the IS NOT NULL predicate to only include nodes or relationships in which a property exists:

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

Table 8. Result
n.name n.belt

"Andy"

"white"

Rows: 1

Using WITH

As WHERE is not considered a clause in its own right, its scope is not limited by a WITH directly before it.

Query
MATCH (n:Person)
WITH n.name as name
WHERE n.age = 25
RETURN name
Table 9. Result
name

"Timothy"

Rows: 1

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

Query
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":

Table 10. Result
n.name n.age

"Peter"

35

Rows: 1

Suffix string search using ENDS WITH

The ENDS WITH operator is used to perform case-sensitive matching on the ending of a string:

Query
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":

Table 11. Result
n.name n.age

"Peter"

35

Rows: 1

Substring search using CONTAINS

The CONTAINS operator is used to perform case-sensitive matching regardless of location within a string:

Query
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":

Table 12. Result
n.name n.age

"Peter"

35

Rows: 1

String matching negation

Use the NOT keyword to exclude all matches on given string from your result:

Query
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":

Table 13. Result
n.name n.age

"Peter"

35

Rows: 1

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 strings 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':

Query
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".

Table 14. Result
n.name n.age

"Timothy"

25

Rows: 1

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.

Query
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":

Table 15. Result
n.name n.age n.email

"Peter"

35

"peter_n@example.com"

Rows: 1

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

\t

Tab

Tab

\\t

\t

Tab

\b

Backspace

Backspace

\\b

\b

Word boundary

\n

Newline

NewLine

\\n

\n

Newline

\r

Carriage return

Carriage return

\\r

\r

Carriage return

\f

Form feed

Form feed

\\f

\f

Form feed

\'

Single quote

Single quote

\"

Double quote

Double quote

\\

Backslash

Backslash

\\\

\\

Backslash

\uxxxx

Unicode UTF-16 code point (4 hex digits must follow the \u)

Unicode UTF-16 code point (4 hex digits must follow the \u)

\\uxxxx

\uxxxx

Unicode UTF-16 code point (4 hex digits must follow the \u)

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:

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

Table 16. Result
n.name n.age

"Andy"

36

Rows: 1

Using path patterns in WHERE

Filter on patterns

Patterns are expressions in Cypher, expressions that return a list of paths. List expressions are also predicates; an empty list represents false, and a non-empty list represents true.

Patterns are not only expressions, they are also predicates. The only limitation to a pattern is that it must be able to express it in a single path. Unlike MATCH, it is not possible to use commas between multiple paths. To combine multiple patterns using WHERE, instead use AND. Note that new variables cannot be introduced. Although it might look very similar to the MATCH patterns, the WHERE clause is used to eliminate matched paths. MATCH (a)-[*]->(b) is very different from WHERE (a)-[*]->(b). The first will produce a path for every path it can find between a and b, whereas the latter will eliminate any matched paths where a and b do not have a directed relationship chain between them.

Query
MATCH
  (timothy:Person {name: 'Timothy'}),
  (other:Person)
WHERE other.name IN ['Andy', 'Peter'] AND (other)-->(timothy)
RETURN other.name, other.age

The name and age values for nodes that have an outgoing relationship to Timothy are returned:

Table 17. Result
other.name other.age

"Andy"

36

Rows: 1

Filter on patterns using NOT

The NOT operator can be used to exclude a pattern:

Query
MATCH
  (person:Person),
  (peter:Person {name: 'Peter'})
WHERE NOT (person)-->(peter)
RETURN person.name, person.age

The name and age values for nodes that do not have an outgoing relationship to Peter are returned:

Table 18. Result
person.name person.age

"Timothy"

25

"Peter"

35

Rows: 2

Filter on patterns with properties

Properties can also be added to patterns:

Query
MATCH (n:Person)
WHERE (n)-[:KNOWS]-({name: 'Timothy'})
RETURN n.name, n.age

The name and age values are returned for nodes that have a relationship with the type KNOWS connected to Timothy:

Table 19. Result
n.name n.age

"Andy"

36

Rows: 1

Filter on relationship type

You can put the exact relationship type in the MATCH pattern, It also possible to do more advanced filtering on the relationship type by using the special property type to compare the type with something else. In this example, the query does a regular expression comparison with the name of the relationship type:

Query
MATCH (n:Person)-[r]->()
WHERE n.name='Andy' AND type(r) =~ 'K.*'
RETURN type(r), r.since

This returns all relationships having a type whose name starts with 'K':

Table 20. Result
type(r) r.since

"KNOWS"

1999

"KNOWS"

2012

Rows: 2

Lists

IN operator

To check if an element exists in a list, use the IN operator. The below query checks whether a property exists in a literal list:

Query
MATCH (a:Person)
WHERE a.name IN ['Peter', 'Timothy']
RETURN a.name, a.age
Table 21. Result
a.name a.age

"Timothy"

25

"Peter"

35

Rows: 2

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:

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

Table 22. Result
n.name n.age n.belt

"Andy"

36

"white"

Rows: 1

Default to true if property is missing

To compare node or relationship properties against missing properties, use the IS NULL operator:

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

Table 23. Result
n.name n.age n.belt

"Andy"

36

"white"

"Peter"

35

<null>

"Timothy"

25

<null>

Rows: 3

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.

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

Table 24. Result
person.name person.age person.belt

"Peter"

35

<null>

Rows: 1

Using ranges

Simple range

To check whether an element exists within a specific range, use the inequality operators <, <=, >=, >:

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

Table 25. Result
a.name a.age

"Timothy"

25

"Peter"

35

Rows: 2

Composite range

Several inequalities can be used to construct a range:

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

Table 26. Result
a.name a.age

"Peter"

35

Rows: 1

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:

Query
WITH 2000 AS minYear
MATCH (a:Person)-[r:KNOWS WHERE r.since < minYear]->(b:Person)
RETURN r.since
Table 27. Result
r.since

1999

Rows: 1

However, it cannot be used inside of variable length relationships, as this would lead to an error. For example:

Query
WITH 2000 AS minYear
MATCH (a:Person)-[r:KNOWS*1..3 WHERE r.since > b.yearOfBirth]->(b:Person)
RETURN r.since
Error message
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.

Query
WITH 2000 AS minYear
MATCH (a:Person)-[r:KNOWS]->(b:Person)
WHERE r.since < minYear
RETURN r.since
Table 28. Result
r.since

1999

Rows: 1

Relationship pattern predicates can also be used inside pattern comprehensions, where the same caveats apply:

Query
WITH 2000 AS minYear
MATCH (a:Person {name: 'Andy'})
RETURN [(a)-[r:KNOWS WHERE r.since < minYear]->(b:Person) | r.since] AS years
Table 29. Result
years

[1999]

Rows: 1