Cypher Query to get duplicate(same id) relationship between nodes

Question

I have city nodes connected to each other by HAS_BUS relationships. eg.

CREATE (:City{id:123,name:'Mumbai'})-[:HAS_BUS{id:22323,arr_time:234,dept_time:250}]->(:City{id:124,name:'Goa'}).

Though I wanted a bus id to be unique I mistakenly put bus with same id more than once i.e there are non unique HAS_BUS relationships.

Q1.How should I find out which ids are not unique
Q2.How should I find out which ids are not unique and delete them.

I wrote this query but got an unknown error

MATCH ()-[r:HAS_BUS]->() with count(r.id) as t match ()-[s:HAS_BUS]->() where s.id=t with count(s.id) as times,s.id as id where count(times)>1 return id,times

The database contains only 80 nodes and 6500 relationships.

I am actually missing GROUP BY feature of mySQL

DATABASE Can be downloaded from here 6MB

Answer 1

I am assuming that you want the bus id to be unique within your entire graph i.e. bus ID 22323 corresponds to exactly one HAS_BUS relation in the entire graph and is not repeated with different cities.

MATCH (c:City)-[r:HAS_BUS]->() 
WITH r.id as busId, count(*) as idCount
WHERE idCount>1
RETURN busId,idCount

note: not tested

will give you all busIds repeated more than once. Then you can figure out which places you want to delete the duplicates from, or delete all and re-create the correct one.

The group by you're looking for is documented at http://docs.neo4j.org/chunked/milestone/query-aggregation.html

Edit to remove all but one duplicate bus id: Make sure you have a backup of your database- this is NOT tested

MATCH (c:City)-[r:HAS_BUS]->() 
WITH r.id as busId, count(*) as idCount
WHERE idCount>1 //Find all duplicate ids
MATCH (c:City)-[r2:HAS_BUS]->()
WHERE r2.id=busId
with COLLECT(r2) as ids,busId //find all relations for those duplicates
with head(ids) as firstId,ids,busId 
with filter(x in ids where x<>firstId) as idsToDelete //keep the first id and collect the rest
foreach (d in idsToDelete | DELETE d); //delete the rest