WITH
The WITH
clause allows query parts to be chained together, piping the results from one to be used as starting points or criteria in the next.
It is important to note that |
Using WITH
, you can manipulate the output before it is passed on to the following query parts.
Manipulations can be done to the shape and/or number of entries in the result set.
One common usage of WITH
is to limit the number of entries passed on to other MATCH
clauses.
By combining ORDER BY
and LIMIT
, it is possible to get the top X entries by some criteria and then bring in additional data from the graph.
WITH
can also be used to introduce new variables containing the results of expressions for use in the following query parts (see Introducing variables for expressions).
For convenience, the wildcard *
expands to all variables that are currently in scope and carries them over to the next query part (see Using the wildcard to carry over variables).
Another use is to filter on aggregated values.
WITH
is used to introduce aggregates which can then be used in predicates in WHERE
.
These aggregate expressions create new bindings in the results.
WITH
is also used to separate reading from updating of the graph.
Every part of a query must be either read-only or write-only.
When going from a writing part to a reading part, the switch must be done with a WITH
clause.
Introducing variables for expressions
You can introduce new variables for the result of evaluating expressions.
MATCH (george {name: 'George'})<--(otherPerson)
WITH otherPerson, toUpper(otherPerson.name) AS upperCaseName
WHERE upperCaseName STARTS WITH 'C'
RETURN otherPerson.name
This query returns the name of persons connected to 'George' whose name starts with a C
, regardless of capitalization.
otherPerson.name |
---|
|
|
Using the wildcard to carry over variables
You can use the wildcard *
to carry over all variables that are in scope, in addition to introducing new variables.
MATCH (person)-[r]->(otherPerson)
WITH *, type(r) AS connectionType
RETURN person.name, otherPerson.name, connectionType
This query returns the names of all related persons and the type of relationship between them.
person.name | otherPerson.name | connectionType |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Filter on aggregate function results
Aggregated results have to pass through a WITH
clause to be able to filter on.
MATCH (david {name: 'David'})--(otherPerson)-->()
WITH otherPerson, count(*) AS foaf
WHERE foaf > 1
RETURN otherPerson.name
The name of the person connected to 'David' with the at least more than one outgoing relationship will be returned by the query.
otherPerson.name |
---|
|
|
Sort results before using collect on them
You can sort your results before passing them to collect, thus sorting the resulting list.
MATCH (n)
WITH n
ORDER BY n.name DESC
LIMIT 3
RETURN collect(n.name)
A list of the names of people in reverse order, limited to 3, is returned in a list.
collect(n.name) |
---|
|
|
Limit branching of a path search
You can match paths, limit to a certain number, and then match again using those paths as a base, as well as any number of similar limited searches.
MATCH (n {name: 'Anders'})--(m)
WITH m
ORDER BY m.name DESC
LIMIT 1
MATCH (m)--(o)
RETURN o.name
Starting at 'Anders', find all matching nodes, order by name descending and get the top result, then find all the nodes connected to that top result, and return their names.
o.name |
---|
|
|
|
Limit and Filtering
It is possible to limit and filter on the same WITH
clause.
Note that the LIMIT
clause is applied before the WHERE
clause.
UNWIND [1, 2, 3, 4, 5, 6] AS x
WITH x
LIMIT 5
WHERE x > 2
RETURN x
The limit is first applied, reducing the rows to the first 5 items in the list. The filter is then applied, reducing the final result as seen below:
x |
---|
|
|
|
|
If the desired outcome is to filter and then limit, the filtering needs to occur in its own step:
Query
UNWIND [1, 2, 3, 4, 5, 6] AS x
WITH x
WHERE x > 2
WITH x
LIMIT 5
RETURN x
This time the filter is applied first, reducing the rows to consist of the list [3, 4, 5, 6]
.
Then the limit is applied.
As the limit is larger than the total number of remaining rows, all rows are returned.
x |
---|
|
|
|
|
|