MongoDB aggregation of daily records hourly basis

Question

I have a collection or orders with the following format:

"createTime" : ISODate("2021-04-16T08:01:39.000Z"),
"statusDetails" : [ 
    {
        "createTime" : ISODate("2021-04-16T08:01:39.000Z"),
        "createUser" : "FOP-SYSTEM",
        "stateOccurTimeStr" : "2021-04-16 15:01:39",
        "status" : 27,
        "statusDesc" : "Shipped"
    }
]

where createTime is showing that when the order has been created
statusDetails.status = 27 showing that order has been shipped
statusDetails.createTime showing that when the order has been shipped

The result which I need is something like this:

Order Date	0-4 Hours	4-8 Hours	8-12 Hours	12-24 Hours	> 24 Hours	Total Orders
01-Jan-21	15	10	4	1	1	31

This shows that on "1-Jan-2021" after order creation,

15 orders shipped between 0-4 hours, 
10 orders shipped between 4-8 hours, 
4 orders shipped between  8-12 hours 

and so on.

What I have done so far is:

db.orders.aggregate([
   { $unwind : "$statusDetails"},
   {$match: {"statusDetails.status": { "$exists": true, "$eq": 24 }}},
   {$project : { createTime: 1,statusDetails:1, 
      dateDiff: {"$divide" : [{ $subtract: ["$statusDetails.createTime","$createTime" ] },3600000]}}},
   {$sort:{"createTime":-1}}
   ])

But this is showing time difference of each individual record, but I need group by

Edit I have updated my query and now it is showing records using $group but still I need to add an another pipeline to group the current data.

db.orders.aggregate([
   
   { $unwind : "$statusDetails"},
   {$match: {"statusDetails.status": { "$exists": true, "$eq": 27 }}},
   {$project : { createTime: 1,statusDetails:1, dateDiff:{"$floor": {"$divide" : [{ $subtract: ["$statusDetails.createTime","$createTime" ] },3600000]}}}},
    { 
        $group: 
        { _id: {  year : { $year : "$createTime" }, month : { $month : "$createTime" }, day : { $dayOfMonth : "$createTime" }},
        shippedTime: { $push: "$dateDiff" },
        count: { $sum: 1 }
        } 
    },
   
   
   
   {$sort:{"createTime":-1}}
])

Answer 1

• $unwind to deconstruct statusDetails array
• $match your conditions
• $addFields to add slot field on the base of your date calculation hour slot from equation and get total
• $group by date as per your format using $dateToString and slot
• $sort by slot in ascending order
• $group by only date and construct array of slots and get total orders

db.collection.aggregate([
  { $unwind: "$statusDetails" },
  { $match: { "statusDetails.status": 27 } },
  {
    $addFields: {
      slot: {
        $multiply: [
          {
            $floor: {
              $divide: [
                {
                  $abs: {
                    "$divide": [
                      { $subtract: ["$statusDetails.createTime", "$createTime"] },
                      3600000
                    ]
                  }
                },
                4
              ]
            }
          },
          4
        ]
      }
    }
  },
  {
    $group: {
      _id: {
        date: {
          $dateToString: {
            date: "$createTime",
            format: "%d-%m-%Y"
          }
        },
        slot: "$slot"
      },
      total: {
        $sum: 1
      }
    }
  },
  { $sort: { "_id.slot": 1 } },
  {
    $group: {
      _id: "$_id.date",
      hours: {
        $push: {
          slot: "$_id.slot",
          total: "$total"
        }
      },
      totalOrders: { $sum: "$total" }
    }
  }
])

Playground

Result would be:

[
  {
    "_id": "16-04-2021",
    "hours": [
      { "slot": 0, "total": 1 }, // from 0 to 4
      { "slot": 4, "total": 1 }, // from 4 to 8
      { "slot": 8, "total": 2 } // from 8 to 12
    ],
    "totalOrders": 4
  }
]