MongoDB monthly grouping

Question

I tried to group the values by month and to get an average for each month.

here is the aggregation method I used

var aggregateArgs = new AggregateArgs(); 
        aggregateArgs.Pipeline =
            new[]
            {
                    new BsonDocument{{ "$match", new BsonDocument
                        {{ "_Timestamp", new BsonDocument {
                            {"$gte", _start}, // start date is 2016-08-04T23:15:00.000+01:00
                            {"$lt", _end}// end date is 2017-09-04T23:15:00.000+01:00
                        }}}
                    }},
                    new BsonDocument("$project",
                    new BsonDocument
                    {
                        { "_id" , 0 },
                        { "new_range", new BsonDocument {
                                                            {"year", new BsonDocument("$year", "$_Timestamp")},
                                                            {"month", new BsonDocument("$month", "$_Timestamp")},
                                                          }
                        }
                    }
                    ),
                    new BsonDocument("$group",
                    new BsonDocument
                    {
                        {"_id", "$new_range" },
                        {"MonthlyAverage", new BsonDocument("$avg", "$totalfundspent")},
                    }),
            };

But I get this

"Enumeration yielded no results"

What am I doing wrong? My MongoDB version is 3.4.4

Example of my documents

{
"_Timestamp" : ISODate("2017-08-04T23:15:00.000+01:00"),
"totalfundspent" : 1138.0,
}
{
"_Timestamp" : ISODate("2017-08-05T23:15:00.000+01:00"),
"totalfundspent" : 638.0,
}

Answer 1

I believe the issue was that the date wasn't being parsed as ISODate("2016-08-04T23:15:00.000+01:00"), casting it to a DateTime and passing that to the BsonDocument makes sure that does happen.

var _start = Convert.ToDateTime("2016-08-04T23:15:00.000+01:00");
var _end = Convert.ToDateTime("2017-09-04T23:15:00.000+01:00");

var match = new BsonDocument {
    {
        "_Timestamp",
        new BsonDocument {
            {
                "$gte", _start
            }, {
                "$lt", _end
            }
        }
    }

};

var project = new BsonDocument {
    {
        "_id",
        0
    }, {
        "new_range", new BsonDocument {
            {
                "year", new BsonDocument("$year", "$_Timestamp")
            },
            {
                "month",
                new BsonDocument("$month", "$_Timestamp")
            },
        }
    }
};

var group = new BsonDocument {
    {
        "_id",
        "$new_range"
    }, {
        "MonthlyAverage", new BsonDocument("$avg", "$totalfundspent")
    }
};

var result = this.coll.Aggregate().Match(match).Project(project).Group(group).ToList();

if you wish to debug the query you can call a .ToString() before the .ToList() like so

var jsonQuery = this.coll.Aggregate().Match(match).Project(project).Group(group).ToString();

This gives you a json document which you can in turn use to query Mongo yourself.