Optional Matches and Regular Expressions Filtering Results

cypher

(Christopher Pikula) #1

So I've been making some queries, and I think I've found something that doesn't make sense to me. (Neo4j 3.3.6)

I've got a query, such as:

MATCH (entity0000:T1) 
OPTIONAL MATCH (entity0000:T1)-[:R1]->(entity0001:R1)-[:R2]->(entity0002:T2)-[:R3]->(entity0003:T3)-[:R4]->(entity0004:T4) 
OPTIONAL MATCH (entity0000:T1)-[:R5_A]->(entity0898:T2)-[:R3]->(entity0899:T3)-[:R4]->(entity0900:T4) 
OPTIONAL MATCH (entity1529:T5)-[:R5_P]->(entity0898:T2)-[:R3]->(entity0899:T3)-[:R4]->(entity0900:T4) 
OPTIONAL MATCH (entity0000:T1)-[:R5_M]->(entity0973:T2)-[:R3]->(entity0974:T3)-[:R4]->(entity0975:T4) 
...  plus several more
WITH entity0105, entity1607, entity1048, entity0898, entity1408, entity0900, entity0975 ... plus several more.
WHERE NOT entity0975.parameter1=~ '(?i)^.*WoRd1.*$'
AND WHERE NOT entity0975.parameter1=~ '(?i)^.*WoRd2.*$'
AND ... a couple more
RETURN entity0000.parameter2 as col0 ... plus a whole bunch more

Now, my question is like this. When I'm doing a regex query, trying to filter out results, does it forcibly turn that entity into a required entity, filtering out results that don't have any values at all for the regular expression query to find? (Aka, the stuff that I would like to keep.)

If so, how can I keep the null columns, getting the partial data from my pattern, while still performing the regex filters on the patterns that have a full set of data?

I'm doing this because my pattern has a small Cartesian product that occurs when it tries to match. Several branches on the pattern have multiple results that match. Sometimes 3, sometimes 4. The extra results are required sometimes, in small queries, but not when doing large queries. When 4 or 5 or those branches are in the pattern, well, for every result that I want, I get 255 more. Hence, the regex filtering.


(Andrew Bowman) #2

If you want to keep rows where the entity is null, you can add that into your WHERE predicate:

WHERE entity0975 IS NULL OR NOT entity0975.parameter1=~ '(?i)^.*WoRd1.*$


(Michael Hunger) #3

Or actually move your WHERE to the OPTIONAL MATCH where it rather belongs.
Then it will be pulled into the match.