WITH, SIZE, and null property values

user_1706802 0
Viewed 28

I have the following Neo4j/Cypher query:

MATCH (person:Person { person_id: "abc123" })
WITH user,
        SIZE( (person)-[:CHECKEDOUT]->() ) AS myCheckoutsCount,
        SIZE( (person)-[:AUTHOR]->(:Book {in_ciculation: true})<-[:HOLD]-(:PERSON {suspended: false}) ) AS holdsForMyBooksCount,
        SIZE( (person)-[:FAVORITE]->(:PERSON {suspended: false}) ) AS favoriteAuthorsCount,
        SIZE( (person)<-[:FAVORITE]-(:PERSON {suspended: false}) ) AS favoritedMeCount
RETURN *

The problem is that in_ciculation and suspended were added after the fact and are null for some books / people - which makes the counts incorrect.

I could write a script to go through and update the fields, if they are null, but it seems like the best practice would be to not assume that the value is set and support a null property value.

I've played around with:

OPTIONAL MATCH (person)-[:FAVORITE]->(favoriteAuthors:PERSON)
WHERE COALESCE (favoriteAuthors.suspended, false) = false
WITH COUNT(favoriteAuthors) AS favoriteAuthorsCount

but that makes the query very slow.

Is there some way to do coalesce, or handle null, within SIZE itself?

Thanks.

neo4j
1 Answers

If you are using an index, this query might be slow:

OPTIONAL MATCH (person)-[:FAVORITE]->(favoriteAuthors:PERSON)
WHERE COALESCE (favoriteAuthors.suspended, false) = false
WITH COUNT(favoriteAuthors) AS favoriteAuthorsCount

However, you can rewrite it to:

OPTIONAL MATCH (person)-[:FAVORITE]->(favoriteAuthors:PERSON)
WHERE NOT exists (favoriteAuthors.suspended) OR  favoriteAuthors.suspended = false
WITH COUNT(favoriteAuthors) AS favoriteAuthorsCount

Definitely make sure to avoid cartesian product, so do the aggregation in between every OPTIONAL MATCH like so:

OPTIONAL MATCH (person)-[:FAVORITE]->(favoriteAuthors:PERSON)
WHERE NOT exists (favoriteAuthors.suspended) OR  favoriteAuthors.suspended = false
WITH COUNT(favoriteAuthors) AS favoriteAuthorsCount

OPTIONAL MATCH (person)<-[:FAVORITE]-(favoritedMe:PERSON)
WHERE NOT exists (favoritedMe.suspended) OR  favoritedMe.suspended = false
WITH COUNT(favoritedMe) AS favoritedMeCount, favoriteAuthorsCount
user_66928950
Related