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.
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.