CODEWITHSUNDEEP
neo4jcypher
Monica Heddneck
Monica Heddneck

Reputation: 3125

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

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

enter image description here

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

Upvotes: 14

Views: 19283

Answers (5)

cybersam
cybersam

Reputation: 66989

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

Upvotes: 13

stdob--
stdob--

Reputation: 29172

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

Upvotes: 17

Jonan87
Jonan87

Reputation: 51

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.

Upvotes: 0

Tshimanga
Tshimanga

Reputation: 885

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]

Upvotes: 6

Val
Val

Reputation: 61

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

Upvotes: 6

Related Questions