Neo4j Cypher filtering based on map parameters

Question

Based on the previous question:

Neo4j Cypher query structure and performance optimization Neo4j Cypher node filtering by pattern comprehension

finally I have refactored my query to the following:

MATCH (parentD)-[:CONTAINS]->(childD:Decision) 
    WHERE id(parentD) = {decisionId} 
        MATCH (childD)<-[:SET_FOR]-(equalFilterValue)-[:SET_ON]->(equalFilterCharacteristic) 
            WHERE ALL(key IN keys({equalFilters}) WHERE id(equalFilterCharacteristic) = toInt(key) AND equalFilterValue.value = ({equalFilters}[key]))  
    WITH DISTINCT childD  
        MATCH (childD)<-[:SET_FOR]-(rangeFilterValue)-[:SET_ON]->(rangeFilterCharacteristic) 
            WHERE ALL(key IN keys({rangeFilters}) WHERE id(rangeFilterCharacteristic) = toInt(key) AND ({rangeFilters}[key])[0] <= rangeFilterValue.value <= ({rangeFilters}[key])[1]) 
WITH * MATCH (childD)-[ru:CREATED_BY]->(u:User)  
RETURN ru, u, childD AS decision 
SKIP 0 LIMIT 100

This query works fine if each *filter type(map) has only one key, for example:

queries.add(new InQuery(integerCharacteristic.getId(), 30));

or

queries.add(new InQuery(stringCharacteristic.getId(), "Two"));

but fails when I add 2 or more conditions, for example:

queries.add(new InQuery(integerCharacteristic.getId(), 30));
queries.add(new InQuery(stringCharacteristic.getId(), "Two"));

The following query doesn't work as expected and my test assertions fail:

MATCH (parentD)-[:CONTAINS]->(childD:Decision) 
WHERE id(parentD) = {decisionId} 
    MATCH (childD)<-[:SET_FOR]-(inFilterValue)-[:SET_ON]->(inFilterCharacteristic) 
        WHERE ALL(key IN keys({inFilters}) WHERE id(inFilterCharacteristic) = toInt(key) AND ({inFilters}[key]) IN inFilterValue.value) 
WITH * MATCH (childD)-[ru:CREATED_BY]->(u:User)  
RETURN ru, u, childD AS decision 
SKIP 0 LIMIT 100

The parameter:

inFilters = {3153=30, 3151=Two}

Why it doesn't work when inFilters map contains 2 or more keys and how to make it working ?

Tezra Tezra · Accepted Answer · 2017-05-11T12:26:35

So for the reason this only works with 1 key, lets go over what one WHERE ALL is doing when there is 2 keys.

WITH inFilters = {3153=30, 3151=Two}
....
WHERE ALL(key IN keys({equalFilters}) 
    WHERE id(equalFilterCharacteristic) = toInt(key) 
    AND equalFilterValue.value = ({equalFilters}[key]))

Is equivalent to

WHERE id(equalFilterCharacteristic) = toInt(3153) 
AND equalFilterValue.value = ({equalFilters}[3153]) 
AND id(equalFilterCharacteristic) = toInt(3151) 
AND equalFilterValue.value = ({equalFilters}[3151])

And the problem there is that now we are checking that the node id of equalFilterCharacteristic is equal to 3153 AND 3151 at the same time, for each and every equalFilterCharacteristic. Since Neo4j only uses real numbers for the node ids, the above statement basically ends up reducing to an expensive WHERE FALSE when there is more than 1 key. So WHERE ALL can never be true in the above case. WHERE ANY however will evaluate to true if at least 1 check-group is true, and would be equivalent to

WHERE ( 
    id(equalFilterCharacteristic) = toInt(3153) 
    AND equalFilterValue.value = ({equalFilters}[3153]) 
)
OR 
(
    id(equalFilterCharacteristic) = toInt(3151) 
    AND equalFilterValue.value = ({equalFilters}[3151]) 
)

Of course, since you know the key you are trying to match, you can skip the ALL and just do (don't think you need toInt() on id(), but id() is a long)

WHERE equalFilterValue.value = ({equalFilters}[id(equalFilterCharacteristic)])

Neo4j Cypher filtering based on map parameters

1 Answers