通过汇总在MongoDB中日期 [英] Aggregating By Date in Mongodb
问题描述
我正在编写一项功能，其中我需要按日期分组。这是我目前的做法：
//假设这是我的示例文档在集合pre>
{
_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
}
}
给定成员ID，开始日期和结束日期;我需要搜索匹配这些过滤器的集合，并按日期分组结果。换句话说，我需要实现的结果是一个列表（例如，12/10/2015 - 计数是5，13/10/2015 - 计数是2）。 StartDate和EndDate是DateTime的类型。
C＃是我使用的编程语言，目前，我写的方式是：
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;
然后将结果反序列化为自定义类...
列表< CustomAggregateResult> grouping = group.Select（g => BsonSerializer.Deserialize< CustomAggregateResult>（g））。OrderBy（g => g.Date）.ToList（）;
这个代码失败，这很明显，就是分组部分。在我的情况下，理想的是，按日期而不是日期时间分组。我已阅读论坛文档和一些类似的主题，但不幸的是，我不能得到它的工作。我的一个尝试是按照文档中的建议。原始mongo查询将是：
db.mycollection.aggregate（
[
{
$ group：{
_id：{month：{$ month：$ createddate}，day：{$ dayOfMonth：$ createddate}，year：{$ year：$ createddate}}
count：{$ sum：1}
}
}
]
）
我省略了$匹配只是为了让这个位工作。我得到的异常是无法从BSON类型对象转换为日期。
总之，我的当前代码工作，但group基于DateTime日期），它结束了一个特定日子的单独计数。我很好奇是否可以实现。未知是我的mongo部分，因为我还没有想出如何做到这一点（即使在一个原始的mongo查询）。
只是一些额外的信息要澄清，我有以下数据注释的datetime对象在C＃（不知道这是否影响）：
[BsonDateTimeOptions（Representation = BsonType.Document）]
public DateTime CreatedDate {get;组; }
我想到的一个解决方案是，是否可以即时投射createddate将其格式化为Ymd，并根据投影字段进行分组。
我试图添加尽可能多的细节和我已经完成的工作，只是为了使它更清楚。我希望这没有造成任何混乱。我会感谢任何帮助/建议，将帮助我产生我想要的结果。谢谢！
解决方案根据@chridam的评论，我能够修复它。再次感谢！
我正在编写以下解决方案，以防万一，如果有人遇到我遇到的同样的问题。
我改变了我的查询，使我变成了：
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 $ sum，1）}}）
.ToListAsync（）。Result;
这给了我一个序列化的对象。然后我反序列化成我的自定义类：
var grouped = group.Select（g => BsonSerializer.Deserialize& RootObject>（g））;
这里是自定义类的定义，这将是一个完美的：
public class Id
{
public int month {get;组; }
public int day {get;组; }
public int year {get;组; }
}
public class RootObject
{
public Id _id {get;组; }
public int count {get;组; }
}
我希望这将是有用的。谢谢！ :)
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!
解决方案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! :)
这篇关于通过汇总在MongoDB中日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
