WHERE

Introduction

WHERE is not a clause in its own right — rather, it’s part of MATCH, OPTIONAL MATCH and WITH.

In the case of WITH, WHERE simply filters the results.

For MATCH and OPTIONAL MATCH on the other hand, WHERE adds constraints to the patterns described. It should not be seen as a filter after the matching is finished.

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 the WHERE is put inside the wrong MATCH clause.

Indexes may be used to optimize queries using WHERE in a variety of cases.

The following graph is used for the examples below:

Graph
  N0 [
    label = "{Swedish, Person|belt = \'white\'\lname = \'Andy\'\lage = 36\l}"
  ]
  N0 -> N3 [
    color = "#2e3436"
    fontcolor = "#2e3436"
    label = "HAS_DOG\nsince = 2016\l"
  ]
  N0 -> N2 [
    color = "#4e9a06"
    fontcolor = "#4e9a06"
    label = "KNOWS\nsince = 1999\l"
  ]
  N0 -> N1 [
    color = "#4e9a06"
    fontcolor = "#4e9a06"
    label = "KNOWS\nsince = 2012\l"
  ]
  N1 [
    label = "{Person|address = \'Sweden/Malmo\'\lname = \'Timothy\'\lage = 25\l}"
  ]
  N2 [
    label = "{Person|email = \'peter_n@example.com\'\lname = \'Peter\'\lage = 35\l}"
  ]
  N2 -> N5 [
    color = "#2e3436"
    fontcolor = "#2e3436"
    label = "HAS_DOG\nsince = 2018\l"
  ]
  N2 -> N4 [
    color = "#2e3436"
    fontcolor = "#2e3436"
    label = "HAS_DOG\nsince = 2010\l"
  ]
  N3 [
    label = "{Dog|name = \'Andy\'\l}"
  ]
  N4 [
    label = "{Dog|name = \'Fido\'\l}"
  ]
  N4 -> N6 [
    color = "#a40000"
    fontcolor = "#a40000"
    label = "HAS_TOY\n"
  ]
  N5 [
    label = "{Dog|name = \'Ozzy\'\l}"
  ]
  N6 [
    label = "{Toy|name = \'Banana\'\l}"
  ]

Basic usage

Boolean operations

You can use the boolean operators AND, OR, XOR and NOT. See Working with null for more information on how this works 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, n.age
Table 1. Result
n.name n.age

"Andy"

36

"Timothy"

25

"Peter"

35

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 for the 'Andy' node will be returned.

Table 2. 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 the 'Timothy' node are returned because he is less than 30 years of age.

Table 3. 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 the 'Peter' node are returned because Andy has known him since before 2000.

Table 4. 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 the 'Timothy' node are returned because he is less than 30 years of age.

Table 5. Result
n.name n.age

"Timothy"

25

Rows: 1

Property existence checking

Use the exists() function to only include nodes or relationships in which a property exists.

Query
MATCH (n:Person)
WHERE exists(n.belt)
RETURN n.name, n.belt

The name and belt for the 'Andy' node are returned because he is the only one with a belt property.

The has() function has been superseded by exists() and has been removed.
Table 6. Result
n.name n.belt

"Andy"

"white"

Rows: 1

String matching

The prefix and suffix of a string can be matched using STARTS WITH and ENDS WITH. To undertake a substring search - i.e. match regardless of 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 for the 'Peter' node are returned because his name starts with 'Pet'.

Table 7. 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 for the 'Peter' node are returned because his name ends with 'ter'.

Table 8. 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 the 'Peter' node are returned because his name contains with 'ete'.

Table 9. 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 for the 'Peter' node are returned because his name does not end with 'y'.

Table 10. 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 example MATCH (n) WHERE n.name =~ '(?i)Lon.*' RETURN n will return nodes with name 'London' or with name 'LonDoN'.

Matching using regular expressions

You can match on regular expressions by using =~ 'regexp', like this:

Query
MATCH (n:Person)
WHERE n.name =~ 'Tim.*'
RETURN n.name, n.age

The name and age for the 'Timothy' node are returned because his name starts with 'Tim'.

Table 11. 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 for the 'Peter' node are returned because his email ends with '.com'.

Table 12. Result
n.name n.age n.email

"Peter"

35

"peter_n@example.com"

Rows: 1

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 the 'Andy' node are returned because his name starts with 'AND' irrespective of casing.

Table 13. 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 represents true.

So, patterns are not only expressions, they are also predicates. The only limitation to your pattern is that you must be able to express it in a single path. You cannot use commas between multiple paths like you do in MATCH. You can achieve the same effect by combining multiple patterns with AND.

Note that you cannot introduce new variables here. Although it might look very similar to the MATCH patterns, the WHERE clause is all about eliminating 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 for nodes that have an outgoing relationship to the 'Timothy' node are returned.

Table 14. 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

Name and age values for nodes that do not have an outgoing relationship to the 'Peter' node are returned.

Table 15. Result
person.name person.age

"Timothy"

25

"Peter"

35

Rows: 2

Filter on patterns with properties

You can also add properties to your patterns:

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

Finds all name and age values for nodes that have a KNOWS relationship to a node with the name 'Timothy'.

Table 16. Result
n.name n.age

"Andy"

36

Rows: 1

Filter on relationship type

You can put the exact relationship type in the MATCH pattern, but sometimes you want to be able to do more advanced filtering on the type. You can use 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 17. Result
type(r) r.since

"KNOWS"

1999

"KNOWS"

2012

Rows: 2

An existential subquery can be used to find out if a specified pattern exists at least once in the data. It can be used in the same way as a path pattern but it allows you to use MATCH and WHERE clauses internally. A subquery has a scope, as indicated by the opening and closing braces, { and }. Any variable that is defined in the outside scope can be referenced inside the subquery’s own scope. Variables introduced inside the subquery are not part of the outside scope and therefore can’t be accessed on the outside. If the subquery evaluates even once to anything that is not null, the whole expression will become true. This also means that the system only needs to calculate the first occurrence where the subquery evaluates to something that is not null and can skip the rest of the work.

Syntax:

EXISTS {
  MATCH [Pattern]
  WHERE [Expression]
}

It is worth noting that the MATCH keyword can be omitted in subqueries and that the WHERE clause is optional.

Using existential subqueries in WHERE

Simple existential subquery

Variables introduced by the outside scope can be used in the inner MATCH clause. The following example shows this:

Query
MATCH (person:Person)
WHERE EXISTS {
  MATCH (person)-[:HAS_DOG]->(:Dog)
}
RETURN person.name AS name
Table 18. Result
name

"Andy"

"Peter"

Rows: 2

Existential subquery with WHERE clause

A WHERE clause can be used in conjunction to the MATCH. Variables introduced by the MATCH clause and the outside scope can be used in this scope.

Query
MATCH (person:Person)
WHERE EXISTS {
  MATCH (person)-[:HAS_DOG]->(dog:Dog)
  WHERE person.name = dog.name
}
RETURN person.name AS name
Table 19. Result
name

"Andy"

Rows: 1

Nesting existential subqueries

Existential subqueries can be nested like the following example shows. The nesting also affects the scopes. That means that it is possible to access all variables from inside the subquery which are either on the outside scope or defined in the very same subquery.

Query
MATCH (person:Person)
WHERE EXISTS {
  MATCH (person)-[:HAS_DOG]->(dog:Dog)
  WHERE EXISTS {
    MATCH (dog)-[:HAS_TOY]->(toy:Toy)
    WHERE toy.name = 'Banana'
  }
}
RETURN person.name AS name
Table 20. Result
name

"Peter"

Rows: 1

Lists

IN operator

To check if an element exists in a list, you can use the IN operator.

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

This query shows how to check if a property exists in a literal list.

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

If you want to compare a property on a node or relationship, but only if it exists, you can compare the property against both the value you are looking for and null, like:

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

Sometimes you might want to test if a value or a variable is null. This is done just like SQL does it, using IS NULL. Also like SQL, the negative is IS NOT NULL, although 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 for an element being inside 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 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