Limiting a Neo4j cypher query results by sum of relationship property

Question

Is there a way to limit a cypher query by the sum of a relationship property?

I'm trying to create a cypher query that returns nodes that are within a distance of 100 of the start node. All the relationships have a distance set, the sum of all the distances in a path is the total distance from the start node.

If the WHERE clause could handle aggregate functions what I'm looking for might look like this

START n=node(1)
MATCH path = n-[rel:street*]-x
WHERE SUM( rel.distance ) < 100
RETURN x

Is there a way that I can sum the distances of the relationships in the path for the where clause?

Answer 1

Sure, what you want to do is like a having in a SQL query.

In cypher you can chain query segments and use the results of previous parts in the next part by using WITH, see the manual.

For your example one would assume:

START n=node(1)
MATCH n-[rel:street*]-x
WITH SUM(rel.distance) as distance
WHERE distance < 100
RETURN x

Unfortunately sum doesn't work with collections yet

So I tried to do it differently (for variable length paths):

START n=node(1)
MATCH n-[rel:street*]-x
WITH collect(rel.distance) as distances
WITH head(distances) + head(tail(distances)) + head(tail(tail(distances))) as distance
WHERE distance < 100
RETURN x

Unfortunately head of an empty list doesn't return null which could be coalesced to 0 but just fails. So this approach would only work for fixed length paths, don't know if that's working for you.

Answer 2

I've come across the same problem recently. In more recent versions of neo4j this was solved by the extract and reduce clauses. You could write:

START n=node(1)
MATCH path = (n)-[rel:street*..100]-(x)
WITH extract(x in rel | x.distance) as distances, x
WITH reduce(res = 0, x in rs | res + x) as distance, x
WHERE distance <100
RETURN x