CODEWITHSUNDEEP
neo4jcypher
Ram
Ram

Reputation: 97

Neo4j Optional Match

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.

Upvotes: 1

Views: 280

Answers (1)

Bruno Peres
Bruno Peres

Reputation: 16375

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.

Upvotes: 2

Related Questions