10.3. Where
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.
Note In the case of multiple ( |
Basic usage
Boolean operations
You can use the expected boolean operators AND
and OR
, and also the boolean function NOT
. See Section 8.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
This query shows how boolean operators can be used.
Result
n |
---|
3 rows |
|
|
|
Try this query live. create (_0 {`age`:25, `name`:"Tobias"}) create (_1 {`age`:34, `name`:"Peter"}) create (_2:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create _2-[:`KNOWS`]->_0 create _2-[:`KNOWS`]->_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 |
|
Try this query live. create (_0 {`age`:25, `name`:"Tobias"}) create (_1 {`age`:34, `name`:"Peter"}) create (_2:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create _2-[:`KNOWS`]->_0 create _2-[:`KNOWS`]->_1 match (n) where n:Swedish return n
Filter on node property
To filter on a property, write your clause after the WHERE
keyword. Filtering on relationship properties works just the same way.
Query.
MATCH (n) WHERE n.age < 30 RETURN n
The "Tobias
" node will be returned.
Result
n |
---|
1 row |
|
Try this query live. create (_0 {`age`:25, `name`:"Tobias"}) create (_1 {`age`:34, `name`:"Peter"}) create (_2:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create _2-[:`KNOWS`]->_0 create _2-[:`KNOWS`]->_1 match (n) where n.age < 30 return n
Property exists
To only include nodes/relationships that have a property, use the HAS()
function and just write out the identifier and the property you expect it to have.
Query.
MATCH (n) WHERE HAS (n.belt) RETURN n
The node named "Andres
" is returned.
Result
n |
---|
1 row |
|
Try this query live. create (_0 {`age`:25, `name`:"Tobias"}) create (_1 {`age`:34, `name`:"Peter"}) create (_2:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create _2-[:`KNOWS`]->_0 create _2-[:`KNOWS`]->_1 match (n) where has(n.belt) return n
Regular expressions
Regular expressions
You can match on regular expressions by using =~ "regexp"
, like this:
Query.
MATCH (n) WHERE n.name =~ 'Tob.*' RETURN n
The "Tobias
" node will be returned.
Result
n |
---|
1 row |
|
Try this query live. create (_0 {`age`:25, `name`:"Tobias"}) create (_1 {`age`:34, `name`:"Peter"}) create (_2:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create _2-[:`KNOWS`]->_0 create _2-[:`KNOWS`]->_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.name =~ 'Some\/thing' RETURN n
No nodes match this regular expression.
Result
n |
---|
0 row |
|
Try this query live. create (_0 {`age`:25, `name`:"Tobias"}) create (_1 {`age`:34, `name`:"Peter"}) create (_2:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create _2-[:`KNOWS`]->_0 create _2-[:`KNOWS`]->_1 match (n) where n.name =~ 'Some\\/thing' 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
The node with name "Andres
" is returned.
Result
n |
---|
1 row |
|
Try this query live. create (_0 {`age`:25, `name`:"Tobias"}) create (_1 {`age`:34, `name`:"Peter"}) create (_2:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create _2-[:`KNOWS`]->_0 create _2-[:`KNOWS`]->_1 match (n) where n.name =~ '(?i)ANDR.*' return n
Using patterns in WHERE
Filter on patterns
Patterns are expressions in Cypher, expressions that return a collection of paths. Collection
expressions are also predicates — an empty collection 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 identifiers 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 |
|
Try this query live. create (_0 {`age`:25, `name`:"Tobias"}) create (_1 {`age`:34, `name`:"Peter"}) create (_2:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create _2-[:`KNOWS`]->_0 create _2-[:`KNOWS`]->_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 |
|
|
Try this query live. create (_0 {`age`:25, `name`:"Tobias"}) create (_1 {`age`:34, `name`:"Peter"}) create (_2:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create _2-[:`KNOWS`]->_0 create _2-[:`KNOWS`]->_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 |
|
Try this query live. create (_0 {`age`:25, `name`:"Tobias"}) create (_1 {`age`:34, `name`:"Peter"}) create (_2:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create _2-[:`KNOWS`]->_0 create _2-[:`KNOWS`]->_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 |
|
|
Try this query live. create (_0 {`age`:25, `name`:"Tobias"}) create (_1 {`age`:34, `name`:"Peter"}) create (_2:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create _2-[:`KNOWS`]->_0 create _2-[:`KNOWS`]->_1 match (n)-[r]->() where n.name='Andres' and type(r) =~ 'K.*' return r
Collections
IN operator
To check if an element exists in a collection, 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 collection.
Result
a |
---|
2 rows |
|
|
Try this query live. create (_0 {`age`:25, `name`:"Tobias"}) create (_1 {`age`:34, `name`:"Peter"}) create (_2:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create _2-[:`KNOWS`]->_0 create _2-[:`KNOWS`]->_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 the belt property are returned.
Result
n |
---|
1 row |
|
Try this query live. create (_0 {`age`:25, `name`:"Tobias"}) create (_1 {`age`:34, `name`:"Peter"}) create (_2:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create _2-[:`KNOWS`]->_0 create _2-[:`KNOWS`]->_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 |
|
|
|
Try this query live. create (_0 {`age`:25, `name`:"Tobias"}) create (_1 {`age`:34, `name`:"Peter"}) create (_2:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create _2-[:`KNOWS`]->_0 create _2-[:`KNOWS`]->_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 an identifier 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 |
|
Try this query live. create (_0 {`age`:25, `name`:"Tobias"}) create (_1 {`age`:34, `name`:"Peter"}) create (_2:`Swedish` {`age`:36, `belt`:"white", `name`:"Andres"}) create _2-[:`KNOWS`]->_0 create _2-[:`KNOWS`]->_1 match (person) where person.name = 'Peter' AND person.belt is null return person