Best way to handle optional parameters within WHERE clause

[kurt_slater]

We have a number of queries via API where portions of the WHERE clause are based on parameters, many of which are optional. When a parameter is empty/null the expected behavior is that portion of the WHERE clause is effectively ignored.

The query below is an example of this. When the $clause_ids parameter is null, we expect it to have no bearing on the query. We are handling this by using the (size(clause_ids)=0) portion of the query.

PROFILE WITH 
'Clause with Mandate' as mandate_clause, COALESCE($clause_ids,[]) AS clause_ids
MATCH (doc:Doc)-[:HAS_VERSION]->(docVersion:DocVersion)-[:CONTAINS*]-(cl:Clause)-[:IS_A]->(ct:ClauseType{name:mandate_clause,isActive:true})
WHERE COALESCE(docVersion.isArchived,false)=false
AND ((size(clause_ids)=0) OR (cl.GUID IN clause_ids)) 
RETURN DISTINCT COUNT(cl)

This works property, but the performance is terrible. What we see is when $clause_ids is null, it triggers a NodeUniqueIndexSeek on the entire DB (some 11M+ nodes). As a test we manually removed the portion of the relevant portion of the where clause and the resulting behavior is a NodeUniqueIndexSeek of around 5000 nodes. This is the result and behavior we expect to see.

Is there a better way to handle an optional parameter like this? It seems like our approach of using size() is really not performant.

We are currently using v3.5 but will be upgrading to v4.2 soon.

Thanks!

[tfontanella011]

Hi @kurt.slater,

Did you try adding a clause into the query to check if $clause_ids is null ? For example, for your query it should be something like this (without applying the COALESCE when $clause_ids is null):

MATCH (doc:Doc)-[:HAS_VERSION]->(docVersion:DocVersion)-[:CONTAINS*]-(cl:Clause)-[:IS_A]->(ct:ClauseType{name:mandate_clause,isActive:true})
WHERE COALESCE(docVersion.isArchived,false)=false
AND (($clause_ids is null) OR (cl.GUID IN clause_ids)) 
RETURN DISTINCT COUNT(cl)

This should be more performant (and in Neo4j 4.x it works accordingly).

Hope it helps