Neo4j Optional Match

Question

I am trying to generate an array which contains the number of messages sent from a employee to all (if any or zero if there is no communication) using OPTIONAL MATCH Clause, but its not working. I am not getting zeroes in the array when there is no communication. I am using the following query:

MATCH(e:Employee{key:101,div:'finance'}),(b:Employee)
OPTIONAL MATCH (e)-[r:Message]->(b)
WITH e.name as  em, r.NUMBER_OF_MESSAGES as msg 
ORDER BY msg DESC
RETURN em, COLLECT(msg) AS row

Here is the output:

Row: [31,20,12,10,8,7,7,2,2,2,2,1,1,1]

What am I doing wrong here? Thanks in advance.

Bruno Peres Bruno Peres · Accepted Answer · 2017-06-27T11:21:18

The problem here is that collect() will collect all values ignoring nulls. When has no communication between two nodes the value of msg variable will be null and consequently ignored by COLLECT(). To fix it change your query to:

MATCH(e:Employee{key:101,div:'finance'}),(b:Employee)
OPTIONAL MATCH (e)-[r:Message]->(b)
WITH e.name as  em, coalesce(r.NUMBER_OF_MESSAGES,0) as msg 
ORDER BY msg DESC
RETURN em, COLLECT(msg) AS row

This query use the coalesce() function. This function will return the first non-null value in the list of arguments passed to it. Then, if r.NUMBER_OF_MESSAGES is null it will be replaced by zero.

Neo4j Optional Match

1 Answers