Replacing all relationships between nodes in Neo4j

We've been investigating how to create or update multiple nodes and relationships in our Neo4j database at the same time. We have designed a format which clearly separates the unique Node ID, properties to append to the node, and it's relationships to other nodes:

[
  {
    "id": "123",
    "type": "TypeName",
    "properties": { ... },
    "relationships": [
      {  id: "456", type: "REL_NAME" },
      ...
    ] 
  },
  ...
]

We have also designed a query which can iterate over this data payload to create or update the matching Node and create or update it's relationships:

UNWIND $payloads AS payload

// Create or update the node by type and ID
CALL apoc.merge.node([payload.type], { id: payload.id }, payload.createMetadata, payload.updateMetadata)
YIELD node AS parent
SET parent += payload.properties
WITH payload, parent

// Iterate over the relationships in the payload data and create or update by type and related node ID
UNWIND payload.relationships as relationship

// Create or update the related node
CALL apoc.merge.node([relationship.type], { id: relationship.id }, payload.createMetadata, payload.updateMetadata)
YIELD node AS child
WITH payload, parent, child, relationship

// Create or update the relationship between the parent and child nodes
CALL apoc.merge.relationship(parent, relationship.type, null, payload.createMetadata, child, payload.updateMetadata)
YIELD rel AS relationships

RETURN parent, relationships

This has proven very effective at creating or updating nodes and their relationships, however our intention is for the relationships in the payload to fully replace the existing sets of relationships - that is create, update or delete them if not present in the list.

To try and achieve this we have refactored our query to first collect all of the existing relationships, as oldRels, and filter them by the types of relationships in the payload. Lastly we are comparing these existing relationships to those which have just been created or updated, as newRels:

UNWIND $payloads AS payload

// Create or update the node by type and ID
CALL apoc.merge.node([payload.type], { id: payload.id }, payload.createMetadata, payload.updateMetadata)
YIELD node AS parent
SET parent += payload.properties
WITH payload, parent

// Collect all existing relationships
MATCH (parent)-[oldRel]->()
// Filter relationships to include only those types in the payload
WHERE type(oldRel) IN payload.relationshipTypes
WITH payload, parent, collect(oldRel) AS oldRels

// Iterate over the relationships in the payload data and create or update by type and related node ID
UNWIND payload.relationships as relationship

// Create or update the related node
CALL apoc.merge.node([relationship.type], { id: relationship.id }, payload.createMetadata, payload.updateMetadata)
YIELD node AS child
WITH payload, parent, oldRels, child, relationship

// Create or update the relationship between the parent and child nodes
CALL apoc.merge.relationship(parent, relationship.type, null, payload.createMetadata, child, payload.updateMetadata)
YIELD rel AS newRel

// Remove old relationships that are not present in the created/updated relationships
WITH parent, collect(newRel) AS newRels, oldRels
FOREACH (oldRel IN oldRels |
    FOREACH (_ IN CASE WHEN NOT oldRel IN newRels THEN [true] ELSE [] END |
        DELETE oldRel)
)

RETURN parent, newRels as relationships

This query works as expected when creating, updating or removing some relationships between nodes but it does not work when:

We want to remove all relationships between nodes (i.e. newRels: [])
We want to add relationships to a node without any existing relationships (i.e. oldRels: [])

So far we haven't been able to figure this out - what could we be doing wrong? Or is this strategy of collecting the old relationships and comparing not viable?

Solution

When you want to remove all relationships, that probably means payload.relationships is an empty list. So when you UNWIND payload.relationships as relationship, the rest of the query is not executed.
When you want to add relationships to a node without any existing relationships, MATCH (parent)-[oldRel]->() will not match anything and the rest of the query is not executed.

This query may work better for you:

UNWIND $payloads AS payload

// Create or update the node by type and ID
CALL apoc.merge.node([payload.type], { id: payload.id }, payload.createMetadata, payload.updateMetadata)
YIELD node AS parent
SET parent += payload.properties
WITH payload, parent

// Collect all existing relationships
OPTIONAL MATCH (parent)-[oldRel]->()
// Filter relationships to include only those types in the payload
WHERE type(oldRel) IN payload.relationshipTypes
WITH payload, parent, collect(oldRel) AS oldRels

CALL {
  WITH payload
  WITH payload
  WHERE SIZE(payload.relationships) = 0
  RETURN [] AS newRels
  
  UNION
  
  WITH payload, parent
  WITH payload, parent
  WHERE SIZE(payload.relationships) > 0

  // Iterate over the relationships in the payload data and create or update by type and related node ID
  UNWIND payload.relationships as relationship

  // Create or update the related node
  CALL apoc.merge.node([relationship.type], { id: relationship.id }, payload.createMetadata, payload.updateMetadata)
  YIELD node AS child

  // Create or update the relationship between the parent and child nodes
  CALL apoc.merge.relationship(parent, relationship.type, null, payload.createMetadata, child, payload.updateMetadata)
  YIELD rel
  RETURN COLLECT(rel) AS newRels
}

// Remove old relationships that are not present in the created/updated relationships
FOREACH (oldRel IN [x IN oldRels WHERE NOT x IN newRels] | DELETE oldRel)

RETURN parent, newRels as relationships

It uses post-union processing in a CALL subquery to add a newRels list to every payload, parent, oldRels row, even when payload.relationships is an empty list.
It uses OPTIONAL MATCH (parent)-[oldRel]->() instead of MATCH so that the query does not end if there is no match.