MongoDB aggreagte fills in missing days

Question

I have a collection of products with the following documents:

{ "_id" : 1, "item" : "abc", created: ISODate("2014-10-01T08:12:00Z") } { "_id" : 2, "item" : "jkl", created: ISODate("2014-10-02T09:13:00Z") } { "_id" : 3, "item" : "hjk", created: ISODate("2014-10-02T09:18:00Z") } { "_id" : 4, "item" : "sdf", created: ISODate("2014-10-07T09:14:00Z") } { "_id" : 5, "item" : "xyz", created: ISODate("2014-10-15T09:15:00Z") } { "_id" : 6, "item" : "iop", created: ISODate("2014-10-16T09:15:00Z") } 

I want to draw a graph that describes the number of products per day, so I use the mongodb aggregation structure to calculate the product group by day:

  var proj1 = { "$project": { "created": 1, "_id": 0, "h": {"$hour": "$created"}, "m": {"$minute": "$created"}, "s": {"$second": "$created"}, "ml": {"$millisecond": "$created"} } }; var proj2 = { "$project": { "created": { "$subtract": [ "$created", { "$add": [ "$ml", {"$multiply": ["$s", 1000]}, {"$multiply": ["$m", 60, 1000]}, {"$multiply": ["$h", 60, 60, 1000]} ] }] } } }; db.product.aggregate([ proj1, proj2, {$group: { _id: "$created", count: {$sum: 1} }}, {$sort: {_id: 1}} ]) 

Result in mongo shell:

 { "result" : [ { "_id" : ISODate("2014-10-01T00:00:00.000Z"), "count" : 1 }, { "_id" : ISODate("2014-10-02T00:00:00.000Z"), "count" : 2 }, { "_id" : ISODate("2014-10-07T00:00:00.000Z"), "count" : 1 }, { "_id" : ISODate("2014-10-15T00:00:00.000Z"), "count" : 1 }, { "_id" : ISODate("2014-10-16T00:00:00.000Z"), "count" : 1 } ], "ok" : 1 } 

Of course, for several days there is no product, and the diagram using the above result looks like this:

But the desired chart should look like this:

So the question is: How to add missing days (for example, from the last 30 days) to the result set using count = 0 ? . This means that the desired result set should look like this: this:

 { "result" : [ { "_id" : ISODate("2014-09-16T00:00:00.000Z"), "count" : 0 }, { "_id" : ISODate("2014-09-17T00:00:00.000Z"), "count" : 0 }, ... { "_id" : ISODate("2014-10-01T00:00:00.000Z"), "count" : 1 }, { "_id" : ISODate("2014-10-02T00:00:00.000Z"), "count" : 2 }, { "_id" : ISODate("2014-10-03T00:00:00.000Z"), "count" : 0 }, ... { "_id" : ISODate("2014-10-07T00:00:00.000Z"), "count" : 1 }, { "_id" : ISODate("2014-09-08T00:00:00.000Z"), "count" : 0 }, ... { "_id" : ISODate("2014-10-15T00:00:00.000Z"), "count" : 1 }, { "_id" : ISODate("2014-10-16T00:00:00.000Z"), "count" : 1 }, // also, add some extra days { "_id" : ISODate("2014-10-17T00:00:00.000Z"), "count" : 0 }, { "_id" : ISODate("2014-10-10T00:00:00.000Z"), "count" : 0 } ], "ok" : 1 } 

Answer 1

Using the aggregate to fully address this issue is a pain.
But it can be achieved.
(Requires MongoDB V2.6 +)

 var proj1 = { "$project" : { "created" : 1, "_id" : 0, "h" : { "$hour" : "$created" }, "m" : { "$minute" : "$created" }, "s" : { "$second" : "$created" }, "ml" : { "$millisecond" : "$created" } } }; var proj2 = { "$project" : { "created" : { "$subtract" : [ "$created", { "$add" : [ "$ml", { "$multiply" : [ "$s", 1000 ] }, { "$multiply" : [ "$m", 60, 1000 ] }, { "$multiply" : [ "$h", 60, 60, 1000 ] } ] } ] } } }; var group1 = { $group : { _id : "$created", count : { $sum : 1 } } }; var group2 = { $group : { _id : 0, origin : { $push : "$$ROOT" }, maxDate : { $max : "$_id" } } }; var step = 24 * 60 * 60 * 1000; // milliseconds of one day var project3 = { $project : { origin : 1, extents : { $map : { "input" : [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29], "as" : "e", "in" : { _id : { $subtract : [ "$maxDate", { $multiply : [ step, "$$e"] }] }, count : { $add : [0] } } } } } }; var project4 = { $project : { _id : 0, values : { $setUnion : [ "$origin", "$extents"] } } }; var unwind1 = { $unwind : "$values" }; var group3 = { $group : { _id : "$values._id", count : { $max : "$values.count" } } }; db.product.aggregate([ proj1, proj2, group1, group2, project3, project4, unwind1, group3, { $sort : { _id : 1 } } ]); 

---

I would like to fill in the missing part at the end of the application with something like this for reference:

 function sortResult(x, y) { var t1 = x._id.getTime(); var t2 = y._id.getTime(); if (t1 < t2) { return -1; } else if (t1 == t2) { return 0; } else { return 1; } } var result = db.product.aggregate(); var endDateMilliseconds = result[result.length - 1]._id.getTime(); var step = 24 * 60 * 60 * 1000; // milliseconds of one day var map = {}; for (var i in result) { map[ result[i]._id.getTime() ] = result[i]; } for (var ms = endDateMilliseconds, x = 1; x < 30; x++) { ms -= step; if ( ! ( ms in map ) ) { map[ms] = {_id : new Date(ms), count : 0}; } } var finalResult = []; for (var x in map) { finalResult.push(map[x]); } finalResult.sort(sortResult); printjson(finalResult); 

Answer 2

Well, first of all: non-existent values ​​are evaluated as null (roughly translated as "nada", "nothing", "no"), which is not 0, which is a well-defined value.

MongoDB does not have a semantic understanding of the difference between 0 and 42, for example. So, how should MongoDB decide what value to accept per day at that time (from which Mongo also has no semantic understanding)?

In principle, you have two options: save 0 for every day, when no value should be written, or you repeat in your application for those days when you want to create a chart and give 0 for every day. as a replacement. Id 'suggests doing the first, as this will allow the use of an aggregation structure.