Optional Create or Optional Merge

[ADTC]

When I used OPTIONAL MATCH and MERGE together, I encountered a problem. The query ran and crashed with an error logged. I was told to check the log, and I found that the variable I was getting from the OPTIONAL MATCH was null, when a MERGE was being performed to create a relationship to it.

How do I make it create the relationship only when OPTIONAL MATCH returns a non-null value (i.e. a node) for it? I thought the simplest syntax (if this has to be implemented) would be to simply add OPTIONAL in front of MERGE or CREATE. Of course, Neo4j could also just ignore any MERGE or CREATE involving null value and emit a warning for such.

[Mats-SX]

Hello @ADTC, and thanks for reaching out to us.

What is the actual query you are having issues with? If you're using OPTIONAL MATCH, and aren't liking that it is nulling out variables when they aren't found, then perhaps you should try just using MATCH instead?

[ADTC]

Oh darn, I didn't save the query. But yes, in retrospect what you say makes sense. Although, what about a case where I MATCH some nodes and only OPTIONAL MATCH some relationships and nodes, and then I want to perform some operation on the MATCHed nodes, and another operation on the OPTIONAL MATCHed nodes, how can I ignore the nulls without writing conditionals? I guess the easy way out is to split into two separate queries and get rid of OPTIONAL.

I can't remember exactly what I was doing. If I get it again I'll ping you.

[Mats-SX]

You could filter out the nulls by using collect() and UNWIND, but it gets a bit messy. But without the actual query and use case it's difficult to give accurate advice.

I'll close this as resolved then. Feel free to reopen should your memory clear up in the future.

All the best Mats

[ADTC]

@Mats-SX I can't reopen the issue. Looks like GitHub issues don't support reopening, or maybe it's a project setting.

I still can't remember my own query, but here is an example from someone else: https://neo4j-users.slack.com/archives/help-cypher/p1464903073001252

MATCH (i:Item {itemID:'79f3a27e-ed18-439c-a1c3-a44f9a6ceacb'})
WITH i
OPTIONAL MATCH (i)-[cz:CURRENT_ZONE]->(z:Zone)
CREATE (i)-[prevZone:PREVIOUS_ZONE {DTS: cz.DTS, friendlyDTS: cz.friendlyDTS}]->(z)
return i;

Above query doesn't work when z is null. The alternative is:

MATCH (i:Item {itemID:'79f3a27e-ed18-439c-a1c3-a44f9a6ceacb'}) 
OPTIONAL MATCH (i)-[cz:CURRENT_ZONE]->(z:Zone)
WITH i,z WHERE NOT z IS NULL
CREATE (i)-[prevZone:PREVIOUS_ZONE {DTS: cz.DTS, friendlyDTS: cz.friendlyDTS}]->(z)
return i;

But I thought it's less verbose and cleaner to say:

MATCH (i:Item {itemID:'79f3a27e-ed18-439c-a1c3-a44f9a6ceacb'}) 
OPTIONAL MATCH (i)-[cz:CURRENT_ZONE]->(z:Zone)
OPTIONAL CREATE (i)-[prevZone:PREVIOUS_ZONE {DTS: cz.DTS, friendlyDTS: cz.friendlyDTS}]->(z)
return i;

[Mats-SX]

@ADTC So when there is no z node found, you would like to not have the relationship created?

MATCH (i:Item {itemID: '79f3a27e-ed18-439c-a1c3-a44f9a6ceacb'})
MATCH (i)-[cz:CURRENT_ZONE]->(z:Zone)
CREATE (i)-[prevZone:PREVIOUS_ZONE {DTS: cz.DTS, friendlyDTS: cz.friendlyDTS}]->(z)
RETURN i

or even shorter:

MATCH (i:Item {itemID: '79f3a27e-ed18-439c-a1c3-a44f9a6ceacb'})-[cz:CURRENT_ZONE]->(z:Zone)
CREATE (i)-[prevZone:PREVIOUS_ZONE {DTS: cz.DTS, friendlyDTS: cz.friendlyDTS}]->(z)
RETURN i

This query will do that, but it will not return the is that do not have the relationship, and perhaps you still want that? Your second query does not do this either, however.

[ADTC]

Yes, the choice of returning all is including those that do not have the relationship, and that I only optionally create relationships when end nodes z exist, not when they are null, and I get all i regardless.

[Mats-SX]

I understand. You could do that like this:

MATCH (i:Item {itemID:'79f3a27e-ed18-439c-a1c3-a44f9a6ceacb'})
MATCH (j:Item {itemID:'79f3a27e-ed18-439c-a1c3-a44f9a6ceacb'})-[cz:CURRENT_ZONE]->(z:Zone)
CREATE (j)-[prevZone:PREVIOUS_ZONE {DTS: cz.DTS, friendlyDTS: cz.friendlyDTS}]->(z)
RETURN i

but it isn't very pretty (and may suffer from performance if there are many is and js).

I can see why you would want OPTIONAL CREATE. Will keep this issue open for the language group to consider.

[splilly]

I also would like to see this feature added as currently many single cyphers that were working on 2.3 have to be broken up into multiple cyphers on neo4j 3.

[splilly]

Also the proposed

MATCH (i:Item {itemID:'79f3a27e-ed18-439c-a1c3-a44f9a6ceacb'})
MATCH (j:Item {itemID:'79f3a27e-ed18-439c-a1c3-a44f9a6ceacb'})-[cz:CURRENT_ZONE]->(z:Zone)
CREATE (j)-[prevZone:PREVIOUS_ZONE {DTS: cz.DTS, friendlyDTS: cz.friendlyDTS}]->(z)
RETURN i

Wont work when there are no matches for the pattern (j:Item {itemID:'79f3a27e-ed18-439c-a1c3-a44f9a6ceacb'})-[cz:CURRENT_ZONE]->(z:Zone) If that pattern matches nothing you won't get i returned.

[ccemeraldeyes]

+1 to optional merge

[coding4kay]

You can create an optional merge with subqueries, with union each subquery return a result.

 call {
    return 1 union 
    with * where 1=2
    return 1

} 
 call {
    with * where 1=1
    return 2

}

Here is a more complex example:

merge(var_1:Person {id:$model.person.id})
set var_1.name = coalesce($model.person.name, var_1.name) 
with *
        call {
        return 1 union
        with var_1
        with * where 1=1 and exists($model.person.lead.person.id)
        merge(var_2:Person {id:$model.person.lead.person.id})
        set var_2.name = $model.person.lead.person.name 
        merge(var_1)-[lead:LEAD]->(var_2)
        return 1
        }
        call {
        return 2 union
        with var_1
        with * where 1=1 and exists($model.person.hasPosition.position.name)
        merge(var_3:Position {name:$model.person.hasPosition.position.name})
        merge(var_1)-[hasPosition:HAS_POSITION]->(var_3)
        return 2
        }
return 'done'

Here are some sample models: {"person":{"id":"3","name":"Kay","hasPosition":{"position":{"name":"Cloud Architect"}},"lead":{"person":{"id":"1"}}}} but it's also possible that not all values exist, for example the lead not exist {"person":{"id":"1","name":"Bernd","lead":{"person":{}},"hasPosition":{"position":{"name":"Manager"}}}}