Composing large statements

Example graph

We continue using the same example data as before:

CREATE (matrix:Movie {title: 'The Matrix', released: 1997})
CREATE (cloudAtlas:Movie {title: 'Cloud Atlas', released: 2012})
CREATE (forrestGump:Movie {title: 'Forrest Gump', released: 1994})
CREATE (keanu:Person {name: 'Keanu Reeves', born: 1964})
CREATE (robert:Person {name: 'Robert Zemeckis', born: 1951})
CREATE (tom:Person {name: 'Tom Hanks', born: 1956})
CREATE (tom)-[:ACTED_IN {roles: ['Forrest']}]->(forrestGump)
CREATE (tom)-[:ACTED_IN {roles: ['Zachry']}]->(cloudAtlas)
CREATE (robert)-[:DIRECTED]->(forrestGump)

This is the resulting graph:

cypher intro large statements01

UNION

If you want to combine the results of two statements that have the same result structure, you can use UNION [ALL].

For example, the following statement lists both actors and directors:

MATCH (actor:Person)-[r:ACTED_IN]->(movie:Movie)
RETURN actor.name AS name, type(r) AS type, movie.title AS title
UNION
MATCH (director:Person)-[r:DIRECTED]->(movie:Movie)
RETURN director.name AS name, type(r) AS type, movie.title AS title
Rows: 3

+-------------------------------------------------+
| name              | type       | title          |
+-------------------------------------------------+
| 'Tom Hanks'       | 'ACTED_IN' | 'Cloud Atlas'  |
| 'Tom Hanks'       | 'ACTED_IN' | 'Forrest Gump' |
| 'Robert Zemeckis' | 'DIRECTED' | 'Forrest Gump' |
+-------------------------------------------------+

Note that the returned columns must be aliased in the same way in all the sub-clauses.

The query above is equivalent to this more compact query:

MATCH (actor:Person)-[r:ACTED_IN|DIRECTED]->(movie:Movie)
RETURN actor.name AS name, type(r) AS type, movie.title AS title

WITH

In Cypher it is possible to chain fragments of statements together, similar to how it is done within a data-flow pipeline. Each fragment works on the output from the previous one, and its results can feed into the next one. Only columns declared in the WITH clause are available in subsequent query parts.

The WITH clause is used to combine the individual parts and declare which data flows from one to the other. WITH is similar to the RETURN clause. The difference is that the WITH clause does not finish the query, but prepares the input for the next part. Expressions, aggregations, ordering and pagination can be used in the same way as in the RETURN clause. The only difference is all columns must be aliased.

In the example below, we collect the movies someone appeared in, and then filter out those which appear in only one movie:

MATCH (person:Person)-[:ACTED_IN]->(m:Movie)
WITH person, count(*) AS appearances, collect(m.title) AS movies
WHERE appearances > 1
RETURN person.name, appearances, movies
Rows: 1

+-------------------------------------------------------------+
| person.name | appearances | movies                          |
+-------------------------------------------------------------+
| 'Tom Hanks' | 2           | ['Cloud Atlas', 'Forrest Gump'] |
+-------------------------------------------------------------+

Using the WITH clause you can pass values from one section of a query to another. This allows you to execute some intermediate calculations or operations within your query to use later.

You must specify the variables in the WITH clause that you want to use later. Only those variables will be passed to the next part of the query. There are a variety of ways to use this functionality (e.g. count, collect, filter, limit results), but we will show a couple, including a cleaner version of our size() query from above (see Counting values in a list). For more information and ways to use WITH, check out the Cypher Manual section.

//Query7: find and list the technologies people like
MATCH (a:Person)-[r:LIKES]-(t:Technology)
WITH a.name AS name, collect(t.type) AS technologies
RETURN name, technologies;
Query7 Results:

cypher results with

//Query8: find number of friends who have other friends - cleaner Query6
MATCH (p:Person)-[:IS_FRIENDS_WITH]->(friend:Person)
WITH p, collect(friend.name) AS friendsList, size((friend)-[:IS_FRIENDS_WITH]-(:Person)) AS numberOfFoFs
WHERE numberOfFoFs > 1
RETURN p.name, friendsList, numberOfFoFs;
Query8 Results:

cypher results with filter

In the first query, we pass the Person name, and a collected list of the Technology types. Only these items can be referenced in the RETURN clause. We cannot use the relationship (r) or even the Person birthdate because we did not pass those values along.

In the second query, we can only reference p and any of its properties (name, birthdate, yrsExperience, twitter), the collection of friends (as a whole, not each value), and the number of friend-of-friends. Since we passed those values in the WITH clause, we can use those in our WHERE or RETURN clauses.

WITH requires all values passed to have a variable (if they do not already have one). Our Person nodes were given a variable (p) in the MATCH clause, so we do not need to assign a variable there.

WITH is also very helpful for setting up parameters before the query. Often useful for parameter keys, url strings, and other query variables when importing data.

//find people with 2-6 years of experience
WITH 2 AS experienceMin, 6 AS experienceMax
MATCH (p:Person)
WHERE experienceMin <= p.yrsExperience <= experienceMax
RETURN p