MATCH following OPTIONAL MATCH

[rafikj]

This is my first post. So please take it easy on me if the question does not make sense.
I am running Neo4j community 4.0.3. I create a Cypher query that looks like:

OPTIONAL MATCH (a)-[r1]->(b)
MATCH (b)-[r2]->(c)

I want the query to return all a's regardless if b is linked to some c or not. But only return the b's which are linked to c.

But the query above is not returning a's for which b exists but c does not.

This is the actual query I am trying to run:

MATCH (rtc_merged_Defect1:Artifact)
-[:http://open-services.net/ns/core#instanceShape]->(:Concept)<-[:INSTANCE_OF]-(rtc_merged_Defect1_instanceShape:Artifact)
MATCH (rtc_merged_Defect1_instanceShape)
-[:http://jazz.net/ns/lqe/merge/mergeShape]->(:Concept{url:'http://jazz.net/ns/lqe/merge/gensym/cm/Defect'})

OPTIONAL MATCH (rtc_merged_Defect1)
-[:http://jazz.net/xmlns/prod/jazz/rtc/cm/1.0/filedAgainst]->(:Concept)<-[:INSTANCE_OF]-(rtc_merged_Defect1_filedAgainst:Artifact)
OPTIONAL MATCH (rtc_merged_Defect1_filedAgainst)
-[:http://www.w3.org/2004/02/skos/core#broader]->(:Concept)<-[:INSTANCE_OF]-(rtc_merged_Defect1_filedAgainst_parent:Artifact)
// here is the problematic MATCH that causes less rtc_merged_Defect1's to be returned.
MATCH (rtc_merged_Defect1_filedAgainst_parent)-[:http://www.w3.org/2004/02/skos/core#broader]->(rtc_merged_Defect1_filedAgainst_parent_parent:Concept)

[intouch_vivek]

Hi @rafikj,

Welcome to the community!!!

Please try like
MATCH (a)
OPTIONAL MATCH (a)-[r1]->(b)-[r2]->(c)
Return a,b,c

[rafikj]

Thanks Vivek,

That did work. So my guess when I see the expression:
OPTIONAL MATCH (a)-[r1]->(b)-[r2]->(c)
What that really means is that r1 is optional but all subsequent relationships on the same path (r2) are not optional. So to generalize if I have:

a-[r1(optional)]->b-[r2(required)]->c-[r3(optional)]->d-[r4(required)]->e-[r5(required)]->f-[r6(optional)]->g

I would have to write:
MATCH (a)
OPTIONAL MATCH (a)-[:r1]->(b)-[:r2]->(c)
OPTIONAL MATCH (c)-[:r3]->(d)-[:r4]->(e)-[:r5]->(f)
OPTIONAL MATCH (f)-[:r6]->(g)

Am I correct?

[intouch_vivek]

Hi,

No you are wrong..
Optional Match is only meaningful when it is following Match statement.. It is used to implement left outer join. So in your case all the a nodes and only match b and c nodes.
Please visit the documentation related to Optional Match

[rafikj]

Thanks Vivek,

I am an advanced SQL and SPARQL user and I am currently finding it difficult to map some concepts and patterns to Cypher.

I did read the documentation and I find the examples are too simple. Going back to the more complicated example:

a-[r1(optional)]->b-[r2(required)]->c-[r3(optional)]->d-[r4(required)]->e-[r5(required)]->f-[r6(optional)]->g

With SQL, I can do

SELECT a.NAME, b.NAME, FROM A a
LEFT OUTER JOIN (B b
	INNER JOIN (C c
		LEFT OUTER JOIN (D d
			INNER JOIN (E e
				INNER JOIN (F f
					LEFT OUTER JOIN G g ON g.r6_ID = f.ID
				) ON r5.e_ID = e.ID
			) ON e.r4_ID = d.ID
		) ON r3.c_ID = c.ID
	) ON c.r2_ID = b.ID
) ON b.r1_ID = a.ID

WITH SPARQL, I can do:

OPTIONAL {
	?a ?r1 ?b.
	?b ?r2 ?c.
	OPTIONAL {
		?c ?r3 ?d.
		?d ?r4 ?e.
		?e ?r5 ?f.
		OPTIONAL {
			?f ?r6 ?g.
		}
	}
}

I just cannot find a way to nest matches in Cypher and mimic the behavior of grouped joins in both SQL and SPARQL.

[intouch_vivek · Accepted solution]

Hi @rafikj,

Community will definitely help you on the same.. However if your original issue is resolved then please close it and create a fresh topic for this one