MongoDb How to aggregate by month and year

Question

I am new to mongodb and trying to learn Mongodb queries

    {
    "_id" : ObjectId("59815d4704ca1760a45957ca"),
    "userEmail" : "lk@gmail.com",
    "expenseAmount" : 200,
    "expenseRemark" : "aa",
    "expenseCategory" : "billing",
    "entryTime" : ISODate("2017-08-02T05:03:57Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("59815d5404ca1760a45957cb"),
    "userEmail" : "lk@gmail.com",
    "expenseAmount" : 300,
    "expenseRemark" : "ff",
    "expenseCategory" : "transport",
    "entryTime" : ISODate("2017-08-02T05:04:11Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("5980191d04ca1760a45957cd"),
    "userEmail" : "lk@gmail.com",
    "expenseAmount" : 100,
    "expenseRemark" : "rr",
    "expenseCategory" : "billing",
    "entryTime" : ISODate("2017-08-01T06:00:46Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("5980192604ca1760a45957ce"),
    "userEmail" : "lk@gmail.com",
    "expenseAmount" : 200,
    "expenseRemark" : "qq",
    "expenseCategory" : "transport",
    "entryTime" : ISODate("2017-08-01T06:01:03Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("5980192e04ca1760a45957cf"),
    "userEmail" : "lk@gmail.com",
    "expenseAmount" : 470,
    "expenseRemark" : "ff",
    "expenseCategory" : "transport",
    "entryTime" : ISODate("2017-08-01T06:01:11Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("59816ac004ca1760a45957d0"),
    "userEmail" : "lk@gmail.com",
    "expenseAmount" : 500,
    "expenseRemark" : "raj",
    "expenseCategory" : "transport",
    "entryTime" : ISODate("2017-08-02T06:01:26Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("59816acb04ca1760a45957d1"),
    "userEmail" : "lk@gmail.com",
    "expenseAmount" : 100,
    "expenseRemark" : "pet",
    "expenseCategory" : "pets",
    "entryTime" : ISODate("2017-08-02T06:01:37Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("597d7a9c04ca1760a45957d2"),
    "userEmail" : "lk@gmail.com",
    "expenseAmount" : 500,
    "expenseRemark" : "gt",
    "expenseCategory" : "sports",
    "entryTime" : ISODate("2017-07-30T06:20:04Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("597d7aaa04ca1760a45957d3"),
    "userEmail" : "lk@gmail.com",
    "expenseAmount" : 560,
    "expenseRemark" : "mov",
    "expenseCategory" : "entertainment",
    "entryTime" : ISODate("2017-07-30T06:20:14Z"),
    "__v" : 0
}

I want to get expenseAmount grouped by Year + Month. I tried

`db.expenses.aggregate( 
       {$project : { 
              month : {$month : "$entryTime"}, 
              year : {$year :  "$entryTime"}
          }}, 
        {$group : { 
                _id : {month : "$month" ,year : "$year" },  
              total : {$sum : "$expenseAmount"} 
        }})`

Which gives

{ "_id" : { "month" : 7, "year" : 2017 }, "total" : 0 }

{ "_id" : { "month" : 8, "year" : 2017 }, "total" : 0 }

Please guide me how can I get aggregated result. I could not figure out the way to do that.

Thanks

Because `$project` only returns the fields you tell it to. Instead you "should" write the whole thing in `$group`. Much more efficient that way. — Neil Lunn, Aug 02 '17 at 08:13

score 14 · Accepted Answer · edited Aug 11 '23 at 19:03

14

You have lost expenseAmount field during projection stage. Simply add it:

{$project: { 
    month: { $month: "$entryTime" }, 
    year: { $year: "$entryTime" },
    expenseAmount: 1
}},

Note that if field does not exist in document, then $sum returns 0.

Also note that there is another aggregation operator which performs as you expected - $addFields. It adds new fields to document and preserves all existing fields from the input document. But in this case you need only expenseAmount field

edited Aug 11 '23 at 19:03

Jan Švábík

314
2
13

answered Aug 02 '17 at 08:12

Sergey Berezovskiy

232,247
41
429
459

how can i use $addFields, please guide. – raju Aug 02 '17 at 08:20
@raju in this case you don't need it, because you need only expenseAmount field from input document. But if you would need other fields from input document as well, then instead including them all manually via $project operator, you can use $addFields (link in answer is clickable) to specify only fields which you want to *add* to existing fields of document – Sergey Berezovskiy Aug 02 '17 at 08:33

score 2 · Answer 2 · answered Nov 11 '19 at 09:40

  db.expenses.aggregate( 
    {$group : { 
            _id : { 
          month : {$month : "$entryTime"}, 
          year : {$year :  "$entryTime"}
      }},  
          total : {$sum : "$expenseAmount"} 
    }})

No need to add "project", only group will do everything.

score -1 · Answer 3 · answered Sep 07 '20 at 14:49

There wont be any need for $project in this case.Just use the below and you would get the expected result.

 db.expenses.aggregate( 
   {$group : { 
           _id : { 
         month : {$month : "$entryTime"}, 
         year : {$year :  "$entryTime"}
     }},  
         total : {$sum : "$expenseAmount"} 
   }})

MongoDb How to aggregate by month and year

3 Answers3

Linked