Neo4j Cypher query empty collect makes the entire result empty

Question

I have been facing wierd problem, I have tried to get data from the neo4j graph that I have built.Here is my query

MATCH (u1:User {user_id: 4})-[:FOLLOWS]->(u2:User)-[]->(r1:Rest{city_id: 1})
WITH COLLECT ({ REST: r1.res_id}) as rows
MATCH (u1:User {user_id: 4})-[rel]->(r2:Rest{city_id: 1})
WHERE NOT (u1:User {user_id: 4})-[rel : BEEN_THERE | ADD_REVIEW]->(r2:Rest{city_id: 1})
WITH rows + COLLECT ({ REST: r2.res_id}) AS allrows
UNWIND allrows as row 
RETURN row.REST as RESTAURANT_ID, count(row.REST) as COUNT
ORDER BY COUNT desc
LIMIT 15;

However when the result of COLLECT ({ REST: r2.res_id}) are empty the entire result becomes empty. Also the query is unable to identify rows from 1st match and return undefined rows. Please let me know. Thanks!

Frank Pavageau Frank Pavageau · Accepted Answer · 2016-09-21T11:39:30

If the pattern doesn't match any path, the result will be empty indeed.

You have to split the MATCH in 2 and make the second one OPTIONAL, or in your actual case, stop matching the same u1 node over and over again:

MATCH (u1:User {user_id: 4})
OPTIONAL MATCH (u1)-[:FOLLOWS]->(:User)-->(r1:Rest {city_id: 1})
WITH u1, collect({ REST: r1.res_id }) AS rows
OPTIONAL MATCH (u1)-->(r2:Rest {city_id: 1})
WHERE NOT (u1)-[:BEEN_THERE | ADD_REVIEW]->(r2)
WITH rows + collect({ REST: r2.res_id }) AS allrows
UNWIND allrows as row 
RETURN row.REST AS RESTAURANT_ID, count(row.REST) AS COUNT
ORDER BY COUNT desc
LIMIT 15

I'm not sure about the first OPTIONAL MATCH in your case (you only mention the second collect as being a blocker), but if you want the aggregation of both patterns where each can be empty, here you go.

Neo4j Cypher query empty collect makes the entire result empty

1 Answers