Can't MERGE with null values; 'Cannot merge node using null property value' in neo4j

Question

I have a column in a csv that looks like this:

I am using this code to test how the splitting of the dates is working:

LOAD CSV WITH HEADERS FROM
'file:///..some_csv.csv' AS line
WITH
SPLIT(line.date_of_birth, '/') AS date_of_birth
return date_of_birth;

This code block works fine and gives me what I'd expect, which is a collection of three values for each date, or perhaps a null if there was no date ( e.g,

[4, 5, 1971]  
[0, 0, 2003]  
[0, 0, 2005]  
 . . .  
null  
null  
 . . .  

My question is, what is this problem with the nulls that are created, and why can't I do a MERGE when there are nulls?

LOAD CSV WITH HEADERS FROM
'file:///..some_csv.csv' AS line
WITH
SPLIT(line.date_of_birth, '/') AS date_of_birth, line
MERGE (p:Person {
 date_of_birth: date_of_birth
});

This block above gives me the error:

Cannot merge node using null property value for date_of_birth  

I have searched around and have only found one other SO question about this error, which has no answer. Other searches didn't help.

I was under the impression that if there isn't a value, then Neo4j simply doesn't create the element.

I figured maybe the node can't be generated since, after all, how can a node be generated if there is no value to generate it from? So, since I know there are no ID's missing, maybe I could MERGE with ID and date, so Neo4j always sees a value.

But this code didn't fare any better (same error message):

LOAD CSV WITH HEADERS FROM
'file:///..some_csv.csv' AS line
WITH
SPLIT(line.date_of_birth, '/') AS date_of_birth, line
MERGE (p:Person {
 ID: line.ID
,date_of_birth: date_of_birth
});  

My next idea is that maybe this error is because I'm trying to split a null value on slashes? Maybe the whole issue is due to the SPLIT.

But alas, same error when simplified to this:

LOAD CSV WITH HEADERS FROM
'file:///..some_csv.csv' AS line
WITH line
MERGE (p:Person {
 subject_person_id: line.subject_person_id
,date_of_birth: line.date_of_birth
});

So I don't really understand the cause of the error. Thanks for looking at this.

EDIT

Both @stdob-- and @cybersam have both answered with equally excellent responses, if you came here via Google please consider them as if both were accepted

Winterize

I get this error even though I have no null or empty values whatsoever. – Peadar

Figured it out: you will also get this error when importing from CSV and the column is inside backticks (for ex. due to spaces) AND YOU HAVE FOREIGN CHARACTERS. – Peadar

Answer 1

As @cybersam said,MERGE doesn't work well with queries where the properties are set within the scope in null. So, you can use ON CREATE and ON MATCH:

LOAD CSV WITH HEADERS FROM
  'file:///..some_csv.csv' AS line
MERGE (p:Person {
  subject_person_id: line.subject_person_id
})
  ON CREATE SET p.date_of_birth = line.date_of_birth
  ON MATCH SET p.date_of_birth = line.date_of_birth

Duck

IfON CREATE and ON MATCH do exactly the same SET, then just replace the 2 clauses with a single SET. – Anisaanise

Answer 2

Some Cypher queries, like MERGE, do not work well with NULL values.

The somewhat tricky workaround for handling this situation with MERGE is to use the FOREACH clause to conditionally perform the MERGE. This query might work for you:

LOAD CSV WITH HEADERS FROM 'file:///..some_csv.csv' AS line
FOREACH (x IN CASE WHEN line.date_of_birth IS NULL THEN [] ELSE [1] END |
  MERGE (:Person {date_of_birth: SPLIT(line.date_of_birth, '/')})
);

[UPDATE with answer for "simplified" query]

Keep potentially-null properties out of your MERGE clause. Also, MERGE clauses must be carefully crafted to avoid creating unwanted extra nodes by limiting the properties specified in the MERGE.

LOAD CSV WITH HEADERS FROM 'file:///..some_csv.csv' AS line
MERGE (p:Person {subject_person_id: line.subject_person_id})
SET p.date_of_birth = line.date_of_birth

Anisaanise

Answer 3

Another solution that I've been rather fond of is to just tell cypher to skip rows in which the field of interest is NULL as follows:

USING PERIODIC COMMIT #
LOAD CSV WITH HEADERS FROM
'file:///.../csv.csv' AS line
WITH line, SPLIT(line.somedatefield, delimiter) AS date
WHERE NOT line.somedatefield IS NULL

[THE REST OF YOUR QUERY INVOLVING THE FIELD]

Cornerstone

Very elegant solution IMO. – Ramachandra

WHERE NOT was key for me – Couchman

Answer 4

Or you can use COALESCE(n.property?, {defaultValue})

Hymnology

why the question mark after property? and are the {}s supposed to mean a required value as in the Backus-Naur notation? – Ellaelladine

Answer 5

Following with Vojtech Ruzicka's approach, you can use something like this your_value:COALESCE(line.your_value, 'default value')

Link to the documentation here, in case you need more information.

Martinsen