MongoDB C# driver aggregation between dates returns null fields

Question

I am using the c# driver for mongodb and want to use an aggregation query for a web API I am creating. For my aggregation query I am concerned with a profile class that has usernames, dates and steps. I want to create a query that selects usernames and gets their total steps for a given week, in descending order by total steps. I only want to display their Username and their total steps.

When I attempt the aggregation query I am having an issue with some of my fields coming up as null. So, I believe my query is not structured correctly.

I have a “Profile” class that I am using for my data currently.

[BsonIgnoreExtraElements]
[DataContract]
public class Profile 
{   
    [DataMember]
    public string Username { get; set; }

    [DataMember]
    public DateTime Date { get; set; }

    [DataMember]
    public uint? Steps { get; set; }
}

I have created some test data with for example profiles using the following

//Test data
for (uint index = 1; index < 20; index++)
{      
    Profile aprofile = new Profile
    {   
        Username = string.Format("testuser{0}", index),
        Date = RandomDay(),
        Steps = (index + index + index)*2

    };
    AddProfile(aprofile);
}

If I run the code a few times and query a particular user, I get data that is like this:

[{"Username":"testuser1","Date":"2014-07-03T00:00:00Z","Steps":6},
{"Username":"testuser1","Date":"2014-07-07T05:00:00Z","Steps":6},
{"Username":"testuser1","Date":"2014-07-17T05:00:00Z","Steps":6},
{"Username":"testuser1","Date":"2014-07-18T05:00:00Z","Steps":6}]

Then, I have a couple static methods to find the earliest date and the latest date for my aggregation query.

//gets a datetime for the earlist date and time possible for the current week
public static DateTime GetStartOfCurrentWeek()
{
    int DaysToSubtract = (int)DateTime.Now.DayOfWeek;
    DateTime dt = DateTime.Now.Subtract(TimeSpan.FromDays(DaysToSubtract));
    return new DateTime(dt.Year, dt.Month, dt.Day, 0, 0, 0, 0);
}

//gets a datetime for the latest date and time possible for the current week
public static DateTime GetEndOfCurrentWeek()
{
    DateTime dt = GetStartOfCurrentWeek().AddDays(6);
    return new DateTime(dt.Year, dt.Month, dt.Day, 23, 59, 59, 999);
}

My attempt at an aggregation query is below.

//Here is my aggregation query to get all profiles within a given week, 
public IEnumerable<Profile> GetAllProfiles()
{
    DateTime StartofWeek = GetStartOfCurrentWeek();
    DateTime EndofWeek = GetEndOfCurrentWeek();


    var match = new BsonDocument 
                {{ "$match", new BsonDocument 
                     {{ "Date", new BsonDocument {
                            {"$gte", StartofWeek},
                            {"$lt", EndofWeek}
                      }}}
                }};

    var group = new BsonDocument
                {{"$group",
                    new BsonDocument
                    {{ "_id", new BsonDocument
                         {{"id", "$Username"},

                    }},
                         {"Steps",  new BsonDocument
                              {{"$sum", "$Steps"}}
                    }}
                }};


    var sort =  new BsonDocument
                {{"$sort", new BsonDocument
                    {{"Steps", -1}}

                }};    



    var pipeline = new[] {match, group, sort};      

    var args = new AggregateArgs { Pipeline = pipeline, OutputMode = AggregateOutputMode.Inline };

    // run the aggregation query and get a list of BsonDocuments
    IEnumerable<BsonDocument> documents = _profiles.Aggregate(args);
}

However, the results I am getting are showing the usernames as Null and the dates as null.

[{"Username":null,"Date":"0001-01-01T00:00:00","Steps":96},
{"Username":null,"Date":"0001-01-01T00:00:00","Steps":66},
{"Username":null,"Date":"0001-01-01T00:00:00","Steps":24}]

What do I need to do to get my array of documents so that the usernames display along with the total steps I have (which do seem to be working). I don't want the Dates in my query results. Just the Username and their total steps for that week.

Answer 1

Your group stage has one flaw, and that is you didn't included the field you want out of your query.

By just changing your group stage, you should be getting the desired field.

var group = new BsonDocument
    {{"$group",
        new BsonDocument
        {
            { "_id", new BsonDocument
                {{"id", "$Username"}}
            },
            { "Steps",  new BsonDocument
                {{"$sum", "$Steps"}}
            },
            { "Username", new BsonDocument
                {{"$first", "$Username"}} // since the rest of the selected object is groupped, we should give a strategy to pick a $Username, and it is safe to use $first aggregate function here
            }
        }
    }};

And if you are not comfortable with the date in your output result just add a project stage, and reshape your output document.

Answer 2

I don't really understand why do you need to query your mongo with BSon. You do have your model class. Aggregation you are trying to perform will look like:

var result = collection.Aggregate()
    .Match(r => r.Date >= StartofWeek && r.Date < EndofWeek)
    .Group(r=>r.Username, r=> new {UserName = r.Key, Dates  = r.Select(d=>d.Date),  Steps = r.Sum(x=>x.Steps)})
    .ToList();

Note, that you could get not one date, but array of it, therefore i aggregate it as Dates.

Reason, why you don't see Date and username is that you don't query it, in your group statement i see just sum of steps. if you really want to continue to query this way, you should add it to your group stage, something like:

var group = new BsonDocument
             {
                    { "_id", new BsonDocument{{"id", "$Username"},
             }},
             {"Steps",  new BsonDocument{{"$sum", "$Steps"}}},
             { "Dates", new BsonDocument{{"$addToSet", "$Date"}} },
             { "Username", new BsonDocument{{"$first", "$Username"}} },
        };

This way i can see the Username and dates in resulting BsonDocument.

But as i said already, this way to query Mongo is not the best one to do in c#