Mongo aggregate sum and group items by date

Question

Very thanks for your time. I am working on a collection where I want to sum item of the same date. Consider following example, here I have two documents in which user_id and played event are stored. I want to sum those documents who have the same date. In my case date, 2017-01-25 have two results and 2017-01-26 has only one. Please look into the expected result.

{
    "_id" : ObjectId("58891b5656a961427e7b23c6"),
    "user_id" : 122,
    "played_event" : [ 
        {
            "date" : ISODate("2017-01-25T21:43:48.146Z"),
            "totalPlayed" : 0,
        }, 
        {
            "date" : ISODate("2017-01-26T22:26:03.273Z"),
            "totalPlayed" : 838,
        }, 
    ]
}

{
    "_id" : ObjectId("58891b5656a961427e7b23f3"),
    "user_id" : 130,
    "played_event" : [ 
        {
            "date" : ISODate("2017-01-25T21:43:48.146Z"),
            "totalPlayed" : 0,
        }, 
        {
            "date" : ISODate("2017-01-30T22:26:03.273Z"),
            "totalPlayed" : 838,
        }, 
    ]
}

Expected Result

{
    "result" : [ 
        {
            "date" : "2017-01-25"
            "sum" : 2
        }, 
        {
            "date":"2017-01-26"
            "sum":1
        }, 
    ],
    "ok" : 1
}

I am trying it with following code

[{"$unwind":"$played_event"},{"$match":{"$and":[{"played_event.date":{"$lte":{"sec":1530766799,"usec":0},"$gte":{"sec":1530162000,"usec":0}}},{"game_id":1}]}},{"$match":{"user_id":{"$nin":[1,2]}}},{"$group":{"_id":"$user_id","total":{"$sum":"$played_event.totalPlayed"},"events":{"$push":"$played_event.date"}}},{"$project":{"_id":0,"user_id":"$_id","total":1,"events":1}}]

but it is not giving me expected results, I am summing up totalPlayed in my query, but this is not required at this time.

Answer 1

You need to first $unwind the "played_event" and then you need to $group by putting the desired format for "date" using $dateToString

db.collection.aggregate([
  { "$unwind": "$played_event" },
  { "$group": {
    "_id": {
      "$dateToString": {
        "format": "%Y-%m-%d",
        "date": "$played_event.date"
      }
    },
    "sum": { "$sum": 1 }
  }},
  { "$project": {
    "date": "$_id",
    "sum": 1,
    "_id": 0
  }}
])

Output

[
  {
    "date": "2017-01-30",
    "sum": 1
  },
  {
    "date": "2017-01-26",
    "sum": 1
  },
  {
    "date": "2017-01-25",
    "sum": 2
  }
]