9.5. With
With WITH
, you can manipulate the result sequence before it is passed on to the following query parts. The
manipulations can be of the shape and/or number of entries in the result set.
One common usage of WITH
is to limit the number of entries that are then passed on to other MATCH
clauses.
By combining ORDER
BY
and LIMIT
, it’s possible to get the top X entries by some criteria, and then bring
in additional data from the graph.
Another use is to filter on aggregated values. WITH
is used to introduce aggregates which can then by used in
predicates in WHERE
. These aggregate expressions create new bindings in the results. WITH
can also, just like
RETURN
, alias expressions that are introduced into the results using the aliases as binding name.
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.
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
The person connected to David with the at least more than one outgoing relationship will be returned by the query.
Result
otherPerson |
---|
1 row |
|
Try this query live. create (_0 {`name`:"David"}) create (_1 {`name`:"Emil"}) create (_2 {`name`:"Anders"}) create (_3 {`name`:"Bossman"}) create (_4 {`name`:"Ceasar"}) create _0-[:`KNOWS`]->_2 create _2-[:`KNOWS`]->_3 create _2-[:`BLOCKS`]->_4 create _3-[:`KNOWS`]->_1 create _3-[:`BLOCKS`]->_0 create _4-[:`KNOWS`]->_1 match (david {name: "David"})--(otherPerson)-->() with otherPerson, count(*) as foaf where foaf > 1 return otherPerson
Sort results before using collect on them
You can sort your results before passing them to collect, thus sorting the resulting collection.
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, in a collection.
Result
collect(n.name) |
---|
1 row |
|
Try this query live. create (_0 {`name`:"David"}) create (_1 {`name`:"Emil"}) create (_2 {`name`:"Anders"}) create (_3 {`name`:"Bossman"}) create (_4 {`name`:"Ceasar"}) create _0-[:`KNOWS`]->_2 create _2-[:`KNOWS`]->_3 create _2-[:`BLOCKS`]->_4 create _3-[:`KNOWS`]->_1 create _3-[:`BLOCKS`]->_0 create _4-[:`KNOWS`]->_1 match (n) with n order by n.name desc limit 3 return collect(n.name)
Limit branching of your 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.
Result
o.name |
---|
2 rows |
|
|
Try this query live. create (_0 {`name`:"David"}) create (_1 {`name`:"Emil"}) create (_2 {`name`:"Anders"}) create (_3 {`name`:"Bossman"}) create (_4 {`name`:"Ceasar"}) create _0-[:`KNOWS`]->_2 create _2-[:`KNOWS`]->_3 create _2-[:`BLOCKS`]->_4 create _3-[:`KNOWS`]->_1 create _3-[:`BLOCKS`]->_0 create _4-[:`KNOWS`]->_1 match (n {name: "Anders"})--(m) with m order by m.name desc limit 1 match (m)--(o) return o.name