Aggregating Mongo collection by year and then by month-CodePudding

I have a Mongo collection that looks like this with a bunch of months, days, years:

[
  {
    "Date": ISODate("2021-08-05T04:59:54.000Z"),
    "Amount": 999,
    "Business": "Business 1",
    
  },
  {
    "Date": ISODate("2021-08-05T04:59:54.000Z"),
    "Amount": 5.99,
    "Business": "Business 2",
    
  },
  {
    "Date": ISODate("2021-07-17T21:41:56.000Z"),
    "Amount": 20000,
    "Business": "Business 2",
    
  },
  {
    "Date": ISODate("2021-06-17T21:41:56.000Z"),
    "Amount": 200,
    "Business": "Business 5",
    
  }
]

I have done an aggregation like this

db.collection.aggregate({
  $group: {
    _id: {
      year: {
        $year: "$Date"
      },
      month: {
        $month: "$Date"
      }
    },
    sum: {
      $sum: "$Amount"
    }
  }
})

...which partially gives me what I want which is a sum of amounts per year and month.

[
  {
    "_id": {
      "month": 6,
      "year": 2021
    },
    "sum": 200
  },
  {
    "_id": {
      "month": 7,
      "year": 2021
    },
    "sum": 20000
  },
  {
    "_id": {
      "month": 8,
      "year": 2021
    },
    "sum": 1004.99
  }
]

What I would like however is to have something like the below where the year is at the top and the months are aggregated in a sum so that it's easier to iterate in the frontend but I have not been able to get it no matter what I have tried:

[
  {
    "year": 2021,
    "sumAmount": 21204.99,
    "months": [
      {
        "month": 7,
        "amount": 20000
      },
      {
        "month": 6,
        "amount": 200
      },
      {
        "month": 8,
        "amount": 1004.99
      }
    ]
  },
  { "year" : 2020,
    ....
  }
]

I have been pretty close in using another $group and $push but I have not been able to get what in my mind is a second group by month. Any help will be appreciated!

CodePudding user response：

You just need one more $group to get your expected result. For another sorting, you can put an $sort after the $group stage. You will need to use $push to keep the ordering in the final array.

db.collection.aggregate([
  {
    $group: {
      _id: {
        year: {
          $year: "$Date"
        },
        month: {
          $month: "$Date"
        }
      },
      sum: {
        $sum: "$Amount"
      }
    }
  },
  {
    "$sort": {
      "_id.year": 1,
      "_id.month": 1
    }
  },
  {
    "$group": {
      "_id": "$_id.year",
      "sumAmount": {
        $sum: "$sum"
      },
      "months": {
        "$push": {
          "month": "$_id.month",
          "amount": "$sum"
        }
      }
    }
  }
])

Here is the Mongo playground for your reference.