Pipelining Queries

Using WITH for aggregation

A very common use of WITH is to aggregate data so that intermediate results can be used to create the final returned data or to pass the intermediate results to the next part of the query.

Here is an example:

MATCH (:Movie {title: 'Toy Story'})-[:IN_GENRE]->(g:Genre)<-[:IN_GENRE]-(m)
WHERE m.imdbRating IS NOT NULL
WITH g.name AS genre,
count(m) AS moviesInCommon,
sum(m.imdbRating) AS total
RETURN genre, moviesInCommon,
total/moviesInCommon AS score
ORDER By score DESC

In this query we are counting the number of movies that share the same Genre node. We use count() to count the number of rows and sum() to total the imdbRating for each movie for the Genre. These values are calculated as part of the aggregation and then used to return the data. In the WITH clause we pass on only the values we need to return a row for the Genre.

Here is another example that shows aggregation and pipelining:

MATCH (u:User {name: "Misty Williams"})-[r:RATED]->(:Movie)
WITH u, avg(r.rating) AS average
MATCH (u)-[r:RATED]->(m:Movie)
WHERE r.rating > average
RETURN average , m.title AS movie,
r.rating as rating
ORDER BY rating DESC

For this query, we first calculate the average rating for all movies that Misty Williams rated. Then we use this calculated value, average as a test for the second MATCH.

Using WITH for collecting

Another common use for the WITH clause is to collect results into a list that will be returned:

MATCH (m:Movie)--(a:Actor)
WHERE m.title CONTAINS 'New York'
WITH m, collect (a.name) AS actors,
count(*) AS numActors
RETURN m.title AS movieTitle, actors
ORDER BY numActors DESC

This query collects the names of actors who acted in the movies containing the string 'New York'. This aggregation collects the names and totals the number of actors.

Here is another example where we perform a 2-step aggregation for collecting a list of maps:

MATCH (m:Movie)<-[:ACTED_IN]-(a:Actor)
WHERE m.title CONTAINS 'New York'
WITH m, collect (a.name) AS actors,
count(*) AS numActors
ORDER BY numActors DESC
RETURN collect(m { .title, actors, numActors }) AS movies

Using LIMIT early

A best practice is to execute queries that minimize the number of rows processed in the query. One way to do that is to limit early in the query. This also helps in reducing the number of properties loaded from the database too early.

For example, this query limits during the aggregation:

PROFILE MATCH (p:Person)-[:ACTED_IN]->(m:Movie)
WHERE p.born.year = 1980
WITH p,
collect(m.title) AS movies LIMIT 3
RETURN p.name AS actor, movies

A better way to do this query is to limit early. This query will perform slightly better because LIMIT is moved up in the query:

PROFILE MATCH (p:Actor)
WHERE p.born.year = 1980
WITH p  LIMIT 3
MATCH (p)-[:ACTED_IN]->(m:Movie)
WITH p, collect(m.title) AS movies
RETURN p.name AS actor,  movies

Use DISTINCT when necessary

Here is an example of a query that retrieves three actors, then collects the names of the genres for the movies that actor acted in.

MATCH (p:Actor)
WHERE p.born.year = 1980
WITH p  LIMIT 3
MATCH (p)-[:ACTED_IN]->(m:Movie)-[:IN_GENRE]->(g:Genre)
WITH p, collect(g.name) AS genres
RETURN p.name AS actor, genres

Notice that for this query, the collected genre names are repeated. You would want to ensure that they are not duplicated as follows:

MATCH (p:Actor)
WHERE p.born.year = 1980
WITH p  LIMIT 3
MATCH (p)-[:ACTED_IN]->(m:Movie)-[:IN_GENRE]->(g:Genre)
WITH p, collect(DISTINCT g.name) AS genres
RETURN p.name AS actor, genres