How to include properties with NULL values using Neo4j MERGE

Question

I have a Node table and an Edge table, both available as CSV files. I managed to load the Node table by:

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM 'file:///NodesETL.csv' AS line
CREATE (:InfoNodes {id: toString(line.id), description: toString(line.description)})

This query creates the InfoNodes with the field values of the CSV file as properties of :InfoNodes which is fine.

InfoNodes have relationships with other InfoNodes, e.g. these relationships exists between Nodes with the same label. These relationships are stored in an Edge table available as an additional CSV file. Every row of this Edge table holds idfrom and idto fields that defines the relationships between InfoNodes on basis of their id property. The Edge table holds also 3 additional fields representing properties of the relationship. The firstproperty is always a string and never NULL e.g. never an empty string. The secondproperty and thirdproperty, both type string, can have NULL values like "". So secondproperty and/or thirdproperty can contain NULL values. I try to use this Edge table to create the [:RELATIONSHIP {firstproperty:, secondproperty:, thirdproperty:}] relationships between (:InfoNodes) by:

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM 'file:///EdgesETL.csv' AS line
MATCH (from:InfoNodes{id: toString(line.idfrom)})
MATCH (to:InfoNodes{id: toString(line.idto)})
MERGE (from)-[:RELATION {firstproperty: toString(line.firstproperty), secondproperty: toString(line.secondproperty), thirdproperty: toString(line.thirdproperty)}]->(to)

This second Cypher script results into an error when secondproperty and thirdproperty in the Edge table contain NULL values. The Neo4j error message is: Cannot merge relationship using null property value for secondproperty. When I remove from the second script the secondproperty field and secondproperty: property than the same type of error occurs mentioning thirdproperty: Cannot merge relationship using null property value for thirdproperty When I remove secondproperty and thirdproperty fields and properties from the previous script then the [:RELATIONS] relationships between InfoNodes are created including firstproperty table fields stored as firstproperty: property of the [:RELATION] relationship.

Question: How to extend the second script in order to load from the Edge table the second property and thirdproperty fields into secondproperty: and thirdproperty: of [:RELATION] relationships including NULL values?

Can't MERGE with null values; 'Cannot merge node using null property value' in neo4j describes the same problem but doesn't answer my question in case of multiple fields/properties with NULL values.

Are there multiple relationships between the same nodes, but with different values for some of the properties? Or are the (idfrom, idto) pairs all unique? — Frank Pavageau
:InfoNodes can have 1 or more relations to other :InfoNodes but in case of multiple relationships each relationship has an own label. So multiple relationships between InfoNods do have their own unique idfrom/idto in the Edge table with their own relationship properties as fields. — Luc

InverseFalcon InverseFalcon · Accepted Answer · 2016-11-24T22:10:38

You'll want to re-review the MERGE section in the developer guide. Specifically, in the introduction, there's mention of ON CREATE and ON MATCH. This allows you to set properties in cases where the MERGE resulted in a creation, or when instead the MERGE matched upon existing elements.

Typically you will want to MERGE only properties that uniquely define the thing, like IDs, and set the rest of the properties within ON CREATE.

Your query after this change might look something like this:

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM 'file:///EdgesETL.csv' AS line
MATCH (from:InfoNodes{id: toString(line.idfrom)})
MATCH (to:InfoNodes{id: toString(line.idto)})
MERGE (from)-[r:RELATION {firstproperty: toString(line.firstproperty)}]->(to)
ON CREATE SET r.secondproperty = toString(line.secondproperty), r.thirdproperty = toString(line.thirdproperty)

How to include properties with NULL values using Neo4j MERGE

1 Answers