How to delete nodes and relationship by using aggregate function on a value

Question

I am using neo4j for the first time, and its fun using such an interactive database, but currently i got stuck in a problem, i have a data of people(uid,first name,last name, skills) , i also have a relationship [:has_skill] my result frame looks like - p1 has a skill s (Robert has skill java) I need to find out how many people have common skills, so i tried the following cypher query

match (p1:People)-[:has_skill]->(s:Skill)<-[:has_skill]-(p2:People) where p1.people_uid="49981" and p2.people_uid="34564" return p1.first_name+' '+p1.last_name as Person1, p2.first_name+' '+p2.last_name as Person2,s.skill_name,s.skillid,count(s)

i am getting p1 as different persons, but due to high skill set, the p2 person is getting repeated, and also the skill is not changing, i tried to delete every node and relationship where skill count of a person is greater then 6 to get good results, but cannot delete it, i am getting "invalid use of aggregating function" This is my attempt to delete

match (p1:People)-[:has_skill]->(s:Skill) where count(s)>6 detach delete p1,s

Please if anyone could guide or correct me where i am going wrong, your help would be highly appreciable . Thanks in advance.

Answer 1

Make sure when using count or other aggregating functions, they are within a WITH clause or a RETURN clause - seems to be a design decision that Neo Technology made when creating Neo4j - see some of the following links for similar cases to yours:

How to count the number of relationships in Neo4j

Neo4j aggregate function

I need to count the number of connection between two nodes with a certain property

Also - see the WITH clause documentation here and the RETURN clause documentation here, in particular, this part of the WITH documentation:

Another use is to filter on aggregated values. WITH is used to introduce aggregates which can then be used in predicates in WHERE. These aggregate expressions create new bindings in the results. WITH can also, like RETURN, alias expressions that are introduced into the results using the aliases as the binding name.

In your case, you are going to want your aggregate function to be used within a WITH clause because you need to use WHERE afterwards to filter only those persons with more than 6 skills. You can use the following query to see which persons have more than 6 skills:

match (p1:People)-[r:has_skill]->(s:Skill)
with p1,count(s) as rels, collect (s) as skills
where rels > 6
return p1,rels,skills

After confirming that the result set is correct, you can use the following query to delete the persons who have more than 6 skills along with all the skill nodes that these persons are related to:

MATCH(p1:People)-[r:has_skill]->(s:Skill)
WITH p1,count(s) as rels, collect (s) as skills
WHERE rels > 6
FOREACH(s in skills | DETACH DELETE s)
DETACH DELETE p1