How can I find and remove nodes for which two of a given relationship exist?

1
votes

I have a requirement that in my database some nodes should have at most one relationship of a given type. For example, nodes with the label Heart should be connected via has to at most one node of label Body.

Here's some test data to illustrate the issue:

CREATE (h:Heart {animal: "zebra"})
CREATE (b1:Body {animal: "zebra"})
CREATE (b2:Body {animal: "elephant"})
CREATE (b1)-[:has]->(h)
CREATE (b2)-[:has]->(h)

CREATE (h2:Heart {animal: "mouse"})
CREATE (b4:Body {animal: "mouse"})
CREATE (b5:Body {animal: "leopard"})
CREATE (b4)-[:has]->(h2)
CREATE (b5)-[:has]->(h2)

With the following query I can find all nodes with connections which don't respect this rule:

MATCH (n:Heart) WHERE (:Body)-[:has]-(n)-[:has]-(:Body) RETURN n

I'd like though, to write a statement to remove the "extra" relationships. Right now I've got a query to remove the nodes composing these "extra" relationships, but it also removes the nodes I'd like to keep (one of each).

MATCH (b1:Body)-[:has]-(n)-[:has]-(b2:Body) DETACH DELETE b1, b2

This also doesn't group relationships by the center node, so I can't really remove all nodes except one, as it would not have the desired effect.

Does anyone know how can I write a statement that will remove all "extra" relationships until only one relationship has remains for each Heart node? Preferably the oldest one.

2
neo4jcypher

2 Answers

2
votes

The statement below deletes all relationships except for one. You'll have to add logic to determine which is the one that you want to keep though :)

MATCH (:Body)-[r:has]->(h:Heart)
WITH h,COLLECT(r) AS rs
WHERE SIZE(rs)> 1
FOREACH (r IN rs[1..] |
    DELETE r
)
1
votes

You might be able to delete extra attachments via the following code

MATCH (b:Body)-[r:has]->(h:Heart)
WITH b, h
ORDER BY id(b)   // you can order it by something else if you have a better created date identifier
WITH collect(b) as bodies, h   // collect turns nodes into a list
WHERE size(bodies) > 1   // size is how large the list is
WITH tail(bodies) as bodies, h   // tail grabs all elements except first
UNWIND bodies as b   // turns them back into individual nodes
WITH b, h
MATCH (b)-[r:has]->(h)
DELETE r;

This works on a small setup I made, but I'm not too good to confirm that it works, or if there's a more succinct version of this.