Mongo nested $group calculate for year and month

3
votes

This is an example order document I have

{startDate: “2010:10:10”, numberOfHours: 10},
{startDate: “2010:11:10”, numberOfHours: 5},
{startDate: “2011:12:10”, numberOfHours: 1},
{startDate: “2012:10:10”, numberOfHours: 10}

First I want to calculate number of orders(totalOrders) and calculate sum of numberOfHours(totalHours) for each year of startDate. Then for each month of the year I need to calculate number of orders(orders) and calculate sum of numberOfHours(hours). Output should look like the following

    [
    // Current year first
   {
     year: 2019,
     totalOrders: 120,
     totalHours: 1234,
     months: [
       { 
          month: 0, // 0 based month index so jan = 0
          orders: 12, 
          hours: 120 
      },
{ 
          month: 1, 
          orders: 5, 
          hours: 100 
      }
      ////////////
      ]
    },
// 2018 etc
]

I looked at nested @group examples and could not find a match. I know how to group for year and month as follows

const result = await this._collection.aggregate([
            {
                $project: {
                    startYear: { $substr: ["$startDate", 0, 4] },
                    startMonth: { $substr: ["$startDate", 5, 2] }
                }
            },
            {
                $group: {
                    _id: { year: "$startYear", month: "$startMonth" },
                    orders: { $sum: 1 },
                    hours: { $sum: "$numberOfHours" }
                },
            },
        ]).toArray();

Any idea how to proceed to the output that I have mentioned? Any help will be highly appreciated.

2
mongodbmongodb-queryaggregation-framework

2 Answers

3
votes

You basically add another $group and just $push the months into an array for each year:

const result = await this._collection.aggregate([
    {
        $project: {
            startYear: { $substr: ["$startDate", 0, 4] },
            startMonth: { $substr: ["$startDate", 5, 2] }
        }
    },
    {
        $group: {
            _id: { year: "$startYear", month: "$startMonth" },
            orders: { $sum: 1 },
            hours: { $sum: "$numberOfHours" }
        },
     },
     {
         $group: {
             _id: { year: "$_id.year" },
             totalOrders: { $sum: "$orders" },
             totalHours: { $sum: "$hours" },
             months: {
               $push: {
                 month: "$_id.month",
                 orders: "$orders",
                 hours: "$hours"
               }
             }
         }
     }
]).toArray();
1
votes

You can better use $dateFromString operator and can $group with format whatever you want.

db.collection.aggregate([
  { "$group": {
    "_id": {
      "month": { "$month": { "$dateFromString": { "dateString": "$startDate", "format": "%Y:%m:%d" }}},
      "year": { "$year": { "$dateFromString": { "dateString": "$startDate", "format": "%Y:%m:%d" }}}
    },
    "hours": { "$sum": "$numberOfHours" },
    "count": { "$sum": 1 }
  }},
  { "$group": {
    "_id": "$_id.year",
    "totalHours": { "$sum": "$hours" },
    "totalOrders": { "$sum": "$count" },
    "months": {
      "$push": {
        "month": "$_id.month",
        "order": "$count",
        "hours": "$hours"
      }
    }
  }}
])