CODEWITHSUNDEEP
mongodbaggregation-frameworkaggregate
Ashish Bairwa
Ashish Bairwa

Reputation: 775

Referring to a different collection from an existing one and counting from the same collection

I have to collections A and B in which the documents of A contains the object ids which are present in the B in the fields centre and gcentre. What I'm currently is outputting the result which contains the name of the parent centre by referring from collection A's object id to B and then referring the gcentre's id to find the child and centre and count the documents assigned via javascript post-processing. Been new to the aggregation pipeline, I don't know how to refer via object id and that sort of counting of records. Is it possible with the aggregation pipeline? I have tried with $lookup but it doesn't seem to give the output as expected.

Documents in collection A:

{
  "_id": {
        "$oid": "5bbafa98d8c77251bea30f8c"
    },
    "parentCentre": {
        "$oid": "1cbafa99d8c77251bea30f11"
    },
    "childCentre": {
        "$oid": "5cbafa99d8c77251bea30f8d"
    },
},
{
  "_id": {
        "$oid": "5bbafa98d8c77251bea30f8c"
    },
    "parentCentre": {
        "$oid": "1cbafa99d8c77251bea30f11"
    },
    "childCentre": {
        "$oid": "5cbafa99d8c77251bea30f8d"
    },
},
{
  "_id": {
        "$oid": "5bbafa98d8c77251bea30f8c"
    },
    "parentCentre": {
        "$oid": "1cbafa99d8c77251bea30f11"
    },
    "childCentre": {
        "$oid": "5cbafa99d8c77251bea30f8d"
    },
},
{
  "_id": {
        "$oid": "5bbafa98d8c77251bea30f8c"
    },
    "parentCentre": {
        "$oid": "1cbafa99d8c77251bea30f21"
    },
    "childCentre": {
        "$oid": "5cbafa99d8c77251bea30f6d"
    },
}

Documents in collection B:

   {
        "_id": {
                "$oid": "1cbafa99d8c77251bea30f11"
            },
            "Type": "Parent",
            "Name": "Kris Labs"
        },
    {
        "_id": {
                "$oid": "1cbafa99d8c77251bea30f21"
            },
            "Type": "Parent",
            "Name": "DEX Labs"
        },
    {
        "_id": {
                "$oid": "5cbafa99d8c77251bea30f8d"
            },
            "Type": "Child",
            "Name": "Mili Labs"
        },
        {
        "_id": {
                "$oid": "5cbafa99d8c77251bea30f6d"
            },
            "Type": "Child",
            "Name": "Max Labs"
        }

Result:

{
  "parentCentreName":"Kris Labs",
  "Records":{
            {
            childCentreName: "Max Labs",
            recordCount: 3
              }
             }
},
{
  "parentCentreName":"DEX Labs",
  "Records":{
            {
            childCentreName: "Mili Labs",
            recordCount: 1
              }
             }
}

Upvotes: 1

Views: 46

Answers (1)

Tiya Jose
Tiya Jose

Reputation: 1419

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

Upvotes: 1

Related Questions