11.3. Where

WHERE adds constraints to the patterns in a MATCH or OPTIONAL MATCH clause or filters the results of a WITH clause.

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

In the case of WITH and START, 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.

[Important]Important

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.

Figure 11.5. Graph

cypher-where-graph.svg

Basic usage

Boolean operations

You can use the expected boolean operators AND and OR, and also the boolean function NOT. See Section 9.8, “Working with NULL” for more information on how this works with NULL.

Query  

MATCH (n)
WHERE n.name = 'Peter' XOR (n.age < 30 AND n.name = "Tobias") OR NOT (n.name = "Tobias" OR
  n.name="Peter")
RETURN n

Result

n
3 rows

Node[0]{name:"Andres",age:36,belt:"white"}

Node[1]{address:"Sweden/Malmo",name:"Tobias",age:25}

Node[2]{email:"peter_n@example.com",name:"Peter",age:34}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (n) where n.name = 'Peter' xor (n.age < 30 and n.name = "Tobias") or not (n.name = "Tobias" or n.name="Peter") return n

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

The "Andres" node will be returned.

Result

n
1 row

Node[0]{name:"Andres",age:36,belt:"white"}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (n) where n:Swedish return n

Filter on node property

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

Query  

MATCH (n)
WHERE n.age < 30
RETURN n

"Tobias" is returned because he is younger than 30.

Result

n
1 row

Node[1]{address:"Sweden/Malmo",name:"Tobias",age:25}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (n) where n.age < 30 return n

Filter on relationship property

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

Query  

MATCH (n)-[k:KNOWS]->(f)
WHERE k.since < 2000
RETURN f

"Peter" is returned because Andres knows him since before 2000.

Result

f
1 row

Node[2]{email:"peter_n@example.com",name:"Peter",age:34}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (n)-[k:KNOWS]->(f) where k.since < 2000 return f

Filter on dynamic node property

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

Parameters  

{
  "prop" : "AGE"
}

Query  

MATCH (n)
WHERE n[toLower({ prop })]< 30
RETURN n

"Tobias" is returned because he is younger than 30.

Result

n
1 row

Node[1]{address:"Sweden/Malmo",name:"Tobias",age:25}

Property exists

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

Query  

MATCH (n)
WHERE exists(n.belt)
RETURN n

"Andres" will be returned because he is the only one with a belt property.

[Important]Important

The HAS() function has been superseded by EXISTS() and has been removed.

Result

n
1 row

Node[0]{name:"Andres",age:36,belt:"white"}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (n) where exists(n.belt) return n

String matching

The start and end of strings can be matched using STARTS WITH and ENDS WITH. To match regardless of location in a string, use CONTAINS. The matching is case-sensitive.

Match the start of a string

The STARTS WITH operator is used to perform case-sensitive matching on the start of strings.

Query  

MATCH (n)
WHERE n.name STARTS WITH 'Pet'
RETURN n

"Peter" will be returned because his name starts with Pet.

Result

n
1 row

Node[2]{email:"peter_n@example.com",name:"Peter",age:34}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (n) where n.name STARTS WITH 'Pet' return n

Match the end of a string

The ENDS WITH operator is used to perform case-sensitive matching on the end of strings.

Query  

MATCH (n)
WHERE n.name ENDS WITH 'ter'
RETURN n

"Peter" will be returned because his name ends with ter.

Result

n
1 row

Node[2]{email:"peter_n@example.com",name:"Peter",age:34}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (n) where n.name ENDS WITH 'ter' return n

Match anywhere in a string

The CONTAINS operator is used to perform case-sensitive matching regardless of location in strings.

Query  

MATCH (n)
WHERE n.name CONTAINS 'ete'
RETURN n

"Peter" will be returned because his name contains ete.

Result

n
1 row

Node[2]{email:"peter_n@example.com",name:"Peter",age:34}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (n) where n.name CONTAINS 'ete' return n

String matching negation

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

Query  

MATCH (n)
WHERE NOT n.name ENDS WITH 's'
RETURN n

"Peter" will be returned because his name does not end with s.

Result

n
1 row

Node[2]{email:"peter_n@example.com",name:"Peter",age:34}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (n) where NOT n.name ENDS WITH 's' return n

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 start 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.

Regular expressions

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

Query  

MATCH (n)
WHERE n.name =~ 'Tob.*'
RETURN n

"Tobias" is returned because his name starts with Tob.

Result

n
1 row

Node[1]{address:"Sweden/Malmo",name:"Tobias",age:25}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (n) where n.name =~ 'Tob.*' return n

Escaping in regular expressions

If you need a forward slash inside of your regular expression, escape it. Remember that back slash needs to be escaped in string literals.

Query  

MATCH (n)
WHERE n.address =~ 'Sweden\\/Malmo'
RETURN n

"Tobias" is returned because his address is in Sweden/Malmo.

Result

n
1 row

Node[1]{address:"Sweden/Malmo",name:"Tobias",age:25}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (n) where n.address =~ 'Sweden\\/Malmo' return n

Case insensitive regular expressions

By pre-pending a regular expression with (?i), the whole expression becomes case insensitive.

Query  

MATCH (n)
WHERE n.name =~ '(?i)ANDR.*'
RETURN n

"Andres" is returned because his name starts with ANDR regardless of case.

Result

n
1 row

Node[0]{name:"Andres",age:36,belt:"white"}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (n) where n.name =~ '(?i)ANDR.*' return n

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 can not 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 can not introduce new variables here. Although it might look very similar to the MATCH patterns, the WHERE clause is all about eliminating matched subgraphs. MATCH (a)-[*]->(b) is very different from WHERE (a)-[*]->(b); the first will produce a subgraph for every path it can find between a and b, and the latter will eliminate any matched subgraphs where a and b do not have a directed relationship chain between them.

Query  

MATCH (tobias { name: 'Tobias' }),(others)
WHERE others.name IN ['Andres', 'Peter'] AND (tobias)<--(others)
RETURN others

Nodes that have an outgoing relationship to the "Tobias" node are returned.

Result

others
1 row

Node[0]{name:"Andres",age:36,belt:"white"}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (tobias {name: 'Tobias'}), (others) where others.name IN ['Andres', 'Peter'] and (tobias)<--(others) return others

Filter on patterns using NOT

The NOT function can be used to exclude a pattern.

Query  

MATCH (persons),(peter { name: 'Peter' })
WHERE NOT (persons)-->(peter)
RETURN persons

Nodes that do not have an outgoing relationship to the "Peter" node are returned.

Result

persons
2 rows

Node[1]{address:"Sweden/Malmo",name:"Tobias",age:25}

Node[2]{email:"peter_n@example.com",name:"Peter",age:34}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; MATCH (persons), (peter {name: 'Peter'}) where not (persons)-->(peter) return persons

Filter on patterns with properties

You can also add properties to your patterns:

Query  

MATCH (n)
WHERE (n)-[:KNOWS]-({ name:'Tobias' })
RETURN n

Finds all nodes that have a KNOWS relationship to a node with the name "Tobias".

Result

n
1 row

Node[0]{name:"Andres",age:36,belt:"white"}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (n) where (n)-[:KNOWS]-({name:'Tobias'}) return n

Filtering 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)-[r]->()
WHERE n.name='Andres' AND type(r)=~ 'K.*'
RETURN r

This returns relationships that has a type whose name starts with K.

Result

r
2 rows

:KNOWS[1]{since:1999}

:KNOWS[0]{since:2012}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (n)-[r]->() where n.name='Andres' and type(r) =~ 'K.*' return r

Lists

IN operator

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

Query  

MATCH (a)
WHERE a.name IN ["Peter", "Tobias"]
RETURN a

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

Result

a
2 rows

Node[1]{address:"Sweden/Malmo",name:"Tobias",age:25}

Node[2]{email:"peter_n@example.com",name:"Peter",age:34}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (a) where a.name IN ["Peter", "Tobias"] return a

Missing properties and values

Default to false if property is missing

As missing properties evaluate to NULL, the comparision in the example will evaluate to FALSE for nodes without the belt property.

Query  

MATCH (n)
WHERE n.belt = 'white'
RETURN n

Only nodes with white belts are returned.

Result

n
1 row

Node[0]{name:"Andres",age:36,belt:"white"}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (n) where n.belt = 'white' return n

Default to true if property is missing

If you want to compare a property on a graph element, but only if it exists, you can compare the property against both the value you are looking for and NULL, like:

Query  

MATCH (n)
WHERE n.belt = 'white' OR n.belt IS NULL RETURN n
ORDER BY n.name

This returns all nodes, even those without the belt property.

Result

n
3 rows

Node[0]{name:"Andres",age:36,belt:"white"}

Node[2]{email:"peter_n@example.com",name:"Peter",age:34}

Node[1]{address:"Sweden/Malmo",name:"Tobias",age:25}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (n) where n.belt = 'white' or n.belt IS NULL return n order by n.name

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, with IS NULL. Also like SQL, the negative is IS NOT NULL, although NOT(IS NULL x) also works.

Query  

MATCH (person)
WHERE person.name = 'Peter' AND person.belt IS NULL RETURN person

Nodes that have name Peter but no belt property are returned.

Result

person
1 row

Node[2]{email:"peter_n@example.com",name:"Peter",age:34}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (person) where person.name = 'Peter' AND person.belt is null return person

Using ranges

Simple range

To check for an element being inside a specific range, use the inequality operators <, <=, >=, >.

Query  

MATCH (a)
WHERE a.name >= 'Peter'
RETURN a

Nodes having a name property lexicographically greater than or equal to Peter are returned.

Result

a
2 rows

Node[1]{address:"Sweden/Malmo",name:"Tobias",age:25}

Node[2]{email:"peter_n@example.com",name:"Peter",age:34}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (a) where a.name >= 'Peter' return a

Composite range

Several inequalities can be used to construct a range.

Query  

MATCH (a)
WHERE a.name > 'Andres' AND a.name < 'Tobias'
RETURN a

Nodes having a name property lexicographically between Andres and Tobias are returned.

Result

a
1 row

Node[2]{email:"peter_n@example.com",name:"Peter",age:34}

Try this query live create (_0:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create (_1 {`address`:"Sweden/Malmo", `age`:25, `name`:"Tobias"}) create (_2 {`age`:34, `email`:"peter_n@example.com", `name`:"Peter"}) create (_0)-[:`KNOWS` {`since`:1999}]->(_2) create (_0)-[:`KNOWS` {`since`:2012}]->(_1) ; match (a) where a.name > 'Andres' AND a.name < 'Tobias' return a