Unwanted entries in COLLECT from OPTIONAL MATCH that has no result

[rich_carpenter]

I'm using Neo4j 3.5 community version the results can be reproduced in the Neo4j browser. I have a cypher query that has 3 COLLECT columns derived from the results of an OPTIONAL MATCH. When there is a result from one of the OPTIONAL MATCH, it is producing the results desired. But when there is no result from the OPTIONAL MATCH, it would be desired that the collect column have an empty array ''. Instead, for this query it produces an array with one entry, in the entry both attributes have a value of null.

This makes it somewhat more difficult to determine if there is data in the column or not, because a list of length one must interrogate the data values to see if there are values for the properties or null for the properties.

Example of result received for column when no results from OPTIONAL MATCH:

[
    {
      "score": null,
      "name": null
    }
]

Example of result desired for column when no result from OPTIONAL MATCH

[]

This is easy to reproduce. Populate an example set of data with:

MERGE (u1:User {uid:'user1'})
MERGE (u2:User {uid:'user2'})
MERGE (u3:User {uid:'user3'})
MERGE (u4:User {uid:'user4'})
MERGE (a1:Application {name:'App1'})
MERGE (a2:Application {name:'App2'})
MERGE (g1:Group {name:'Group1'})
MERGE (g2:Group {name:'Group2'})
MERGE (u1)-[:RANKING {score:0.5}]-(a1)
MERGE (u1)-[:RANKING {score:0.5}]-(a2)
MERGE (u1)-[:SIMILAR {score:0.2}]-(u2)
MERGE (u1)-[:RANKING {score:0.3}]-(g1)
MERGE (u1)-[:RANKING {score:0.3}]-(g2)
MERGE (u2)-[:RANKING {score:0.2}]-(a1)
MERGE (u2)-[:RANKING {score:0.2}]-(a2)
MERGE (u2)-[:SIMILAR {score:0.2}]-(u3)
MERGE (u3)-[:RANKING {score:0.5}]-(a2)

The the query creating the three collect columns can be run:

MATCH (this:User)
OPTIONAL MATCH (this)-[r:RANKING]->(a:Application) 
WITH this, collect({score:r.score, name:a.name}) AS rankedApplications
OPTIONAL MATCH (this)-[r:RANKING]->(a:Group) 
WITH this, collect({score:r.score, name:a.name}) AS rankedGroups, rankedApplications
OPTIONAL MATCH (this)-[r:SIMILAR]->(a:User) 
WITH this, rankedGroups, rankedApplications, collect({score:r.score, uid:a.uid}) AS similarUsers
RETURN this.uid AS uid, rankedGroups, rankedApplications, similarUsers

For user1 everything comes out as desired as there were results for all three of the OPTIONAL MATCH conditions. Notice the last column 'similarUsers' that has a single match and creates a list of size 1.

However, for rows that don't have data for some of the OPTIONAL MATCH, such as for user2 in the 'rankedGroups' column, it produces a list of size 1 where the property values of the one entry are null, instead of creating an empty list.

How can I modify this query to create a COLLECT column with an empty list in the result when there are no results from the OPTIONAL MATCH used to create the data for that column?

[Cobra]

Hello @rich_carpenter and welcome to the Neo4j community

You can use CASE WHEN clause:

MATCH (this:User)
OPTIONAL MATCH (this)-[r:RANKING]->(a:Application) 
WITH this, CASE WHEN r IS NOT NULL THEN collect({score:r.score, name:a.name}) ELSE [] END AS rankedApplications
OPTIONAL MATCH (this)-[r:RANKING]->(a:Group) 
WITH this, CASE WHEN r IS NOT NULL THEN collect({score:r.score, name:a.name}) ELSE [] END AS rankedGroups, rankedApplications
OPTIONAL MATCH (this)-[r:SIMILAR]->(a:User) 
WITH this, rankedGroups, rankedApplications, CASE WHEN r IS NOT NULL THEN collect({score:r.score, uid:a.uid}) ELSE [] END AS similarUsers
RETURN this.uid AS uid, rankedGroups, rankedApplications, similarUsers

Regards,
Cobra

[rich_carpenter]

Thank you @Cobra that looks like it will do it!

[andrew_bowman · Accepted solution]

One approach you can use for this is to use a map projection (from a node or relationship) instead of a literal map:

MATCH (this:User)
OPTIONAL MATCH (this)-[r:RANKING]->(a:Application) 
WITH this, collect(a {score:r.score, .name}) AS rankedApplications
...

That should get you the desired empty list when the OPTIONAL MATCH found no matches.

[rich_carpenter]

@andrew.bowman Thank you! This approach works as well (showing there is more than one way it can be done) and has the benefit of requiring less code. It has a more intuitive feel to it, by not creating the map when there are no entries, rather than having to specifically check for it afterwards.