这是我的收藏品的样子......
{ "_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.
更多推荐
发布评论