WHERE clause
WHERE
isn’t usually considered a standalone clause but rather a part of the
MATCH
, OPTIONAL MATCH
and WITH
clauses.
When used next to the WITH
clause, the WHERE
clause only filters the
results, while when used with MATCH
and OPTIONAL MATCH
it adds constraints
to the described patterns.
WHERE
should be used directly after MATCH
or OPTIONAL MATCH
clauses in
order to avoid problems with performance or results.
- Basic usage
1.1. Boolean Operators
1.2. Inequality Operators Operators
1.3. Filter with node labels
1.4. Filter with node properties
1.5. Filter with relationship properties
1.6. Check if property is not null
1.7. Filter with pattern expressions - String matching
- Regular Expressions
- Existential subqueries
4.1. Basic EXISTS in WHERE
4.2. Negation with NOT EXISTS
4.3. When to use EXISTS instead of pattern expressions
4.4. RETURN in EXISTS subqueries
4.5. EXISTS with UNION
4.6. Outer scope variables and WITH
Dataset
The following examples are executed with this dataset. You can create this dataset locally by executing the queries at the end of the page: Dataset queries.
1. Basic Usage
1.1. Boolean Operators
Standard boolean operators like NOT
, AND
, OR
and XOR
can be used:
MATCH (c:Country)
WHERE c.language = 'English' AND c.continent = 'Europe'
RETURN c.name;
Output:
+----------------+
| c.name |
+----------------+
| United Kingdom |
+----------------+
1.2. Inequality Operators Operators
Standard inequality operators like <
, <=
, >
and >=
can be used:
MATCH (c:Country)
WHERE (c.population > 80000000)
RETURN c.name;
Output:
+---------+
| c.name |
+---------+
| Germany |
+---------+
1.3. Filter with node labels
Nodes can be filtered by their label using the WHERE
clause instead of specifying it directly in the MATCH
clause:
MATCH (c)
WHERE c:Country
RETURN c.name;
Output:
+----------------+
| c.name |
+----------------+
| Germany |
| France |
| United Kingdom |
+----------------+
1.4. Filter with node properties
Just as labels, node properties can be used in the WHERE clause to filter nodes:
MATCH (c:Country)
WHERE c.population < 70000000
RETURN c.name;
Output:
+----------------+
| c.name |
+----------------+
| France |
| United Kingdom |
+----------------+
1.5. Filter with relationship properties
Just as with node properties, relationship properties can be used as filters:
MATCH (:Country {name: 'United Kingdom'})-[r]-(p)
WHERE r.date_of_start = 2014
RETURN p;
Output:
+---------------------------+
| p |
+---------------------------+
| (:Person {name: "Harry"}) |
| (:Person {name: "Anna"}) |
+---------------------------+
1.6. Check if property is not null
To check if a node or relationship property exists use the IS NOT NULL
option:
MATCH (c:Country)
WHERE c.name = 'United Kingdom' AND c.population IS NOT NULL
RETURN c.name, c.population;
Output:
+----------------+----------------+
| c.name | c.population |
+----------------+----------------+
| United Kingdom | 66000000 |
+----------------+----------------+
1.7. Filter with pattern expressions
Currently, we support pattern expression filters with the exists(pattern)
function, which can perform filters based on neighboring entities:
MATCH (p:Person)
WHERE exists((p)-[:LIVING_IN]->(:Country {name: 'Germany'}))
RETURN p.name
ORDER BY p.name;
The exists()
function can be used only with the WHERE
clause.
Output:
+----------------+
| c.name |
+----------------+
| Anna |
| John |
+----------------+
2. String matching
Apart from comparison and concatenation operators Cypher provides special string operators for easier matching of substrings:
Operator | Description |
---|---|
a STARTS WITH b | Returns true if the prefix of string a is equal to string b. |
a ENDS WITH b | Returns true if the suffix of string a is equal to string b. |
a CONTAINS b | Returns true if some substring of string a is equal to string b. |
MATCH (c:Country)
WHERE c.name STARTS WITH 'G' AND NOT c.name CONTAINS 't'
RETURN c.name;
Output:
+---------+
| c.name |
+---------+
| Germany |
+---------+
3. Regular expressions
Inside WHERE
clause, you can use regular expressions for text filtering. To
use a regular expression, you need to use the =~
operator.
For example, finding all Person
nodes which have a name ending with a
:
MATCH (n:Person) WHERE n.name =~ ".*a$" RETURN n;
Output:
+--------------------------+
| n |
+--------------------------+
| (:Person {name: "Anna"}) |
+--------------------------+
The regular expression syntax is based on the modified ECMAScript regular expression grammar. The ECMAScript grammar can be found here, while the modifications are described in this document.
4. Existential subqueries
Existential subqueries allow you to use EXISTS { ... }
within WHERE
(or as a standalone expression) to test whether a subquery returns at least one row. The subquery can reference variables from the outer scope (correlated subquery), while variables created inside the subquery are not visible outside of it.
4.1. Basic EXISTS in WHERE
Return people who live in Germany:
MATCH (p:Person)
WHERE EXISTS {
MATCH (p)-[:LIVING_IN]->(:Country {name: 'Germany'})
}
RETURN p.name
ORDER BY p.name;
Output:
+---------+
| p.name |
+---------+
| Anna |
| John |
+---------+
4.2. Negation with NOT EXISTS
Return people who do not live in the United Kingdom:
MATCH (p:Person)
WHERE NOT EXISTS {
MATCH (p)-[:LIVING_IN]->(:Country {name: 'United Kingdom'})
}
RETURN p.name
ORDER BY p.name;
Output:
+---------+
| p.name |
+---------+
| John |
+---------+
4.3. When to use EXISTS instead of pattern expressions
Pattern expressions like exists( (p)-[:FRIENDS_WITH]-() )
are convenient for simple existence checks, but they cannot contain additional clauses such as WHERE
, WITH
/aggregation, or multiple pattern parts. EXISTS { ... }
supports this additional logic.
For example, return people who have at least two friendships (uses aggregation inside the subquery):
MATCH (p:Person)
WHERE EXISTS {
MATCH (p)-[:FRIENDS_WITH]-(:Person)
WITH count(*) AS friendsCount
WHERE friendsCount >= 2
}
RETURN p.name
ORDER BY p.name;
Output:
+---------+
| p.name |
+---------+
| Anna |
| Harry |
| John |
+---------+
You can also include property predicates on relationships inside the subquery. For example, people connected by a friendship that started before 2012:
MATCH (p:Person)
WHERE EXISTS {
MATCH (p)-[r:FRIENDS_WITH]-(:Person)
WHERE r.date_of_start < 2012
}
RETURN p.name
ORDER BY p.name;
Output:
+---------+
| p.name |
+---------+
| Harry |
| John |
+---------+
4.4. RETURN in EXISTS subqueries
EXISTS subqueries do not require a RETURN
clause. If one is present, it does not need to be aliased (unlike CALL
subqueries), and any variables returned within the EXISTS
subquery are not available after the subquery finishes.
MATCH (person:Person)
WHERE EXISTS {
MATCH (person)-[:LIVING_IN]->(country:Country)
RETURN country.name
}
RETURN person.name AS name
Output:
+---------+
| name |
+---------+
| Anna |
| Harry |
| John |
+---------+
4.5. EXISTS with UNION
EXISTS can be used with a UNION
clause, and the RETURN
clauses are not required. If one branch has a RETURN
clause, then all branches require one. If any UNION
branch returns at least one row, the entire EXISTS
expression evaluates to true
.
MATCH (person:Person)
WHERE EXISTS {
MATCH (person)-[:LIVING_IN]->(:Country {name: 'Germany'})
UNION
MATCH (person)-[:WORKING_IN]->(:Country {name: 'United Kingdom'})
}
RETURN person.name AS name
Output:
+---------+
| name |
+---------+
| Anna |
| Harry |
+---------+
4.6. Outer scope variables and WITH
Variables from the outside scope are visible for the entire subquery, even when using a WITH
clause. Shadowing these variables is not allowed. An outside scope variable is shadowed when a newly introduced variable within the inner scope is defined with the same name. The example below shadows the outer variable countryName
and will therefore throw an error.
WITH 'United Kingdom' as countryName
MATCH (person:Person)-[:LIVING_IN]->(c:Country {name: countryName})
WHERE EXISTS {
WITH "Germany" AS countryName
MATCH (person)-[:LIVING_IN]->(d:Country)
WHERE d.name = countryName
}
RETURN person.name AS name
+---------+
| name |
+---------+
| Anna |
| John |
+---------+
Dataset queries
We encourage you to try out the examples by yourself. You can get our dataset locally by executing the following query block.
MATCH (n) DETACH DELETE n;
CREATE (c1:Country {name: 'Germany', language: 'German', continent: 'Europe', population: 83000000});
CREATE (c2:Country {name: 'France', language: 'French', continent: 'Europe', population: 67000000});
CREATE (c3:Country {name: 'United Kingdom', language: 'English', continent: 'Europe', population: 66000000});
MATCH (c1),(c2)
WHERE c1.name= 'Germany' AND c2.name = 'France'
CREATE (c2)<-[:WORKING_IN {date_of_start: 2014}]-(p:Person {name: 'John'})-[:LIVING_IN {date_of_start: 2014}]->(c1);
MATCH (c)
WHERE c.name= 'United Kingdom'
CREATE (c)<-[:WORKING_IN {date_of_start: 2014}]-(p:Person {name: 'Harry'})-[:LIVING_IN {date_of_start: 2013}]->(c);
MATCH (p1),(p2)
WHERE p1.name = 'John' AND p2.name = 'Harry'
CREATE (p1)-[:FRIENDS_WITH {date_of_start: 2011}]->(p2);
MATCH (p1),(p2)
WHERE p1.name = 'John' AND p2.name = 'Harry'
CREATE (p1)<-[:FRIENDS_WITH {date_of_start: 2012}]-(:Person {name: 'Anna'})-[:FRIENDS_WITH {date_of_start: 2014}]->(p2);
MATCH (p),(c1),(c2)
WHERE p.name = 'Anna' AND c1.name = 'United Kingdom' AND c2.name = 'Germany'
CREATE (c2)<-[:LIVING_IN {date_of_start: 2014}]-(p)-[:LIVING_IN {date_of_start: 2014}]->(c1);
MATCH (n)-[r]->(m) RETURN n,r,m;