How to aggregate the count/sum of 3 collections using MongoDb?

user_11209037 0
Viewed 95

I am trying to aggregate multiple collections and get the daily totals based on the createdAt field:

db={
  "agents": [
    {
      "_id": ObjectId("5a934e000ac2030405000370"),
      "name": "Book Now"
    }
  ],
  "tolls": [
    {
      "_id": ObjectId("5a934e000102030405000000"),
      "amountCollected": 20,
      "plaza": "Shimabala",
      "directionLane": "South/3",
      "cashier": "Chishala",
      "agent": {
        "_id": ObjectId("5a934e000ac2030405000370"),
        "name": "Book Now"
      },
      "createdAt": ISODate("2021-06-02T13:23:06.232Z")
    },
    {
      "_id": ObjectId("a9934ec001a203040500dc00"),
      "amountCollected": 150,
      "plaza": "Shimabala",
      "directionLane": "South/3",
      "cashier": "Chishala",
      "agent": {
        "_id": ObjectId("5a934e000ac2030405000370"),
        "name": "Book Now"
      },
      "createdAt": ISODate("2021-06-04T13:23:06.232Z")
    }
  ],
  "fuel": [
    {
      "_id": ObjectId("60b79e4e4afd77a1c27c730c"),
      "litres": 5.2,
      "price": 90,
      "station": "Chilanga",
      "fuelAttendant": "Manda",
      "agent": {
        "_id": ObjectId("5a934e000ac2030405000370"),
        "name": "Book Now"
      },
      "createdAt": ISODate("2021-06-04T16:06:16.232Z")
    }
  ]
}

Expected results:

[
  {
    "_id": "2021-06-02",
    "fuel": 0,
    "tolls": 1
  },
  {
    "_id": "2021-06-04",
    "fuel": 1,
    "tolls": 1
  }
]

Because the two collections connected to the agent's collection, I have this query but not sure how to go from there:

db.agents.aggregate([
  {
    "$lookup": {
      "localField": "_id",
      "from": "tolls",
      "foreignField": "agent._id",
      "as": "tolls"
    }
  },
  {
    "$unwind": {
      "path": "$tolls",
      "preserveNullAndEmptyArrays": true
    }
  },
  {
    "$lookup": {
      "localField": "_id",
      "from": "fuel",
      "foreignField": "agent._id",
      "as": "fuel"
    }
  },
  {
    "$unwind": {
      "path": "$fuel",
      "preserveNullAndEmptyArrays": true
    }
  } 
])

Link to the Playground

aggregation-framework mongodb lookup sum
1 Answers
  • query from tolls collection
  • $project to show required fields and add new field type for "tolls"
  • $unionWith with fuel collection and $project to show required fields add new field type for "fuel"
  • now we have merged both collections document in root and added type for each document
  • $group by date and type, count total elements
  • $group by only date and construct the array of both type with its count in key-value format
  • $addFields to convert that analytic field to object using $arrayToObject
db.tolls.aggregate([
  { $project: { createdAt: 1, type: "tolls" } },
  {
    $unionWith: {
      coll: "fuel",
      pipeline: [
        { $project: { createdAt: 1, type: "fuel" } }
      ]
    }
  },
  {
    $group: {
      _id: {
        date: {
          $dateToString: {
            date: "$createdAt",
            format: "%Y-%m-%d"
          }
        },
        type: "$type"
      },
      count: { $sum: 1 }
    }
  },
  {
    $group: {
      _id: "$_id.date",
      analytic: {
        $push: { k: "$_id.type", v: "$count" }
      }
    }
  },
  { $addFields: { analytic: { $arrayToObject: "$analytic" } } }
])

Playground

Result wound be:

[
  {
    "_id": "2021-06-04",
    "analytic": {
      "fuel": 1,
      "tolls": 1
    }
  },
  {
    "_id": "2021-06-02",
    "analytic": {
      "tolls": 1
    }
  }
]

This will not add 0 count field, you have to manage it on frontend/client-side

user_89871280
Related