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

Neo4j Graph Platform Cypher
1

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.

Question_SO
Question_SO820×537 42.9 KB

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 by any property myself, as it can change over time inside the db, therefore the query should start with the top 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
2

Hi,
It was a very interesting question and I enjoyed it.
First, I wrote this Cypher.

CREATE (c1:Category {name: 'c1'}),
       (c2:Category {name: 'c2'}),
       (c3:Category {name: 'c3'}),
       (c4:Category {name: 'c4'}),
       (c5:Category {name: 'c5'}),
       (c6:Category {name: 'c6'})
CREATE (i1:Item {name: 'i1'}),
       (i2:Item {name: 'i2'}),
       (i3:Item {name: 'i3'}),
       (i4:Item {name: 'i4'}),
       (i5:Item {name: 'i5'}),
       (i6:Item {name: 'i6'}),
       (i7:Item {name: 'i7'})
CREATE (c1)-[:IS_PARENT_OF]->(c2),
       (c1)-[:IS_PARENT_OF]->(c3),
       (c2)-[:IS_PARENT_OF]->(c4),
       (c2)-[:IS_PARENT_OF]->(c5),
       (c3)-[:IS_PARENT_OF]->(c6)
CREATE (c2)<-[:IS_CATEGORIZED_AS]-(i1),
       (c3)<-[:IS_CATEGORIZED_AS]-(i2),
       (c4)<-[:IS_CATEGORIZED_AS]-(i3),
       (c4)<-[:IS_CATEGORIZED_AS]-(i4),
       (c5)<-[:IS_CATEGORIZED_AS]-(i5),
       (c6)<-[:IS_CATEGORIZED_AS]-(i6),
       (c6)<-[:IS_CATEGORIZED_AS]-(i7)

Next, I wrote this Cypher to count like this.
I'm sure there are better Cypher, but I wrote it without using APOC.

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

I think the results are correct.

count
count702×443 33.2 KB

2 Likes
3

Thank you Koji very much for your effort, that's exactly what I was looking for!

4

Hi,

This Cypher with a label like this would be better.
Changed the 2nd line from "->(c)" to "->(c:Category)".

MATCH (category:Category)
OPTIONAL MATCH (category)-[:IS_PARENT_OF*..10]->(c:Category)
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

May the 4th be with you.