You can use the following aggregation query:

db.A.aggregate([
  {
    $group: {
      _id: {
        p: "$parentCentre",
        c: "$childCentre"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: "$_id.p",
      Records: {
        $push: {
          childCentreName: "$_id.c",
          recordCount: "$count"
        }
      }
    }
  },
  {
    $unwind: "$Records"
  },
  {
    "$lookup": {
      "from": "B",
      "localField": "_id",
      "foreignField": "_id",
      "as": "p"
    }
  },
  {
    "$lookup": {
      "from": "B",
      "localField": "Records.childCentreName",
      "foreignField": "_id",
      "as": "c"
    }
  },
  {
    $unwind: "$c"
  },
  {
    $unwind: "$p"
  },
  {
    $project: {
      "parentCentreName": "$p.Name",
      "Records.childCentreName": "$c.Name",
      "Records.recordCount": 1,
      _id: 0
    }
  },
  {
    $group: {
      _id: "$parentCentreName",
      "Records": {
        $push: "$Records"
      }
    }
  },
  {
    $project: {
      "parentCentreName": "$_id",
      "Records": 1,
      _id: 0
    }
  }
])

MongoDB Playground

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top