MongoDb query to return object of object where the object is aggregation of few fields

user_8119044 0
Viewed 27

I want to build a MongoDB query that $match on the first stage of the pipeline and then returns an object of object, where the first object is $project of a few common fields and next object is sum aggregation of the fields which are not common, the fields will be mentioned in the pipeline. For example, given 2 documents after the match pipeline -

{
  "bus": "EXAMP",
  "city": "Kanpur",
  "AccountName": "Examp Transport Service",
  "agencyName": "BBS",
  "depotName": "RAYAPUR HUBLI",
  "CashCollection": 8,
  "Collection": 30,
  "IssueTicket": 5,
  "PassengerCount": 4,
  "TicketCount": 4
}
{
  "bus": "EXAMP",
  "city": "Kanpur",
  "AccountName": "Examp Transport Service",
  "agencyName": "BBS",
  "depotName": "RAYAPUR HUBLI",
  "CashCollection": 10,
  "Collection": 20,
  "IssueTicket": 7,
  "PassengerCount": 5,
  "TicketCount": 4
}

So I would need projection of fields [bus, city, AccountName, agencyName, depotName] in the first object, and in the next I would need the aggregation of fields [CashCollection, Collection, IssueTicket, PassengerCount, TicketCount]. So my object of object should look like below

{
  result: [
    {
     "bus": "EXAMP",
     "city": "Kanpur",
     "AccountName": "Examp Transport Service",
     "agencyName": "BBS",
     "depotName": "RAYAPUR HUBLI",
    }
  ],
  aggregates: {
   "CashCollection": 18,
  "Collection": 50,
  "IssueTicket": 12,
  "PassengerCount": 9,
  "TicketCount": 8
 }
}
mongodb pipeline
1 Answers

You can go with the following aggregation

  • $match to filter the matching output. You may use $or if you need more than one matching output
  • $facet to devide incoming data
  • $group to group by the matching filds

Here is the code,

db.collection.aggregate([
  { "$match": { bus: "EXAMP" }  },
  {
    "$facet": {
      "result": [
        {
          "$group": {
            "_id": "$bus",
            "bus": { "$first": "$bus" },
            "city": { "$first": "$city" },
            "AccountName": { "$first": "$AccountName" },
            "agencyName": { "$first": "$agencyName" },
            "depotName": { "$first": "$depotName" }
          }
        }
      ],
      aggregate: [
        {
          "$group": {
            "_id": null,
            "totalCollection": { $sum: "$Collection" },
            "IssueTicket": { $sum: "$IssueTicket" },
            "PassengerCount": { $sum: "$PassengerCount" },
            "TicketCount": { $sum: "$TicketCount" }
          }
        }
      ]
    }
  },
  {
    $set: {
      aggregate: {
        "$ifNull": [
          { "$arrayElemAt": [ "$aggregate", 0 ] }, null ]
      }
    }
  }
])

Working MongoPlayground

user_79757710
Related