Neo4j 4: Post-Union Processing Explained

Luanne Misquitta

by Luanne Misquitta

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.

Post-Union Processing with Cypher in Neo4j 4

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 ORDER BY 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.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.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 n in 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 (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 fail with

Variable `m` already declared

Note 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!

Share this blog post:

comments powered by Disqus