'Cannot merge the following node because of null property value' using UNWIND not csv

[paul_thomas1]

So I've seen lots of posts about 'Cannot merge the following node because of null property value' but most are trying to use a csv file. We have an Excel file that we parse, using Knime as our ETL tool, into small chunks of 50 that we then pass into Neo4j. As long as the data is as expected then everything is fine. I'm testing cases of missing data and trying to use the UNWIND but not having muck luck.

**below is an example of what I'm trying to do not actual data from work.

This was my first attempt, missing MiddleName and Relation.

UNWIND[
	{FirstName: "John", LastName: "Smith", MiddleName: "Tom", Relation: "Cousin"},
	{FirstName: "Jane", LastName: "Smith", MiddleName: "Dunn", Relation: "Cousin"},
	{FirstName: "Bob", LastName: "Smith"},
	{FirstName: "Sue", LastName: "Smith", MiddleName: "Mum", Relation: "Cousin"},
	{FirstName: "Bill", LastName: "Smith", MiddleName: "Aaron", Relation: "Cousin"},
	{FirstName: "Kris", LastName: "Smith", MiddleName: "Hens", Relation: "Cousin"},
	{FirstName: "Anne", LastName: "Smith", MiddleName: "Ches", Relation: "Cousin"}
] as dataSet
MERGE(p:Person {FirstName: dataSet.FirstName, LastName: dataSet.LastName, MiddleName: dataSet.MiddleName})
MERGE(r:Relation {Name: dataSet.Relation})
MERGE(p)-[h:HAS_RELATIVE]->(r)

-- I get the 'Cannot merge...null' error

Using WITH.
I add MiddleName just to get to the relationship which I am more concerned with.

UNWIND[
	{FirstName: "John", LastName: "Smith", MiddleName: "Tom", Relation: "Cousin"},
	{FirstName: "Jane", LastName: "Smith", MiddleName: "Dunn", Relation: "Cousin"},
	{FirstName: "Bob", LastName: "Smith", MiddleName: "Dunn"},
	{FirstName: "Sue", LastName: "Smith", MiddleName: "Mum", Relation: "Cousin"},
	{FirstName: "Bill", LastName: "Smith", MiddleName: "Aaron", Relation: "Cousin"},
	{FirstName: "Kris", LastName: "Smith", MiddleName: "Hens", Relation: "Cousin"},
	{FirstName: "Anne", LastName: "Smith", MiddleName: "Ches", Relation: "Cousin"}
] as dataSet
MERGE(p:Person {FirstName: dataSet.FirstName, LastName: dataSet.LastName, MiddleName: dataSet.MiddleName})
WITH dataSet WHERE dataSet.Relation IS NOT NULL
MERGE(r:Relation {Name: dataSet.Relation})
MERGE(p)-[h:HAS_RELATIVE]->(r)

-- This works for Person but creates a blank node

MATCH(n) RETURN n
╒════════════════════════════════════════════════════════════╕
│"n" │
╞════════════════════════════════════════════════════════════╡
│{"LastName":"Smith","MiddleName":"Tom","FirstName":"John"} │
├────────────────────────────────────────────────────────────┤
│{"LastName":"Smith","MiddleName":"Dunn","FirstName":"Jane"} │
├────────────────────────────────────────────────────────────┤
│{"LastName":"Smith","MiddleName":"Dunn","FirstName":"Bob"} │
├────────────────────────────────────────────────────────────┤
│{"LastName":"Smith","MiddleName":"Mum","FirstName":"Sue"} │
├────────────────────────────────────────────────────────────┤
│{"LastName":"Smith","MiddleName":"Aaron","FirstName":"Bill"}│
├────────────────────────────────────────────────────────────┤
│{"LastName":"Smith","MiddleName":"Hens","FirstName":"Kris"} │
├────────────────────────────────────────────────────────────┤
│{"LastName":"Smith","MiddleName":"Ches","FirstName":"Anne"} │
├────────────────────────────────────────────────────────────┤
│{"Name":"Cousin"} │
├────────────────────────────────────────────────────────────┤
│{} │
└────────────────────────────────────────────────────────────┘

Suggestions?

*** This appears to work though I haven't dug into it enough to explain.

UNWIND[
	{FirstName: "John", LastName: "Smith", MiddleName: "Tom", Relation: "Cousin"},
	{FirstName: "Jane", LastName: "Smith", MiddleName: "Dunn", Relation: "Cousin"},
	{FirstName: "Bob", LastName: "Smith", MiddleName: ""},
	{FirstName: "Sue", LastName: "Smith", MiddleName: "Mum", Relation: "Cousin"},
	{FirstName: "Bill", LastName: "Smith", MiddleName: "Aaron", Relation: "Cousin"},
	{FirstName: "Kris", LastName: "Smith", MiddleName: "Hens", Relation: "Cousin"},
	{FirstName: "Anne", LastName: "Smith", MiddleName: "Ches", Relation: "Cousin"}
] as dataSet
MERGE(p:Person {FirstName: dataSet.FirstName, LastName: dataSet.LastName, MiddleName: dataSet.MiddleName})
FOREACH(x IN CASE WHEN dataSet.Relation IS NULL THEN [] ELSE [1] END |
	MERGE(r:Relation {Name: dataSet.Relation})
	MERGE(p)-[h:HAS_RELATIVE]->(r)
)

Its from stackoverflow ( cypher - Can't MERGE with null values; 'Cannot merge node using null property value' in neo4j - Stac... ) and not the chosen solution though the original poster says there are 2 solutions.

[ameyasoft]

paul.thomas1:

FOREACH(x IN CASE WHEN dataSet.Relation IS NULL THEN [] ELSE [1] END |
	MERGE(r:Relation {Name: dataSet.Relation})
	MERGE(p)-[h:HAS_RELATIVE]->(r)
)

Try this:
dataSet.Relation IS NOT NULL :

FOREACH(x IN CASE WHEN dataSet.Relation IS NOT NULL THEN [] ELSE [1] END |
	MERGE(r:Relation {Name: dataSet.Relation})
	MERGE(p)-[h:HAS_RELATIVE]->(r)
)

[andrew_bowman]

Your earlier query where the WITH dataSet WHERE dataSet.Relation IS NOT NULL guards the remaining query (merge on relation and merge of relationship) is a good approach, I would stick with that.

Regarding creation of a blank node, I don't see how that query could create a blank node, it is possible that it was created by some earlier run of the query and not cleaned up before testing again.

If you want to supply a default value in case the value doesn't exist (or is null) in the input data, you can use coalesce(), such as

MERGE(p:Person {FirstName: coalesce(dataSet.FirstName, ""), LastName: coalesce(dataSet.LastName, ""), MiddleName: coalesce(dataSet.MiddleName, "")})