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

Answer 1

As @cybersam said merge not 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

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, '/')})
);

Answer 3

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

Answer 4

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]