Search with NOT operator with pattern as well as constraints on properties

[georg_fette]

Hello,
In chapter "3.7.5.2. Filter on patterns using NOT" it is said, that using the not operator only "patterns" can be excluded from the match. I would like to also put constraints on the properties of the nodes to be excluded. So something like
MATCH (A)-[ * ]->(B:myType)
WHERE B.value = 'x' AND NOT ((A)-[ * ]->(C:myType) WHERE C.value = 'y')
RETURN A, B
So the nodes C of type myType do exists, but I would like to exclude those with a specific property.

MY first idea was to use this:
MATCH (A)-[ * ]->(B:myType), (A)-[ * ]->(C:myType)
WHERE B.value = 'x' AND NOT (C.value = 'y')
RETURN A, B
But this would merely match the same node for B and C (or possibly another one that has a value different than 'y') but it would not check on the definite non-existence of a possibly existing node C that has 'y' as value.
Greetings
Georg

[suryagupta08 · Accepted solution]

Hi Georg,

here NOT is not enough to exclude relations between A and C, you need to use EXISTS as well.
If you want to exclude all those relation between A and B where A also have relation with C with a property (value='y'). You can try this.

MATCH (A)-->(B:myType)
WHERE B.value = 'x' AND NOT EXISTS ((A)-->(:myType{value:'y'}) )
RETURN A, B

I hope this helps 🙂

best
surya

[georg_fette]

That solution looks nice. How would the solution look like if the constraint on the non existing node involves the calls of operators or functions ? Is it allowed to use the property-specifying-syntax including operators, e.g.

MATCH (A)-->(B:myType)
WHERE B.value = 'x' AND NOT EXISTS ((A)-->(:myType{sqrt(value * value + 1) > 5}) )
RETURN A, B

[suryagupta08]

If you will try your query you will see that it will give an error. You cannot check for property like this
sqrt(value * value + 1) > 5

this can be checked in the where statement but not with the key:value way inside a node definition.

I can see that you are trying to limit your relationship between node A, B, where node B should have a label :myType and a property value=x but not a property sqrt(value * value + 1) > 5

if the nodes B and C have the same label, then you do not need to check another relation
EXISTS ((A)-->(:myType{value:'y'}) )

rather you can just check the property in the where statement. you can just implement it like this

MATCH (A)-->(B:myType) 
WHERE B.value = 'x' AND sqrt(B.value * B.value + 1) <= 5
RETURN A, B

[georg_fette]

Yes, I presumed, that I would have to filter it with a WHERE, but how can I include the constraint, which I have to put in the where in the restricted scope inside the NOT EXISTS ? When I put it in the property constraints inside the curly brackets, it would look like this:

MATCH (A)-->(B:myType)
WHERE B.value = 'x' AND NOT EXISTS ((A)-->(C:myType{ WHERE sqrt(C.value * C.value + 1) <= 5}))
RETURN A, B

Or is it allowed to put inside the NOT EXISTS sub-statement complete queries, like this:?

MATCH (A)-->(B:myType)
WHERE B.value = 'x' AND NOT EXISTS (MATCH (A)-->(C:myType) WHERE sqrt(C.value * C.value + 1) <= 5) RETURN C)
RETURN A, B