Many years ago, I requested the Cypher UNION clause, and Andres Taylor graciously added it. This was followed by Aseem Kishore’s request for Post-Union Processing, and it garnered a whopping 99 comments over time.
It is exciting to see support for a subset of subqueries in openCypher, i.e. uncorrelated subqueries in the soon-to-be-released Neo4j 4, bringing post-union processing finally to Cypher. Given its history, a short article is in order.
Union in 3.x
In pre-4x versions of Neo4j, UNION served to combine the results of 2 or more queries into one result set.
For example, to return the list of people whom Julie knows or works with, and the people who live in the same city as Julie and tag her, you could do:
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:KNOWS|WORKS_WITH]-(m)
RETURN DISTINCT m.firstname
UNION
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:LIVES_IN]->(c)
WITH n,c
MATCH (c)<-[:LIVES_IN]-(m)-[:TAGS]->(n)
RETURN DISTINCT m.firstnameThere was no way to order the entire result set, though, or limit it.
This query:
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:KNOWS|WORKS_WITH]-(m)
RETURN DISTINCT m.firstname
UNION
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:LIVES_IN]->(c)
WITH n,c
MATCH (c)<-[:LIVES_IN]-(m)-[:TAGS]->(n)
RETURN DISTINCT m.firstname
ORDER BY m.firstnamewould just order the results of the second part of the UNION, as it belonged there and not to the UNION as a whole. There was also no way to process the result set and filter it further, for example, or aggregate; this had to be done in your application.
Union and post-processing in 4x
Post-processing the results of UNIONs is now possible with uncorrelated subqueries. We’ll examine some examples and some things to bear in mind when using subqueries. A simple example, taken from the original feature request, is a query for:
- People that Julie knows or works with
- People who work with those whom Julie knows
- People who tag Julie and also live in the same city as her
- Ordered by name
CALL {
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:KNOWS|WORKS_WITH]-(m)
RETURN DISTINCT m.firstname AS name
UNION
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:KNOWS]-()-[:WORKS_WITH]-(m)
RETURN DISTINCT m.firstname AS name
UNION
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:LIVES_IN]->(c)
WITH n,c
MATCH (c)<-[:LIVES_IN]-(m)-[:TAGS]->(n)
RETURN DISTINCT m.firstname AS name
}
RETURN name ORDER BY nameLet’s see what a simple aggregation looks like:
CALL {
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:KNOWS|WORKS_WITH]-(m)
RETURN n,m
union
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:KNOWS]-()-[:WORKS_WITH]-(m)
RETURN n,m
UNION
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:LIVES_IN]->(c)
WITH n,c
MATCH (c)<-[:LIVES_IN]-(m)-[:TAGS]->(n)
RETURN n, m
}
RETURN n,COUNT(distinct m) AS countWe can also now use the result of the union in further matches. Here is a simple filtering example:
CALL {
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:KNOWS|WORKS_WITH]-(m)
RETURN DISTINCT m
UNION
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:KNOWS]-()-[:WORKS_WITH]-(m)
RETURN DISTINCT m
UNION
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:LIVES_IN]->(c)
WITH n,c
MATCH (c)<-[:LIVES_IN]-(m)-[:TAGS]->(n)
RETURN DISTINCT m
}
WITH m
MATCH (c:City {name:"Kimton"})
WHERE (m)-[:LIVES_IN]->(c)
RETURN m.firstname ORDER BY m.firstname
Other considerations
Now, if you decide to “optimise” and match Julie only once, beware!
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})
CALL {
MATCH (n)-[:KNOWS|:WORKS_WITH]-(m)
RETURN m
UNION
MATCH (n)-[:KNOWS]-()-[:WORKS_WITH]-(m)
RETURN m
UNION
MATCH (city)<-[:LIVES_IN]-(m)-[:TAGS]->(n)
RETURN m
}
RETURN m.firstnameThis does not do as you expect, because 4.0 does not support correlated subqueries, and the following restrictions apply:
- A subquery cannot refer to variables from the enclosing query. In the example above, the
nsubqueries are not for Julie at all, but every node in the graph is matched - A subquery cannot return variables with the same names as variables in the enclosing query
As such, the following query:
MATCH (m:Person)
CALL {
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:KNOWS|WORKS_WITH]-(m) RETURN DISTINCT m
UNION
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:KNOWS]-()-[:WORKS_WITH]-(m) RETURN DISTINCT m
UNION
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:LIVES_IN]->(c)
WITH n,c
MATCH (c)<-[:LIVES_IN]-(m)-[:TAGS]->(n)
RETURN DISTINCT m
}
RETURN m ORDER BY m.firstnamewill fail with
Variable `m` already declaredNote that enclosing queries are allowed, just uncorrelated with the subquery. For example:
MATCH (c:City)
CALL {
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:KNOWS|WORKS_WITH]-(m) RETURN DISTINCT m
UNION
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:KNOWS]-()-[:WORKS_WITH]-(m) RETURN DISTINCT m
UNION
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})-[:LIVES_IN]->(c)
WITH n,c
MATCH (c)<-[:LIVES_IN]-(m)-[:TAGS]->(n)
RETURN DISTINCT m
}
RETURN c,count(m)will return as many rows as there are cities- the subquery is evaluated for every incoming input row.
4.1 update
Neo4j 4.1 added support for correlated subqueries, so now a subquery can refer to variables from the enclosing query if they are explicitly imported, such as:
MATCH (n:Person {firstname:"Julie", lastname:"Corkery"})
CALL {
WITH n
MATCH (n)-[:KNOWS|WORKS_WITH]-(m) RETURN DISTINCT m
UNION
WITH n
MATCH (n)-[:KNOWS]-()-[:WORKS_WITH]-(m) RETURN DISTINCT m
UNION
WITH n
MATCH (n)-[:LIVES_IN]->(c)
WITH n,c
MATCH (c)<-[:LIVES_IN]-(m)-[:TAGS]->(n)
RETURN DISTINCT m
}
RETURN c,count(m)And there you have it, thanks to the Cypher team, post-union processing is now available in the brand new Neo4j 4 release!
