Neo4j Cypher reduce UNWINDs calls

Question

I have the following Cypher query:

UNWIND childD.detailedCriterionIds as dCId
WITH childD, childDStat, dCId + coalesce(childDStat['replaceableCriterionIds.' + dCId],[]) as cGroup
WITH childD, childDStat, cGroup
WHERE NOT AlL(x IN cGroup WHERE x IN $zeroCriterionIds )
WITH childD, childDStat, collect(cGroup) as cGroups
WHERE size(cGroups) >= size(childD.detailedCriterionIds)

WITH childD, childDStat, cGroups
UNWIND cGroups as cGroup
WITH childD, childDStat, cGroup
WHERE ANY(x IN cGroup WHERE x IN $detailedCriterionIds)
WITH childD, childDStat, collect(cGroup) as cGroups
WHERE size(cGroups) > 0

As you can see, I use 2 UNWINDs there. Is it possible to optimize this query to avoid the second UNWIND? If so, could you please show how?

I tried the following, but it produced a different result:

UNWIND childD.detailedCriterionIds as dCId
WITH childD, childDStat, dCId + coalesce(childDStat['replaceableCriterionIds.' + dCId],[]) as cGroup
WITH childD, childDStat, cGroup
WHERE NOT AlL(x IN cGroup WHERE x IN $zeroCriterionIds )
AND ANY(x IN cGroup WHERE x IN $detailedCriterionIds)
WITH childD, childDStat, collect(cGroup) as cGroups
WHERE size(cGroups) > 0

Where is my mistake?

Answer 1

Your second snippet does not have an equivalent to the size(cGroups) >= size(childD.detailedCriterionIds) test, so it will not always produce the same result.

In order to do that test, you need to calculate the number of cGroups. Therefore, there may not be a better approach than what you are already doing in the first snippet.

Minor nit: the WITH childD, childDStat, cGroup clause is redundant.