Cypher COLLECT clause to build a list where some lists could be empty

Question

I have a database in which I have Entity nodes, User nodes, and a couple of relationships including LIKES, POSTED_BY. I'm trying to write a query to achieve this objective:

Find all Entity nodes that a particular user LIKES or those that have been POSTED_BY that User

Note that I have simplified my query - in real I have a bunch of other conditions similar to the above.

I'm trying to use a COLLECT clause to aggregate the list of all Entity nodes, and build on that line by line.

MATCH (e)<-[:LIKES]-(me:User{id: 'rJVbpcqzf'} )
WITH me, COLLECT(e) AS all_entities
MATCH (e)-[:POSTED_BY]->(me)
WITH me, all_entities + COLLECT(e) AS all_entities
UNWIND all_entities AS e
WITH DISTINCT e
RETURN e;

This seems to be returning the correct list ONLY if there is at least one Entity that the user has liked (i.e., if the first COLLECT returns a non-empty list). However, if there is no Entity that I have liked, the entire query returns empty.

Any suggestions on what I'm missing here?

Gabor Szarnyas Gabor Szarnyas · Accepted Answer · 2017-12-23T00:34:37

Use OPTIONAL MATCH:

MATCH (me:User {id: 'rJVbpcqzf'})
OPTIONAL MATCH (me)-[:LIKES|POSTED_BY]->(e)
RETURN collect(DISTINCT e) AS all_entities

Notes:

Instead of collecting and unwinding, you can simply use DISTINCT. You can also use DISTINCT with collect.
You can also use multiple relationship types, i.e. the LIKES|POSTED_BY for the relationship type here.

Cypher COLLECT clause to build a list where some lists could be empty

1 Answers