How to aggregate across a $substr in MongoDB-CodePudding

I have a collection, ledger, with the following document format:

  {
    _id: ###,
    month: 202112,
    name: 'XXXXXXXXXXXX',
    gross_revenue: 482.28
  }

The month actually contains both the year and month, YYYYMM. And there are multiple entries per 'month'. What I'm wanting to do is sum the gross_revenue values across the years. So take a $substr of month to get the year and then sum up gross_revenue.The result would ideally look like this:

2019: 99999.99,
2020: 88888.88,
.
.

I can aggregate for a given month, and I can get the substr, but can't figure out how to do combine them to aggregate by year.

db.ledger.aggregate([ { $match: {month: 202111} },{ $group: { _id: null, total: { $sum: "$gross_revenue" } } } ] )

db.ledger.aggregate([{$project: { year: {$substr:["$month", 0,4]}}}])

Any help is appreciated.

CodePudding user response：

Query

group by year (we can use any aggregation operator to group)
replace root to make the expected output

Test code here

aggregate(
[{"$group":
  {"_id":{"$substrCP":["$month", 0, 4]},
   "sum":{"$sum":"$gross_revenue"}}},
 {"$replaceRoot":
  {"newRoot":{"$arrayToObject":[[{"k":"$_id", "v":"$sum"}]]}}}])