按日期的MongoDB聚合(MongoDB Aggregation by Date)
这是我的收藏品的样子......
{ "_id" : ObjectId("53c66071409aeb38133c6ece"), "starttime" : ISODate("2014-07-15T11:22:25Z"), "product" : "1", "customer" : "Microsoft" } { "_id" : ObjectId("53c66071409aeb38133c6ecf"), "starttime" : ISODate("2014-07-15T11:22:25.882Z"), "customer" : "Microsoft", } { "_id" : ObjectId("53c66072409aeb38133c6ed0"), "starttime" : ISODate("2014-07-16T11:22:26.15Z"), "customer" : "Microsoft", } { "_id" : ObjectId("53c66072409aeb38133c6ed0"), "starttime" : ISODate("2014-07-16T11:22:27.15Z"), "customer" : "Apple", }我想按日期对数据进行分组并格式化输出，以便它显示如下...（我已经对我缺少的内容发表评论）
[ { "_id": { "date": { "year": 2014, "month": 7, "day": 16 } }, "productions": [ { "name": "Microsoft", "number": 1 //THIS IS MISSING AT THE MOMENT }, { "name": "Apple", "number": 1 //THIS IS MISSING AT THE MOMENT } ], "total": 2 }, { "_id": { "date": { "year": 2014, "month": 7, "day": 15 } }, "productions": [ { "name": "Microsoft", "number": 2 //THIS IS MISSING AT THE MOMENT } ], "total": 2 } ]我按照月份和年份使用mongoose.js给出问题组的答案，我非常接近解决这个问题，但并不完全解决。 我现在正处于无法真正看到树木的地方！
到目前为止，这是我的代码......
Job.aggregate({ $project: { date: { year: { $year: "$starttime", }, month: { $month: "$starttime" }, day: { $dayOfMonth: "$starttime" } }, customer: "$customer", } }, { $group: { _id: { date: { year: "$date.year", month: "$date.month", day: "$date.day" }, }, productions: { $addToSet: { name: "$customer", }, }, total: { $sum: 1 } }, }, function(error, customers) { if (error) res.json(error); res.json(customers); })缺少的是每个日期的“产品编号”总和。 我刚收到日期总计。 谁能告诉我哪里出错了？ 我非常感激。 谢谢。
Here's how my collection looks...
{ "_id" : ObjectId("53c66071409aeb38133c6ece"), "starttime" : ISODate("2014-07-15T11:22:25Z"), "product" : "1", "customer" : "Microsoft" } { "_id" : ObjectId("53c66071409aeb38133c6ecf"), "starttime" : ISODate("2014-07-15T11:22:25.882Z"), "customer" : "Microsoft", } { "_id" : ObjectId("53c66072409aeb38133c6ed0"), "starttime" : ISODate("2014-07-16T11:22:26.15Z"), "customer" : "Microsoft", } { "_id" : ObjectId("53c66072409aeb38133c6ed0"), "starttime" : ISODate("2014-07-16T11:22:27.15Z"), "customer" : "Apple", }I would like to group the data by date and format the output so it shows like this... (I've put a comment on what I'm missing)
[ { "_id": { "date": { "year": 2014, "month": 7, "day": 16 } }, "productions": [ { "name": "Microsoft", "number": 1 //THIS IS MISSING AT THE MOMENT }, { "name": "Apple", "number": 1 //THIS IS MISSING AT THE MOMENT } ], "total": 2 }, { "_id": { "date": { "year": 2014, "month": 7, "day": 15 } }, "productions": [ { "name": "Microsoft", "number": 2 //THIS IS MISSING AT THE MOMENT } ], "total": 2 } ]I followed the answer given to question groups by month and year using mongoose.js and I'm fairly close to solving this but not quite there. I'm at the point now where I can't really see the wood for the trees!
Here's my code so far...
Job.aggregate({ $project: { date: { year: { $year: "$starttime", }, month: { $month: "$starttime" }, day: { $dayOfMonth: "$starttime" } }, customer: "$customer", } }, { $group: { _id: { date: { year: "$date.year", month: "$date.month", day: "$date.day" }, }, productions: { $addToSet: { name: "$customer", }, }, total: { $sum: 1 } }, }, function(error, customers) { if (error) res.json(error); res.json(customers); })The thing that's missing is the "productions number" total for each date. I just get the Date total. Can someone tell me where I'm going wrong? I'd appreciate it very much. Thanks.
最满意答案
要实现您的目标，您需要在聚合管道中使用2 $group阶段。
db.Job.aggregate([
{"$project" : {"date":{"year":{"$year":"$starttime"},"month":{"$month":"$starttime"},"day":{"$dayOfMonth":"$starttime"}},"customer":"$customer"}},
// Group by "customer" as well
{"$group" : {
"_id":{"date":{"year":"$date.year","month":"$date.month","day":"$date.day",customer:"$customer"}},
"total":{"$sum":1}
}},
// Adding a $project phase just to simplify the JSON
{"$project" : {year:"$_id.date.year", month:"$_id.date.month", day:"$_id.date.day", customer:"$_id.date.customer", total:"$total", _id:0}},
// Second group phase to get the desired output
{"$group" : {
_id:{year:"$year", month:"$month", day:"$day"},
productions:{$addToSet:{name:"$customer", number:"$total"}},
total:{$sum:"$total"}
}}
])
上面的查询产生输出：
{
"result" : [
{
"_id" : {
"year" : 2014,
"month" : 7,
"day" : 15
},
"productions" : [
{
"name" : "Microsoft",
"number" : 2
}
],
"total" : 2
},
{
"_id" : {
"year" : 2014,
"month" : 7,
"day" : 16
},
"productions" : [
{
"name" : "Microsoft",
"number" : 1
},
{
"name" : "Apple",
"number" : 1
}
],
"total" : 2
}
],
"ok" : 1
}
要更好地了解发生的情况，请参阅管道的每个阶段的输出。
To achieve what you want, you will need 2 $group phases in your aggregation pipeline.
db.Job.aggregate([
{"$project" : {"date":{"year":{"$year":"$starttime"},"month":{"$month":"$starttime"},"day":{"$dayOfMonth":"$starttime"}},"customer":"$customer"}},
// Group by "customer" as well
{"$group" : {
"_id":{"date":{"year":"$date.year","month":"$date.month","day":"$date.day",customer:"$customer"}},
"total":{"$sum":1}
}},
// Adding a $project phase just to simplify the JSON
{"$project" : {year:"$_id.date.year", month:"$_id.date.month", day:"$_id.date.day", customer:"$_id.date.customer", total:"$total", _id:0}},
// Second group phase to get the desired output
{"$group" : {
_id:{year:"$year", month:"$month", day:"$day"},
productions:{$addToSet:{name:"$customer", number:"$total"}},
total:{$sum:"$total"}
}}
])
The above query produces the output:
{
"result" : [
{
"_id" : {
"year" : 2014,
"month" : 7,
"day" : 15
},
"productions" : [
{
"name" : "Microsoft",
"number" : 2
}
],
"total" : 2
},
{
"_id" : {
"year" : 2014,
"month" : 7,
"day" : 16
},
"productions" : [
{
"name" : "Microsoft",
"number" : 1
},
{
"name" : "Apple",
"number" : 1
}
],
"total" : 2
}
],
"ok" : 1
}
To better understand what is happening, see the output of each phase of the pipeline.
更多推荐
发布评论