jruts/neo4j_delete_duplicate_nodes.md

## neo4j_delete_duplicate_nodes.md

      
    Raw
  

              neo4j_delete_duplicate_nodes.md
            
          
    How to delete duplicate nodes and their relationships in neo4j with cypher based on a property of that node?

The problem is easy to understand. We have 'duplicate' nodes in our database based on the 'id' field on the node properties.
Well this sounds easy enough, until you have to actually do it.
First step

My first attempt was to try and figure out which nodes are actualy duplicate (based on a property on the node).
This seems to be pretty straightforward.
Cypher:
MATCH (g:geo) 
WITH g.id as id, collect(g) AS nodes 
WHERE size(nodes) >  1
RETURN nodes
This query finds all the nodes with label geo (change this to your own labels) and creates a list of all the
nodes where the id property occurs more than once.

This results into a list of all the geo nodes that ar considered duplicate.
Example Result


nodes


geo:123, geo:123


geo:578, geo:578, geo:578


Second step

Now we want to try and get rid of the duplicate ones, but of course keep 1 remaining.
Cypher:
MATCH (g:geo) 
WITH g.id as id, collect(g) AS nodes 
WHERE size(nodes) >  1
FOREACH (g in tail(nodes) | DELETE g)
This results in this error:
org.neo4j.kernel.api.exceptions.ConstraintViolationTransactionFailureException: Cannot delete node<866>, because it still has relationships. To delete this node, you must first delete its relationships.
Seems like we are close. But lets explain what we did in this query first.

We used the same query as before, but instead of returning the nodes we will loop over tail(nodes) and delete the nodes we find in there.

What tail does is getting all the nodes in the list, except the first one. So we ensure that 1 always remains.
Third step

As the previous error described, we need to remove the relationships of the duplicated first.
Cypher:
MATCH (g:geo) 
WITH g.id as id, collect(g) AS nodes 
WHERE size(nodes) >  1
UNWIND tail(nodes) as tails
MATCH (tails)-[r]-()
DELETE r
And poof, magic happened. This was successful and the relationships of the duplicates are now removed.

This means that we can run the query of step 2 again to delete the actual duplicates!
Solution

Delete duplicate relations of the duplicate nodes first
MATCH (g:geo) 
WITH g.id as id, collect(g) AS nodes 
WHERE size(nodes) >  1
UNWIND tail(nodes) as tails
MATCH (tails)-[r]-()
DELETE r
Delete actual duplicate nodes
MATCH (g:geo) 
WITH g.id as id, collect(g) AS nodes 
WHERE size(nodes) >  1
FOREACH (g in tail(nodes) | DELETE g)
I hope it can help some of you out!