Fill missing dates in records

Solution 1:

You need few additional stages to return default values. First of all you need to use $group with _id set to null to collect all results in one document. Then you can use $map with an array of days as an input. Inside that $map you can use $indexOfArray to find if that date exists in your current result set. If yes (index != -1) then you can return that value, otherwise you need to return default subdocument with views set to 0. Then you can use $unwind to get back a list of documents and $replaceRoot to promote nested stats to a top level.

ProductView.aggregate([
    { $match: { productId: '5b8c0f3204a10228b00a1745' } },
    { $project: { day: { $substr: ["$createdAt", 0, 10] } } },
    {
        $group: {
            _id: "$day",
            count: { $sum: 1 },
            time: { $avg: "$createdAt" },
        }
    },
    { $sort: { _id: 1 } },
    {
        $project: {
            date: '$_id',
            views: '$count',
        },
    },
    {
        $group: {
            _id: null,
            stats: { $push: "$$ROOT" }
        }
    },
    {
        $project: {
            stats: {
                $map: {
                    input: [ "2018-09-01", "2018-09-02", "2018-09-03", "2018-09-04", "2018-09-05" ],
                    as: "date",
                    in: {
                        $let: {
                            vars: { dateIndex: { "$indexOfArray": [ "$stats._id", "$$date" ] } },
                            in: { 
                                $cond: {
                                    if: { $ne: [ "$$dateIndex", -1 ] },
                                    then: { $arrayElemAt: [ "$stats", "$$dateIndex" ] },
                                    else: { _id: "$$date", date: "$$date", views: 0 }
                                } 
                            }
                        }
                    }
                }
            }
        }
    },
    {
        $unwind: "$stats"
    },
    {
        $replaceRoot: {
            newRoot: "$stats"
        }
    }
]).exec((err, result) => ...)

You can generate a static list of dates in your application logic using simple loop. I believe that's possible in MongoDB as well (using $range) but it might complicate this aggregation pipeline. Let me know if you're fine with that or you want to try to generate that array of dates in MongoDB.

Solution 2:

Your question is similar to post from 2014.

All the answers provided at that post are valid and it would be much simpler if you can generate missing days in your application code.

Since you have asked for mongodb solution and lot has changed from 2014 I created a new aggregation pipeline that you can use with 3.6 version.

ProductView.aggregate([
   -- convert the string date into date type for date calcualtions. can avoid this step if you can store the date as date type in collection
    {"$addFields":{"createdAt":{"$dateFromString":{"dateString":"$createdAt"}}}},
      -- strip the time part so we can add whole milliseconds from epoch to calculate next day
    {"$project":{
        "day":{"$dateFromParts":{"year":{"$year":"$createdAt"},"month":{"$month":"$createdAt"},"day":{"$dayOfMonth":"$createdAt"}}}
    }},
      -- generate two sets of data, one that has count by day, other that has unique days, min day and max day
    {"$facet":{
        "daycounts":[{"$group":{"_id":"$day","count":{"$sum":1}}}],
        "maxmindays":[
          {"$group":{
             "_id":null,
             "days":{"$addToSet":"$day"},
             "minday":{"$min":{"$divide":[{"$subtract":["$day",new Date("1-1-1970")]},1000]}},
             "maxday":{"$max":{"$divide":[{"$subtract":["$day",new Date("1-1-1970")]},1000]}}
           }}
        ]
    }},
    {"$project":{
        "data":{
          "$let":{
            "vars":{"maxminday":{"$arrayElemAt":["$maxmindays",0]}},
            "in":{
              -- $range - iterate from min date to max date one day at a time
              "$map":{
                "input":{"$range":["$$maxminday.minday",{"$add": ["$$maxminday.maxday", 60*60*24]},60*60*24]},
                "as":"r",
                "in": {
              -- convert back to milliseconds to get the day
                  "$let":{
                    "vars":{"current":{"$add": [new Date(0), {"$multiply":["$$r", 1000 ]}]}},
                    "in":{
              -- check if the day is in the collection, if yes lookup view inside the daycount facet to get the matching count, else set the view to zero
                      "$cond":[
                        {"$in":["$$current","$$maxminday.days"]},
                        {
                          "date":{"$substr":["$$current",0,10]},
                          "views":{"$let":{"vars":{"daycount":{"$arrayElemAt":["$daycounts",{"$indexOfArray":["$daycounts._id","$$current"]}]}},"in":"$$daycount.count"}}
                        },
                        {"date":{"$substr":["$$current",0,10]},"views":0}
                      ]
                    }
                  }
                }
              }
            }
          }
        }
    }},
    -- flatten the array of data
    {"$unwind":"$data"},
    -- promote the data to top
    {"$replaceRoot":{newRoot:"$data"}}
])