how do I delete duplicate relationships between two nodes with cypher?

Question

When I run this query:

START n1=node(7727), n2=node(7730)
MATCH n1-[r:SKILL]->n2 RETURN r

it gives me a list of duplicate relationships that I have between the two nodes. what do I add to the cypher query to iterate over the relationship to keep one relationship and delete the rest?

Teratogenic

Answer 1

To do this for two known nodes:

start n=node(1), m=node(2) match (n)-[r]->(m) 
with n,m,type(r) as t, tail(collect(r)) as coll 
foreach(x in coll | delete x)

To do this globally for all relationships (be warned this operation might be very expensive depending on the size of your graph):

start r=relationship(*) 
match (s)-[r]->(e)
with s,e,type(r) as typ, tail(collect(r)) as coll 
foreach(x in coll | delete x)

Cutter

In recent versions of neo4j, I believe the START clause is unnecessary, right? It seems to work the same without it. – Leralerch

I didn't understand how this worked until I realised tail returns all but the first element. thanks! – Nielson

I was wondering where you found the "relationship" function you use? I don't see it in the documentation anywhere, only "relationships". Is it an older function? – Inwrought

this has been removed long time ago. These days you would just omit the first line and begin the statement with match. – Cutter

Answer 2

With Neo4J 4.x and to globally remove duplicate relationships, you'll want to use the following instead. The syntax has changed slightly and the start prefix mentioned in the other reply no longer works.

match ()-[r]->() 
match (s)-[r]->(e) 
with s,e,type(r) as typ, tail(collect(r)) as coll 
foreach(x in coll | delete x)

Stirpiculture

Note, why the first line? (I think it is not needed) match ()-[r]->() – Phung

why wouldn't match ()-[r:RELTYPE]-() with type(r) as typ, tail(collect(r)) as coll foreach(x in coll | delete x); work instead? Why does it delete all the relationships with that type? – Canoewood

Answer 3

If you have trust issues about random queries that deletes data from the DB you can do what I did.

First you might want to check if the selected relationships are really duplicates. This query will set a property willBeDeleted to true, so you can check if you really want to delete those.

match (a)-[r]->(b) 
with a,b,type(r) as typ, tail(collect(r)) as coll 
foreach(x in coll | set x.willBeDeleted=true)

Now you can check which relationships will be deleted actually.

match(a)-[r]-(b)
where r.willBeDeleted=true
return a, b, r

If you think the right relationships will be deleted, then you can execute this query to delete the duplicates.

match (a)-[r]->(b) 
with a,b,type(r) as typ, tail(collect(r)) as coll 
foreach(x in coll | delete x)

Novokuznetsk

Answer 4

Here's a great way to find all your duplicate relationships and get some visibility into where they are happening.

MATCH p=(n)-[r1]->(g)<-[r2]-(n)
WHERE type(r1) = type(r2) AND r1 <> r2
RETURN type(r1) as relType, labels(n), labels(g), count(p)

Ramirez

Answer 5

you can use mergeRelationships:

MATCH (n1)-[r:SKILL]->(n2) RETURN r
WITH n1, n2, COLLECT(r) AS rels
WHERE SIZE(rels) > 1
CALL apoc.refactor.mergeRelationships(rels, {properties:"discard"})
YIELD rel RETURN rel

about the operation types:

• overwrite - last property in list wins
• discard - the property from the first node will remain if already set, otherwise the first property in list will be written
• combine - if there is only one property in list, it will be set / kept as single property otherwise create an array, tries to coerce values

from official docs: https://neo4j.com/labs/apoc/4.1/overview/apoc.refactor/apoc.refactor.mergeRelationships/

Alost