CODEWITHSUNDEEP
c#mongodbaggregation-framework
Yavar Hasanov
Yavar Hasanov

Reputation: 533

Aggregating By Date in Mongodb

I am writing a piece of functionality in which I am required to group by Date. Here is how I do currently: 

//Assuming this is my sample document in the collection
{
   "_id" : ObjectId("56053d816518fd1b48e062f7"), 
   "memberid" : "7992bc31-c3c5-49e5-bc40-0a5ba41af0bd",  
   "sourceid" : NumberInt(3888), 
   "ispremium" : false, 
   "createddate" : {
       "DateTime" : ISODate("2015-09-25T12:26:41.157+0000"), 
       "Ticks" : NumberLong(635787808011571008)
   }, 
   "details": {
      //a large sub-document
    }
}

Given the member id, start date and end date; I am required to search the collection matching these filters and group the results by Date. In other words, the result I need to achieve is a list like (e.g., 12/10/2015 - count is 5, 13/10/2015 - count is 2). StartDate and EndDate are the types of DateTime.

C# is the programming language I use and currently, the way I have written is:

    var builder = Builders<MyCollection>.Filter;
    var filter = builder.Eq(d => d.MemberId, memberId) & builder.Gte(d => d.CreatedDate, startDate) & builder.Lt(d => d.CreatedDate, endDate.AddDays(1));

    var group = collection.Aggregate()
                          .Match(filter)
                          .Group(new BsonDocument { { "_id", "$createddate" }, { "count", new BsonDocument("$sum", 1) } })
                          .ToListAsync().Result;

I then deserialize the result to custom class...

List<CustomAggregateResult> grouped = group.Select(g => BsonSerializer.Deserialize<CustomAggregateResult>(g)).OrderBy(g => g.Date).ToList();

Where this code fails, which is obvious, is the grouping part. What would be ideal in my case is, to group by Date rather than DateTime. I have read the group documentation and some similar threads here but, unfortunately, I couldn't get it working. One of my attempts was to do as suggested in the documentation. Raw mongo query for that would be:

db.mycollection.aggregate(
[
  {
    $group : {
       _id : { month: { $month: "$createddate" }, day: { $dayOfMonth: "$createddate" }, year: { $year: "$createddate" } },
     count: { $sum: 1 }
    }
  }

] )

I had left out the $match just to get this bit working. The exception I got was "cannot convert from BSON type Object to Date".

In summary, my current code works but "group" based on the DateTime (instead of just Date) and it ends up separate counts for one particular day. I am curious whether or not it is achievable. Unknown is the mongo part to me as I haven't figured out how to do this.(even in a raw mongo query).

Just some additional information to clarify, I have the following data annotation for the datetime object in C# (not sure if this affects):

[BsonDateTimeOptions(Representation = BsonType.Document)]
public DateTime CreatedDate {get; set; }

One solution in my mind is whether it's possible to project the "createddate" field on the fly and format it as "Y-m-d" and do the grouping based on the projected field.

I tried to add as many details as I can and the work I have done so far, just to make it clearer. I hope this didn't cause any confusion. I'd appreciate any help/suggestion that would help me to produce the result I want. Thanks!

Upvotes: 3

Views: 3648

Answers (1)

Yavar Hasanov
Yavar Hasanov

Reputation: 533

I was able to fix it, according to @chridam 's comment. Thanks again!

I am writing the solution below, just in case, if someone ever runs into the same problem I did.

I changed my query so that I became: 

 var group = collection.Aggregate()
                       .Match(filter)
                       .Group(new BsonDocument { { "_id", new BsonDocument { { "month", new BsonDocument("$month", "$createddate.DateTime") }, { "day", new BsonDocument("$dayOfMonth", "$createddate.DateTime") }, { "year", new BsonDocument("$year", "$createddate.DateTime") } } }, { "count", new BsonDocument("$sum", 1) } })
                       .ToListAsync().Result;

This gave me a serialized object. Then I deserialized it into the custom class I had:

var grouped = group.Select(g => BsonSerializer.Deserialize<RootObject>(g));

Here is the custom class definition which will be a bit polished-up:

public class Id
{
    public int month { get; set; }
    public int day { get; set; }
    public int year { get; set; }
}

public class RootObject
{
    public Id _id { get; set; }
    public int count { get; set; }
}

I hope this will be useful. Thanks! :)

Upvotes: 4

Related Questions