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:

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

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

\t

Tab

\\t

\t

Tab

\b

Backspace

\\b

\b

Word boundary

\n

Newline

NewLine

\\n

\n

Newline

\r

Carriage return

\\r

\r

Carriage return

\f

Form feed

\\f

\f

Form feed

\'

Single quote

\"

Double quote

\\

Backslash

\\\

\\

Backslash

\uxxxx

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`

Was this page helpful?

WHERE

Introduction

Example graph

Basic usage

Node pattern predicates

Boolean operations

Filter on node label

Filter on node property

Filter on relationship property

Filter on dynamically-computed node property

Property existence checking

Using WITH

String matching

Prefix string search using STARTS WITH

Suffix string search using ENDS WITH

Substring search using CONTAINS

String matching negation

Regular expressions

Matching using regular expressions

Escaping in regular expressions

Case-insensitive regular expressions

Using path patterns in WHERE

Filter on patterns

Filter on patterns using NOT

Filter on patterns with properties

Filter on relationship type

Lists

IN operator

Missing properties and values

Default to false if property is missing

Default to true if property is missing

Filter on null

Using ranges

Simple range

Composite range

Pattern element predicates

Relationship pattern predicates

Using `WITH`

Prefix string search using `STARTS WITH`

Suffix string search using `ENDS WITH`

Substring search using `CONTAINS`

Using path patterns in `WHERE`

Filter on patterns using `NOT`

`IN` operator

Default to `false` if property is missing

Default to `true` if property is missing

Filter on `null`