How to include specific relationship counts in Cypher query

Question

Given this schema…

…and this sample data set…

…how can I write a Cypher query that will return this data, including the counts…

More details

• The :Keywords form a directed, acyclic tree with the :Category at the root.
• Each :Keyword has 0 or more :Content items.
• Although every :Keyword has exactly one parent :Keyword (or the :Category), the :Keyword ↔️ :Content relationship is many-to-many.

Query Requirements

• I'm hoping to make this a single query based on knowing only the ID for the selected node, shown in red: 🔴.
• Counts show number of distinct content nodes attached to the selected Keyword's sub-tree. See the 3rd image attachment for example counts.
• The query should include the black content 🔹's as shown as the goal is to display thumbnail images of all content directly tagged with the current selection.

Answer 1

Creating your graph:

The first statement creates the nodes, the second the relationships between them.

CREATE
  (Root:Category {name: 'Letters'}),
  (KeywordA:KeyWord {name: 'A'}),
  (KeywordAA:KeyWord {name: 'A A'}),
  (KeywordAB:KeyWord {name: 'A B'}),
  (KeywordAC:KeyWord {name: 'A C'}),
  (KeywordC:KeyWord {name: 'C'}),
  (KeywordCA:KeyWord {name: 'C A'}),
  (KeywordCB:KeyWord {name: 'C B'}),
  (KeywordD:KeyWord {name: 'D'}),
  (KeywordDA:KeyWord {name: 'D A'}),
  (KeywordB:KeyWord {name: 'B'}),
  (KeywordBA:KeyWord {name: 'B A'}),
  (KeywordBAA:KeyWord {name: 'B A A'}),
  (KeywordBAB:KeyWord {name: 'B A B'}),
  (KeywordBAC:KeyWord {name: 'B A C'}),
  (KeywordBB:KeyWord {name: 'B B'}),
  (KeywordBBA1:KeyWord {name: 'B B A'}),
  (KeywordBBA2:KeyWord {name: 'B B A'}),
  (KeywordBBA3:KeyWord {name: 'B B A'}),
  (KeywordBBA4:KeyWord {name: 'B B A'}),
  (KeywordBBA5:KeyWord {name: 'B B A'}),
  (Content18:Content {name: '18'}),
  (Content19A:Content {name: '19'}),
  (Content19B:Content {name: '19'}),
  (Content20:Content {name: '20'}),
  (Content1:Content {name: '1'}),
  (Content2:Content {name: '2'}),
  (Content3A:Content {name: '3'}),
  (Content3B:Content {name: '3'}),
  (Content4:Content {name: '4'}),
  (Content5:Content {name: '5'})

CREATE
  (Root)-[:CONTAINS]->(KeywordA),
  (KeywordA)-[:CONTAINS]->(KeywordAA),
  (KeywordA)-[:CONTAINS]->(KeywordAB),
  (KeywordA)-[:CONTAINS]->(KeywordAC),
  (Root)-[:CONTAINS]->(KeywordC),
  (KeywordC)-[:CONTAINS]->(KeywordCA),
  (KeywordC)-[:CONTAINS]->(KeywordCB),
  (Root)-[:CONTAINS]->(KeywordD),
  (KeywordD)-[:CONTAINS]->(KeywordDA),
  (Root)-[:CONTAINS]->(KeywordB),
  (KeywordB)-[:CONTAINS]->(KeywordBA),
  (KeywordBA)-[:CONTAINS]->(KeywordBAA),
  (KeywordBA)-[:CONTAINS]->(KeywordBAB),
  (KeywordBA)-[:CONTAINS]->(KeywordBAC),
  (KeywordB)-[:CONTAINS]->(KeywordBB),
  (KeywordBB)-[:CONTAINS]->(KeywordBBA1),
  (KeywordBB)-[:CONTAINS]->(KeywordBBA2),
  (KeywordBB)-[:CONTAINS]->(KeywordBBA3),
  (KeywordBBA2)-[:CONTAINS]->(KeywordBBA4),
  (KeywordBBA2)-[:CONTAINS]->(KeywordBBA5),
  (KeywordCB)-[:DESCRIBES]->(Content18),
  (KeywordDA)-[:DESCRIBES]->(Content19B),
  (KeywordBAA)-[:DESCRIBES]->(Content19A),
  (KeywordBAA)-[:DESCRIBES]->(Content1),
  (KeywordBAB)-[:DESCRIBES]->(Content20),
  (KeywordBB)-[:DESCRIBES]->(Content1),
  (KeywordBB)-[:DESCRIBES]->(Content2),
  (KeywordBB)-[:DESCRIBES]->(Content3A),
  (KeywordBBA1)-[:DESCRIBES]->(Content4),
  (KeywordBBA1)-[:DESCRIBES]->(Content5),
  (KeywordBBA4)-[:DESCRIBES]->(Content5),
  (KeywordBBA5)-[:DESCRIBES]->(Content3B);

Graphical representation.

Selecting your sub graph:

1. defining your starting point, the category
2. any number of keywords until reaching your selected keyword
3. defining your selected keyword
4. any number of keywords until reaching a content node
5. hand over the parameter (id) for your selected keyword
6. retrieve all identified nodes of step 1 to 5

Statement:

//                  |----------- (1) -----------| |--- (2) --|  |--------- (3) ---------| |--------- (4) --------|
MATCH keywordPath = (:Category {name: 'Letters'})-[:CONTAINS*]->(selectedKeyword:KeyWord)-[:CONTAINS*]->(:KeyWord)
  //    |-------- (5) ---------|
  WHERE id(selectedKeyword) = 15
UNWIND
// |------------ (6) -----------|
nodes(keywordPath) AS keywordNode
MATCH contentPath = (keywordNode:KeyWord)-[contentRelationship:DESCRIBES]->(contentNode:Content)
RETURN keywordPath,contentPath;

Your desired solution:

As far as I understood, you are interested in the relationships towards the content nodes to display according thumbnails. You can retrieve them by the following Cypher statement:

MATCH keywordPath = (:Category {name: 'Letters'})-[:CONTAINS*]->(selectedKeyword:KeyWord)-[:CONTAINS*]->(:KeyWord)
  WHERE id(selectedKeyword) = 15
UNWIND
nodes(keywordPath) AS keywordNode
MATCH contentPath = (keywordNode:KeyWord)-[contentRelationship:DESCRIBES]->(contentNode:Content)
RETURN contentPath;

---

Update: query for keyword related content counts

Statement:

MATCH keywordPath = (:Category {name: 'Letters'})-[:CONTAINS*]->(selectedKeyword:KeyWord)-[:CONTAINS*]->(:KeyWord)
  WHERE id(selectedKeyword) = 15
UNWIND
nodes(keywordPath) AS keywordNode
WITH DISTINCT keywordNode
MATCH contentPath = (keywordNode:KeyWord)-[*]->(contentNode:Content)
RETURN keywordNode, count(DISTINCT contentNode);

Result

╒════════════════╤═════════════════════════════╕
│"keywordNode"   │"count(DISTINCT contentNode)"│
╞════════════════╪═════════════════════════════╡
│{"name":"B B A"}│2                            │
├────────────────┼─────────────────────────────┤
│{"name":"B B A"}│1                            │
├────────────────┼─────────────────────────────┤
│{"name":"B B A"}│2                            │
├────────────────┼─────────────────────────────┤
│{"name":"B B A"}│1                            │
├────────────────┼─────────────────────────────┤
│{"name":"B"}    │8                            │
├────────────────┼─────────────────────────────┤
│{"name":"B B"}  │6                            │
└────────────────┴─────────────────────────────┘

Answer 2

ON HOLD If I understand your question right.
This query can help you:

MATCH (n:Keyword {name:"B B"})-[*1..6]->(c:Content)
RETURN COUNT(DISTINCT c)

You can change 1..6, but set relation to [*] is great time consuming base on db schema, It's better to set a range for traversing relations.