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
}
}
])
CLICK HERE to find out more related problems solutions.