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 WITH affects variables in scope. Any variables not included in the WITH clause are not carried over to the rest of the query. The wildcard * can be used to include all variables that are currently in scope.

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.

graph with clause

Introducing variables for expressions

You can introduce new variables for the result of evaluating expressions.

Query

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.

Table 1. Result
otherPerson.name
`"Caesar"`
`Rows: 1`

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.

Query

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.

Table 2. Result
person.name	otherPerson.name	connectionType
`"David"`	`"Anders"`	`"KNOWS"`
`"Anders"`	`"Bossman"`	`"KNOWS"`
`"Anders"`	`"Caesar"`	`"BLOCKS"`
`"Bossman"`	`"David"`	`"BLOCKS"`
`"Bossman"`	`"George"`	`"KNOWS"`
`"Caesar"`	`"George"`	`"KNOWS"`
`Rows: 6`

Filter on aggregate function results

Aggregated results have to pass through a WITH clause to be able to filter on.

Query

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.

Table 3. Result
otherPerson.name
`"Anders"`
`Rows: 1`

Sort results before using collect on them

You can sort your results before passing them to collect, thus sorting the resulting list.

Query

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.

Table 4. Result
collect(n.name)
`["George","David","Caesar"]`
`Rows: 1`

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.

Query

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.

Table 5. Result
o.name
`"Anders"`
`"Bossman"`
`Rows: 2`

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.

Query

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:

Table 6. Result
x
`3`
`4`
`5`
`Rows: 3`

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.

Table 7. Result
x
`3`
`4`
`5`
`6`
`Rows: 4`