# Determine number of node relationships in recursive query

**URL:** <https://community.neo4j.com/t/determine-number-of-node-relationships-in-recursive-query/16103>\
**Category:** Cypher\
**Created:** [March 21, 2020, 4:11pm UTC](https://community.neo4j.com/t/determine-number-of-node-relationships-in-recursive-query/16103 "2020-03-21T16:11:07Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![r.chevalier335](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/r.chevalier335/32/6868_2.png) [@r.chevalier335](https://community.neo4j.com/u/r.chevalier335)\
**Post date:** [March 21, 2020, 4:11pm UTC](https://community.neo4j.com/t/determine-number-of-node-relationships-in-recursive-query/16103/1 "2020-03-21T16:11:08Z")

</div>

My graph consists Nodes that have CHILD\_OF relationships. I'm able to query and generate a tree structure with the following query:

```auto
MATCH p = (:Node { name:"Root" })<-[:CHILD_OF *0..]-(c:Node) with *, relationships(p) as i
with REDUCE (path = '1', index IN i | path + '.' + index.index) AS path, c.name as name
ORDER BY path
RETURN { path: path, name: name }

```

The query returns:

```auto
╒════════════════════════════════════╕
│"{ path: path, name: name }" │
╞════════════════════════════════════╡
│{"path":"1","name":"Root"} │
├────────────────────────────────────┤
│{"path":"1.1","name":"Sub Assy 1"} │
├────────────────────────────────────┤
│{"path":"1.1.1","name":"Part 1"} │
├────────────────────────────────────┤
│{"path":"1.1.2","name":"Part 2"} │
├────────────────────────────────────┤
│{"path":"1.2","name":"Sub Assy 2"} │
├────────────────────────────────────┤
│{"path":"1.2.1","name":"Sub Assy 1"}│
├────────────────────────────────────┤
│{"path":"1.2.1.1","name":"Part 1"} │
├────────────────────────────────────┤
│{"path":"1.2.1.2","name":"Part 2"} │
├────────────────────────────────────┤
│{"path":"1.3","name":"Sub Assy 3"} │
└────────────────────────────────────┘

```

In order to create a new child, I need to determine the number of existing children for the parent node first. When I try to count them I always get a result of one.

```auto
MATCH p = (:Node { name:"Root" })<-[:CHILD_OF *0..]-(c:Node) with *, relationships(p) as i
with COUNT ((:Node { ID: c.ID })<-[:CHILD_OF]-(:Node)) as childcount, i, c
with *, REDUCE (path = '1', index IN i | path + '.' + index.index) AS path, c.name as name
ORDER BY path
RETURN { path: path, name: name, childcount: childcount }

```

Which returns:

```auto
╒════════════════════════════════════════════════════╕
│"{ path: path, name: name, childcount: childcount }"│
╞════════════════════════════════════════════════════╡
│{"path":"1","name":"Root","childcount":1} │
├────────────────────────────────────────────────────┤
│{"path":"1.1","name":"Sub Assy 1","childcount":1} │
├────────────────────────────────────────────────────┤
│{"path":"1.1.1","name":"Part 1","childcount":1} │
├────────────────────────────────────────────────────┤
│{"path":"1.1.2","name":"Part 2","childcount":1} │
├────────────────────────────────────────────────────┤
│{"path":"1.2","name":"Sub Assy 2","childcount":1} │
├────────────────────────────────────────────────────┤
│{"path":"1.2.1","name":"Sub Assy 1","childcount":1} │
├────────────────────────────────────────────────────┤
│{"path":"1.2.1.1","name":"Part 1","childcount":1} │
├────────────────────────────────────────────────────┤
│{"path":"1.2.1.2","name":"Part 2","childcount":1} │
├────────────────────────────────────────────────────┤
│{"path":"1.3","name":"Sub Assy 3","childcount":1} │
└────────────────────────────────────────────────────┘

```

Which is incorrect. "Root" should have 3 children and "Sub Assy 1" should have 2.  
If I do the count a count for "Root" and "Sub Assy 1" all by themselves I get the correct results.

```auto
MATCH (:Node { name:"Root" })<-[i:CHILD_OF]-(c:Node) return count(i)

```

Correctly returns 3

```auto
MATCH (:Node { name:"Sub Assy 1" })<-[i:CHILD_OF]-(c:Node) return count(i)

```

Correctly returns 2

How do I get the correct childcount in my recursive query?

---

<div class="post-metadata">

**Author:** ![intouch\_vivek](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/intouch_vivek/32/4097_2.png) [@intouch\_vivek](https://community.neo4j.com/u/intouch_vivek)\
**Post date:** [March 21, 2020, 6:06pm UTC](https://community.neo4j.com/t/determine-number-of-node-relationships-in-recursive-query/16103/2 "2020-03-21T18:06:59Z")

</div>

@r.chevalier335,

Although it is not a good solution but try

Match (c:Node)-[rel:CHILD\_OF]-\>(d:Node)  
With d.name as name , size(collect(rel)) as childCount  
Return name, childCount  
union  
Match (c:Node)-[rel:CHILD\_OF]-\>(d:Node)  
With c.name as name , size(collect(rel)) as childCount  
Return name, childCount

---

<div class="post-metadata">

**Author:** ![r.chevalier335](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/r.chevalier335/32/6868_2.png) [@r.chevalier335](https://community.neo4j.com/u/r.chevalier335)\
**Post date:** [March 21, 2020, 8:40pm UTC](https://community.neo4j.com/t/determine-number-of-node-relationships-in-recursive-query/16103/3 "2020-03-21T20:40:52Z")

</div>

Thanks Vivek. On its own that indeed returns the number of children for each parent. How does that fit into my recursive query though?

```auto
╒════════════╤════════════╕
│"name" │"childCount"│
╞════════════╪════════════╡
│"Root" │3 │
├────────────┼────────────┤
│"Sub Assy 1"│2 │
├────────────┼────────────┤
│"Sub Assy 2"│1 │
├────────────┼────────────┤
│"Sub Assy 3"│1 │
├────────────┼────────────┤
│"Part 1" │1 │
├────────────┼────────────┤
│"Part 2" │1 │
└────────────┴────────────┘

```

---

<div class="post-metadata">

**Author:** ![andrew\_bowman](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/andrew_bowman/32/73_2.png) [@andrew\_bowman](https://community.neo4j.com/u/andrew_bowman)\
**Post date:** [March 22, 2020, 10:39am UTC](https://community.neo4j.com/t/determine-number-of-node-relationships-in-recursive-query/16103/4 "2020-03-22T10:39:29Z")

</div>

`count()` is an aggregation function that works across rows. In your case, you don't really need aggregations. You have the reference to the node of interest, `c`, already, so what you need is the degree of relationships on `c`, and we can get that using the `size()` function:

`size((c)<-[:CHILD_OF]-()) as childcount`

```auto
MATCH p = (:Node { name:"Root" })<-[:CHILD_OF *0..]-(c:Node) 
WITH reduce (path = '1', rel IN relationships(p) | path + '.' + rel.index) AS path, size((c)<-[:CHILD_OF]-()) as childcount, c.name as name
ORDER BY path
RETURN { path: path, name: name, childcount:childcount}

```

A couple other things that can help...

We can use [map projection](https://neo4j.com/docs/cypher-manual/current/syntax/maps/#cypher-map-projection) to simplify your return, and use `apoc.text.join()` from APOC procedures in place of the reduce() function:

```auto
MATCH p = (:Node { name:"Root" })<-[:CHILD_OF *0..]-(c:Node) 
WITH c, apoc.text.join("1" + [rel in relationships(p) | rel.index], '.') as path, size((c)<-[:CHILD_OF]-()) as childcount
ORDER BY path
RETURN c { .name, path, childcount}

```

---

<div class="post-metadata">

**Author:** ![r.chevalier335](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/r.chevalier335/32/6868_2.png) [@r.chevalier335](https://community.neo4j.com/u/r.chevalier335)\
**Post date:** [March 22, 2020, 2:49pm UTC](https://community.neo4j.com/t/determine-number-of-node-relationships-in-recursive-query/16103/5 "2020-03-22T14:49:49Z")

</div>

Thank you so much Andrew, you are brilliant! I've been struggling with this for a week.

I have no doubt that if I live long enough, I could have eventually found all of this information in the docs. The challenge I have, and I'm sure others struggle with too, is where to start looking when trying to solve a problem like this. Can you offer any suggestions?

---

<div class="post-metadata">

**Author:** ![andrew\_bowman](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/andrew_bowman/32/73_2.png) [@andrew\_bowman](https://community.neo4j.com/u/andrew_bowman)\
**Post date:** [March 23, 2020, 12:37am UTC](https://community.neo4j.com/t/determine-number-of-node-relationships-in-recursive-query/16103/6 "2020-03-23T00:37:21Z")

</div>

Here's a link to one of our important knowledge base articles on [understanding Cypher cardinality](https://neo4j.com/developer/kb/understanding-cypher-cardinality/). I would normally link you to the rest of our Cypher knowledge base articles, but we have a little hiccup there that needs fixing first.

I find it often helps when seeing unexpected results or behavior to go through the query and return at various places to check if the results at that point (table or text results) make sense, and to make sure you understand what rows exist at that point, since further operations will execute per row.

You may also want to review the documentation (and make sure the documentation version matches your Neo4j version) for info on how various functions work.

---

<div class="post-metadata">

**Author:** ![r.chevalier335](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/r.chevalier335/32/6868_2.png) [@r.chevalier335](https://community.neo4j.com/u/r.chevalier335)\
**Post date:** [March 23, 2020, 12:49am UTC](https://community.neo4j.com/t/determine-number-of-node-relationships-in-recursive-query/16103/7 "2020-03-23T00:49:00Z")

</div>

More great advice. Many thanks. Stay healthy, 🙂

---

<div class="post-metadata">

**Author:** ![r.chevalier335](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/r.chevalier335/32/6868_2.png) [@r.chevalier335](https://community.neo4j.com/u/r.chevalier335)\
**Post date:** [March 30, 2020, 1:40am UTC](https://community.neo4j.com/t/determine-number-of-node-relationships-in-recursive-query/16103/8 "2020-03-30T01:40:37Z")

</div>

**Now I want to return the child node with my query.**

```auto
type Node {
	ID: ID!
  name: String
  child: [Child]
}

type Child @relation(name: "CHILD_OF") {
	from: Node #child 	
	to: Node #parent
	index: String
}

type Tree {
	path: String
	name: String
	indent: Int
	childCount: Int
	node:[Node] // return the Node object 
}

type Query {
	tree(root: String):[Tree]
		@cypher(statement:
			"""MATCH p = (:Node { name: $root })<-[:CHILD_OF *0..]-(c:Node) 
			WITH c, apoc.text.join('1' + [rel in relationships(p) | rel.index], '.') as path, size((c)<-[:CHILD_OF]-()) as childCount, c as Node
			ORDER BY path
			RETURN c { .name, path, childCount, indent: size(split(path,'.'))-1, Node}"""
		)
}

```

**Again I get a nice result in the the neo4J browser but not so much in the playground.**

```auto
query
	{tree(root: "Root"){
    path
    indent
    childCount
    node{
      name
    }
	}
}

```

```auto
{
  "errors": [
    {
      "message": "Cannot read property '0' of undefined",
      "locations": [
        {
          "line": 2,
          "column": 3
        }
      ],
      "path": [
        "tree"
      ],
      "extensions": {
        "code": "INTERNAL_SERVER_ERROR",
        "exception": {
          "stacktrace": [
            "TypeError: Cannot read property '0' of undefined",
            " at buildCypherSelection (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/selections.js:315:20)",
            " at recurse (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/selections.js:87:33)",
            " at buildCypherSelection (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/selections.js:173:12)",
            " at recurse (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/selections.js:87:33)",
            " at buildCypherSelection (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/selections.js:173:12)",
            " at recurse (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/selections.js:87:33)",
            " at buildCypherSelection (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/selections.js:173:12)",
            " at customQuery (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/translate.js:558:68)",
            " at translateQuery (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/translate.js:501:12)",
            " at cypherQuery (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/index.js:141:40)"
          ]
        }
      }
    }
  ],
  "data": {
    "tree": null
  }
}

```

**What am I missing here?**