Mongo count for each document

Question

I have a collection of elements which can have parent-child relation. It looks like this:

    [
     {
       _id: 1,
       parent_id: null
       name: 'First element',
     },
     {
       _id: 2,
       parent_id: 1
       name: 'Second element',
     },
    ]

I want to calculate how many children each element have. I need to compare _id and parent_id of each document.

What I want to get:

    [
     {
      _id: 1,
       parent_id: null
       name: 'First element',
       childrens: 1,
     }
    {
      _id: 2,
       parent_id: 1
       name: 'Second element',
       childrens: 0,
     }
    ]

How can I achieve that? I tried to look in documentation for aggregation and other stuff but still I don't know how to do that.

Answer 1

You can use $graphLookup to perform a recursive lookup.

db.collection.aggregate([
  {
    "$graphLookup": {
      "from": "collection",
      "startWith": "$_id",
      "connectFromField": "_id",
      "connectToField": "parent_id",
      "as": "children"
    }
  },
  {
    "$addFields": {
      "children": {
        $size: "$children"
      }
    }
  }
])

Here is the Mongo Playground for your reference.