Create nodes if optional match returns null

Question

All the nodes I'll be using have unique constraints associated to them.

I tried to create new nodes like this (I thought about MERGE so it would not create the node if it already exists):

MERGE (n:url{url_addr:'test.com'})-[:FROM_DEVICE]->(o:device{mode:'iphone'})

And the node already exists it returns this error:

Node(21) already exists with label `url` and property `url_addr` = 'test.com'

It was obvious, since I didn't match them beforehand, so I did it in order to just pass the results properties:

MATCH (n:url{url_addr:'test.com'}), (o:device{mode:'iphone'})
MERGE (n)-[:FROM_DEVICE]->(o)

But in case any of this records do not exist new nodes will not be created.

So I thought about using OPTIONAL MATCH, since it would replace any non-existent value with null, but this means it would also replace the properties I passed with null values.

OPTIONAL MATCH (n:url{url_addr:'test.com'}) RETURN n
MERGE n-[:FROM_DEVICE]->(o:device{mode:'iphone'})

It returned this error:

Failed to create relationship `  REL87(d65d30eb-65f5-4460-b166-15996622cf1b)`, node `n` is missing. If you prefer to simply ignore rows where a relationship node is missing, set 'cypher.lenient_create_relationship = true' in neo4j.conf

So I thought about using CASE statements in order to first verify if the match would return null and then create the node to define the relationship, but things didn't work well:

OPTIONAL MATCH (n:url{url_addr:'test.com'})
WITH n as test
RETURN CASE
    WHEN test IS NULL THEN CREATE (:url{url_addr:'test.com'})
END

This happens:

Invalid input '{': expected
  "!="
  "%"
  ")"
  "*"
  "+"
  ","
  "-"
  "."
  "/"
  ":"
  "<"
  "<="
  "<>"
  "="
  "=~"
  ">"
  ">="
  "AND"
  "CONTAINS"
  "ENDS"
  "IN"
  "IS"
  "OR"
  "STARTS"
  "XOR"
  "["
  "^" (line 4, column 51 (offset: 135))
"        WHEN CASE IS NULL THEN CREATE (a:cookie_id{url:'test.com'})"
                                                   ^

Removing the properties of the node does not help.

Brakebein Brakebein · Accepted Answer · 2021-06-26T09:11:13

MERGE always matches or creates the whole pattern. So, you might want to split up the MERGE clause, so that it matches or creates each node and the relationship:

MERGE (n:url {url_addr:'test.com'}) // match or create url node
MERGE (o:device {mode:'iphone'})    // match or create device node
MERGE (n)-[:FROM_DEVICE]->(o)       // match or create relationship

You might also want to have a look at conditional cypher execution with APOC procedures: https://neo4j.com/labs/apoc/4.2/cypher-execution/conditionals/

Create nodes if optional match returns null

1 Answers