Neo4j Cypher, exclude relationship from counting in variable-length path

We can limit a variable-length FIRST|SECOND path using this pattern:

(a)-[r:FIRST|SECOND*..3]->(b)

But what is the best way to return FIRST|SECOND paths that contain up to 3 SECOND relationships?

This turned out to be a very hard problem. Your use case does not allow there to be an upper bound on the variable-length path pattern (which is normally best practice), because the first (or second, or third) SECOND relationship could appear after any number of relationships.

Although the following query does not impose any upper bounds, it also does not attempt to extend a subpath any further after finding each SECOND relationship. So it avoids needlessly searching to the end of every possible path. The FIRST*0.. syntax use a zero lower bound and means "match paths having 0 or more consecutive FIRST relationships".

This query is able to find all paths with up to 3 SECOND relationships by chaining together 3 separate subpath queries, and then using the APOC method apoc.path.combine to stitch together the subpaths into the final full paths. The NULL checks are used to ignore subpaths that found no match.

MATCH (a:Test {testid: 1})
OPTIONAL MATCH p1=(a)-[:FIRST*0..]->()-[:SECOND]->(b)
OPTIONAL MATCH p2=(b)-[:FIRST*0..]->()-[:SECOND]->(c)
OPTIONAL MATCH p3=(c)-[:FIRST*0..]->()-[:SECOND]->(d)
WITH p1, p3,
  CASE WHEN p2 IS NOT NULL THEN apoc.path.combine(p1, p2) END AS p12
WITH p1, p12,
  CASE WHEN p3 IS NOT NULL THEN apoc.path.combine(p12, p3) END AS p123
WITH (COLLECT(p1) + COLLECT(p12) + COLLECT(p123)) AS paths
UNWIND paths AS p
RETURN p

Caveat: this query is not efficient unless most paths contain at least 3 SECOND relationships. If that is not what your graph looks like, then create a new question providing details of your data model and how densely it is populated with SECOND nodes. Your data model may need to be modified.

There are two cases I can think of.

a. SECOND is the last relationship linked to b. Something like this:

(a)-[:FIRST]->()-[:SECOND]->()-[:SECOND]->()-[:SECOND]->(b)

For this, you can try this query:

MATCH path=(a)-[r:FIRST|SECOND*1..]->()-[:SECOND]->(b)
WHERE SIZE([r1 in relationships(path) WHERE type(r1) = 'SECOND' | r1]) = 3
RETURN path

In this query, I fix the last relationship linked to b is of type 'SECOND', and we check whether the count of SECOND relationships is 3.

b. Another case is, any relationship can be linked to b at the end of the path, provided you have 3 SECOND relations on the path. Something like this:

(a)-[:SECOND]->()-[:SECOND]->()-[:SECOND]->()-[:FIRST]->(b)

For this case, try the following query:

 MATCH path=(a)-[r:FIRST|SECOND*1..]->(b)
 WHERE SIZE([r1 in relationships(path) WHERE type(r1) = 'SECOND' | r1]) = 3
 RETURN path

As noted by @cybersam in the comments, to avoid out-of-memory errors, you should put an upper bound in the pattern match, which suits your purpose. Also, if upto 3 SECOND relationships are required, then this query will work:

 MATCH path=(a)-[r:FIRST|SECOND*1..10]->(b)
 WHERE SIZE([r1 in relationships(path) WHERE type(r1) = 'SECOND' | r1]) <= 3
 RETURN path

Note, I have provided an upper limit of 10 here, you can modify it accordingly.