Many, many years ago, I requested for the Cypher UNION clause in Cypher and Andres Taylor graciously added it. This was followed by the request for Post-Union Processing by Aseem Kishore, and it began to collect a whopping 99 comments over the course of 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 who 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.firstname
There 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.firstname
would 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 that work with those that Julie knows
- People that 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 ORDERBY name
Let’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 count
We 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.firstnameOther 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)RETURNmUNIONMATCH(n)-[:KNOWS]-()-[:WORKS_WITH]-(m)RETURNmUNIONMATCH(city)<-[:LIVES_IN]-(m)-[:TAGS]->(n)RETURNm}RETURNm.firstname
This 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
nin the subqueries are not 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 (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.firstname
will fail with
Variable `m` already declared Note that enclosing queries are allowed, just uncorrelated with the subquery. For example:
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.firstname
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!
