Aggregate Hourly Weekly Monthly Yearly data in mongodb

Q1. I need to filter data by created date and driverId then need to sum up the total by Hourly, Weekly, Monthly, and Yearly. I already checked with other solutions but it doesn't help much.

Sample Data:

    [
      {
        id: "1",
        created : "2022-01-04T03:22:18.739Z",
        completed: "2022-01-06T03:53:28.463Z",
        driverId: "B-72653",
        total: 15,
      },
      {
        id: "2",
        created : "2022-01-01T03:22:18.739Z",
        completed: "2022-01-02T03:53:28.463Z",
        driverId: "B-72653",
        total: 33
      },
      {
        id: "3",
        created : "2021-08-26T01:22:18.739Z",
        completed: "2021-08-26T09:53:28.463Z",
        driverId: "B-72653",
        total: 43
      },  
      {
        id: "4",
        created : "2021-03-26T02:22:18.739Z",
        completed: "2021-03-26T07:53:28.463Z",
        driverId: "B-73123",
        total: 35
      },  
    ]

Response needed:

    {
        Hourly:[10,5,5,6,7,8,4,5,6,3,44,2,1,2,3,44,5,6,75,4,3,2,1], // 24 Hours (Each Hour Total)
        Weekly:[10,30,34,45,56,67,78], // 7 days (Each Day Total)
        Monthly:[10,30,34,45,56,67,78,55,44,33,22,12], // 12 Months (Each Month Total)
        Yearly: [10,30] // Year Total (Each Year Total)
    }

Q2. How can we filter nested array by-products > brand id and get the sum of product price by its id and filter by Hourly, Weekly, Monthly, Yearly?.

Solution 1:

You can use $group with _id being $hour / $week / $month / $year to aggregate the sum. $push them into an array to get your expected result. Use $facet to repeat the process for all 4 cases.

db.collection.aggregate([
  {
    "$facet": {
      "Hourly": [
        {
          $group: {
            _id: {
              $hour: "$created"
            },
            total: {
              $sum: "$total"
            }
          }
        },
        {
          $sort: {
            _id: 1
          }
        },
        {
          $group: {
            _id: null,
            result: {
              $push: {
                hour: "$_id",
                total: "$total"
              }
            }
          }
        }
      ],
      Weekly: [
        {
          $group: {
            _id: {
              "$week": "$created"
            },
            total: {
              $sum: "$total"
            }
          }
        },
        {
          $sort: {
            _id: 1
          }
        },
        {
          $group: {
            _id: null,
            result: {
              $push: {
                week: "$_id",
                total: "$total"
              }
            }
          }
        }
      ],
      Monthly: [
        {
          $group: {
            _id: {
              $month: "$created"
            },
            total: {
              $sum: "$total"
            }
          }
        },
        {
          $sort: {
            _id: 1
          }
        },
        {
          $group: {
            _id: null,
            result: {
              $push: {
                month: "$_id",
                total: "$total"
              }
            }
          }
        }
      ],
      Yearly: [
        {
          $group: {
            _id: {
              $year: "$created"
            },
            total: {
              $sum: "$total"
            }
          }
        },
        {
          $sort: {
            _id: 1
          }
        },
        {
          $group: {
            _id: null,
            result: {
              $push: {
                year: "$_id",
                total: "$total"
              }
            }
          }
        }
      ]
    }
  },
  {
    "$addFields": {
      "Hourly": {
        "$arrayElemAt": [
          "$Hourly",
          0
        ]
      },
      "Weekly": {
        "$arrayElemAt": [
          "$Weekly",
          0
        ]
      },
      "Monthly": {
        "$arrayElemAt": [
          "$Monthly",
          0
        ]
      },
      "Yearly": {
        "$arrayElemAt": [
          "$Yearly",
          0
        ]
      }
    }
  },
  {
    "$addFields": {
      "Hourly": "$Hourly.result",
      "Weekly": "$Weekly.result",
      "Monthly": "$Monthly.result",
      "Yearly": "$Yearly.result"
    }
  }
])

Here is the Mongo playground for your reference.