Impossible to merge with zero values; 'Unable to merge node using null property value' in neo4j

Question

I have a csv column that looks like this:

I use this code to check how the date schedule works:

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 expect, it is a set of three values ​​for each date, or possibly null if there was no date (e.g.

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

My question is: what is the problem with creating zeros, and why can't I do MERGE with zeros?

 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 an error:

 Cannot merge node using null property value for date_of_birth 

I searched and found another SO question about this error, which has no answer. Other searches did not help.

I got the impression that if there is no value, then Neo4j just does not create an element.

I figured it might be that node cannot be generated, because in the end, how can you create a node if there is no value to create it? So, since I know that the identifier is missing, maybe I could have MERGE with the id and date, so Neo4j always sees the value.

But this code didn't get 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 due to the fact that I'm trying to split a null value into slashes? Perhaps the whole problem is with SPLIT .

But alas, the same error if simplified:

 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 really don’t understand the cause of the error. Thanks for looking at this.

EDIT

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

Answer 1

As @cybersam said merging does not work well with queries where properties are set in a scope with a null value. Thus, you can use when creating and according to :

 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, such as MERGE , do not work with NULL values.

A somewhat complicated workaround to handle this situation with MERGE is to use the FOREACH to conditionally execute MERGE . This query may 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 prefer is to simply tell cypher to skip lines where 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]