Search code examples
neo4jcountcyphermatchneo4j-apoc

Cypher query to count the number of relationships of specific type that each node has, including same type relationships in their sub-nodes

I have a node hierarchy like following, where nodes c1..c6 are of type :Category, and their sub-nodes i1..i7 are of type :Item.

enter image description here

What I need to obtain is the number of Items in each Category, including their sub-categories. The output should look like this:

category    childCount  itemCount
   c1           5           7
   c2           2           4
   c3           1           3
   c4           0           2
   c5           0           1
   c6           0           2

Currently I have a query that returns the correct amount of child nodes, but the number of items is only displayed for each node, not summed up in total. Not sure if I'm missing anything here, or if this is not the right approach?

It's important to note that I can't rely on specifying the starting node myself, as it can change over time inside the db, therefore the query should start with the Category node that does not have a parent.

MATCH p = (c:Category)-[:IS_PARENT_OF *0..]->(c)
WITH c, apoc.text.join("1" + [rel in relationships(p) | rel.index], '.') as path, size((:Category)<-[:IS_PARENT_OF*]-(c)) as childCount, size((:Item)-[:IS_CATEGORIZED_AS]->(c)) as itemCount, c.name AS name
ORDER BY path
RETURN name, childCount, itemCount

Output as it is now:

category    childCount  itemCount
   c1           5           0
   c2           2           1
   c3           1           1
   c4           0           2
   c5           0           1
   c6           0           2
Solution

  • For future visitors, this is the solution from an answer I got from neo4j online community:

    MATCH (category:Category)
OPTIONAL MATCH (category)-[:IS_PARENT_OF*..10]->(c)
OPTIONAL MATCH (category)<-[:IS_CATEGORIZED_AS]-(item1:Item)
OPTIONAL MATCH (c)<-[:IS_CATEGORIZED_AS]-(item2:Item)
RETURN category.name AS category,
   count(DISTINCT(c)) AS childCount,
   count(DISTINCT(item1)) + count(DISTINCT(item2)) AS itemCount

    For more details see here:

    https://community.neo4j.com/t/cypher-query-to-count-the-number-of-relationships-of-specific-type-that-each-node-has-including-same-type-relationships-in-their-sub-nodes-ask-question/17987