So I needed to know occupied time for an staff from orders they've been assigned. A customer creates an order and one or more staffs are assigned to that order for services that customer has requested. To get the insights of orders and staff schedule time, I needed to write some aggregation.

The MongoDB schema for orders collection is like this -

{
  orderNum: Number,
  scheduledAt: Date,
  services: [{
    staffId: ObjectId,
    averageTimeMinute: Number // time taken to complete the service
    // ....
  }],
  // ...
}

scheduledAt tells when is the service scheduled to be serviced to the customer. And averageTimeMinute tells the average time takes for that service. The problem here is the scheduledAt is a Date while averageTimeMinute is minutes in Number. And I needed to come up with a solution that gives us a range of time when the staff is assigned to do the service. That can be calculated using plain javascript by -

const fromTime = scheduledAt;
const endTime = new Date(scheduledAt);
endTime.setMinutes(endTime.getMinutes() + services[i].averageTimeMinute)

But the better way of doing anything like this, is with the help of MongoDB aggregations. It is very powerful to manipulate data in a way we want. If you don't know what an aggregation is, you can think of it like a pipeline where you input data from one end and it does something with data and outputs from another end.

The thing is aggregations are hard to understand and write. After hours of trial and error, I finally came up with an aggregation which will give me the desired output I need. Here it is

db.getCollection("orders").aggregate([
  { $unwind: { path: "$services" } },
  {
    $group: {
      _id: "$service.staffId",
      services: {
        $push: {
          start: "$scheduledAt",
          end: {
            $toDate: {
              $add: [
                { $toLong: "$scheduledAt" },
                { $multiply: ["$services.averageMinuteTime", 60000.0] },
              ],
            },
          },
        },
      },
    },
  },
]);

So the trick here is, since scheduledAt is a Date and averageMinuteTime is a Number, we need both of these in same unit, Number. So the first step was to convert scheduledAt to a unix timestamp and in mongo we can do like this

{ $toLong: "$scheduledAt" }

Now we have both in same type, Number, we also need to make both numbers in same type. Now scheduledAt is in miliseconds, we also need to make our averageMinuteTime into miliseconds. We can do it by multiplying by 60000. And in MongoDB we can do like this -

 { $multiply: ["$services.averageMinuteTime", 60000.0] },

Now, both our scheduledAt and averageMinuteTime is in same type and unit. Let's add both and we will get our desired endTime for the service.

$add: [
   { $toLong: "$scheduledAt" },
   { $multiply: ["$services.averageMinuteTime", 60000.0] },
],

And the last step is to convert it back to date, which in very easy to do -

$toDate: {
  $add: [
     { $toLong: "$scheduledAt" },
     { $multiply: ["$services.averageMinuteTime", 60000.0] },
  ],
},

and here it is, we got out desired output from the pipeline. Now I can render beautiful charts to see when our staffs are scheduled to work.

I hope you found this interesting. Please let me know if you have any feedback.

Thanks