Unique constraints on node properties after the fact

Question

I have a site in beta and it includes nodes called Topics. I forgot to add the unique constraint to the name property and now there are duplicates of nodes. I used MERGE hoping it would find the unique names and just add the relationships but apparently that isn't the case. Is there anyway to merge all the relationships to a single node and then remove the duplicates so I can add the constraint once its cleaned up?

I found this but I have multiple nodes with different relationships. Anyone know how to modify it to work for my situation?

//Edit

Using Michael's code, I modified the block to this:

MATCH (t:Topic)
WITH t.name as name, collect(t) as topics, count(*) as cnt
WHERE cnt > 1
WITH head(topics) as first, tail(topics) as rest
LIMIT 1000
UNWIND rest AS to_delete
OPTIONAL MATCH (to_delete)-[r:TOPIC_OF]->(g:Group)
FOREACH (x in case when r is null then [] else [1] |
   MERGE (first)-[new_r:TOPIC_OF]->(g)
   // in case you have to copy-rel-properties
   SET new_r = r
   DELETE r
)
OPTIONAL MATCH (to_delete)<-[r2:INTEREST_OF]-(p:Person)
FOREACH (x in case when r2 is null then [] else [1] |
   MERGE (first)-[new_r2:INTEREST_OF]->(p)
   // in case you have to copy-rel-properties
   SET new_r2 = r2
   DELETE r2
)

OPTIONAL MATCH (to_delete)<-[r3:SKILL_OF]-(p:Person)
FOREACH (x in case when r3 is null then [] else [1] |
   MERGE (first)-[new_r3:SKILL_OF]->(p)
   // in case you have to copy-rel-properties
   SET new_r3 = r3
   DELETE r3
)

OPTIONAL MATCH (to_delete)-[r4:TOPIC]->(p:Project)
FOREACH (x in case when r4 is null then [] else [1] |
   MERGE (first)-[new_r4:TOPIC]->(p)
   // in case you have to copy-rel-properties
   SET new_r4 = r4
   DELETE r4
)

OPTIONAL MATCH (to_delete)-[r5:NEEDS]->(p:Project)
FOREACH (x in case when r5 is null then [] else [1] |
   MERGE (first)-[new_r5:NEEDS]->(p)
   // in case you have to copy-rel-properties
   SET new_r5 = r5
   DELETE r5
)
DELETE to_delete
RETURN count(*);

I am getting an error while running it:

Invalid input '|': expected whitespace, comment, '.', node labels, '[', "=~", IN, STARTS, ENDS, CONTAINS, IS, '^', '*', '/', '%', '+', '-', '=', "<>", "!=", '<', '>', "<=", ">=", AND, XOR, OR or END (line 8, column 52 (offset: 276))
"FOREACH (x in case when r is null then [] else [1] |"

What am I missing?

Are you using neoxygen/neoclient ? – Christophe Willemsen Nov 12 '15 at 21:27 — Christophe Willemsen, Nov 12 '15 at 21:27
Yes I am using Neoclient – Wally Kolcz Nov 17 '15 at 14:22 — Wally Kolcz, Nov 17 '15 at 14:22

score 1 · Answer 1 · answered Nov 18 '15 at 01:15

You can modify it slightly to deal with more rels:

MATCH (t:Topic)
WITH t.name as name, collect(t) as topics, count(*) as cnt
WHERE cnt > 1
WITH head(topics) as first, tail(topics) as rest
LIMIT 1000
UNWIND rest AS to_delete
OPTIONAL MATCH (to_delete)-[r:TOPIC_OF]->(g:Group)
FOREACH (x in case when r is null then [] else [1] |
   MERGE (first)-[new_r:TOPIC_OF]->(g)
   // in case you have to copy-rel-properties
   SET new_r = r
   DELETE r
)
OPTIONAL MATCH (to_delete)-[r2:TOPIC_OF2]->(g:Group)
FOREACH (x in case when r is null then [] else [1] |
   MERGE (first)-[new_r2:TOPIC_OF2]->(g)
   // in case you have to copy-rel-properties
   SET new_r2 = r2
   DELETE r2
)
DELETE to_delete
RETURN count(*);

I edited my initial question to include your code block with some modifications but i am not sure I did it right and I am getting an error — Wally Kolcz, Nov 18 '15 at 14:11

score 0 · Answer 2 · edited May 23 '17 at 12:30

I had a similar issue (I think) - see my question here. Although my question was not answered, the technique and queries I set out did resolve my issue. Is this at all helpful?

Queries I used as outlined were similar to

// get all outgoing relationships
MATCH (a:Label1 { title : 'blah' })-[r]->(o)
RETURN r
// returns FOO and BAR

// for each relationship type, create one from (d) and copy the properties over
MATCH (a:Label1 { title : 'blah' })-[r:FOO]->(o), (d:Label1 { title : 'blah blah' })
CREATE (d)-[r2:FOO]->(o)
SET r2 = r
...etc...

// now do the same for incoming relationships
MATCH (a:Label1 { title : 'blah' })<-[r]-(o)
RETURN r
// returns FOO and BAR

// for each relationship type, create one from (d) and copy the properties over
MATCH (a:Label1 { title : 'blah' })<-[r:FOO]-(o), (d:Label1 { title : 'blah blah' })
CREATE (d)<-[r2:FOO]-(o)
SET r2 = r
...etc...

// finally delete node and relationships (if required)
MATCH (a:Label1 { title : 'blah' })-[r]-(o)
DELETE r, a

Of course this will only help if you can separately identify the nodes identified by a and d...

Unique constraints on node properties after the fact

2 Answers2