Mongodb - Fetching last 30 days of Mongo records, summing by date, and filling in missing dates in range

DasBeasto
March 16, 2022
162 views
0 votes
2 Answers

I have a Mongo database filled with "Events" records, that look like this:

{
    timestamp: 2022-03-15T22:11:34.711Z,
    _id: new ObjectId("62310f16b0d71321e887a905")
}

Using a NodeJs server, I need to fetch the last 30 days of Events, grouped/summed by date, and any dates within that 30 days with no records need to be filled with 0.

Using this code I can get the correct events, grouped/summed by date:

Event.aggregate( [
      { 
          $match:  {   
              timestamp: {
                  $gte: start,
                 $lte: end,
             }
        } 
    },
    {
        $project: {
            date: {
               $dateToParts: { date: "$timestamp" }
            },
         }
      },
      {
         $group: {
            _id: {
               date: {
                  year: "$date.year",
                  month: "$date.month",
                  day: "$date.day"
               }
            },
            "count": { "$sum": 1 }
         }
    }
] )

This will return something like this:

[
    {
        "_id": {
            "date": {
                "year": 2022,
                "month": 3,
                "day": 14
            }
        },
        "count": 3
    },
    {
        "_id": {
            "date": {
                "year": 2022,
                "month": 3,
                "day": 15
            }
        },
        "count": 8
    },
]

I also have this Javascript code to generate the last 30 days of dates:

 const getDateRange = (start, end)  => {
    const arr = [];
    for(let dt = new Date(start); dt <= end; dt.setDate(dt.getDate() + 1)){
        arr.push(new Date(dt));
    }
    return arr;
};
const subtractDays = (date, days) => {
    return new Date(date.getTime() - (days * 24 * 60 * 60 * 1000));
}

const end = new Date(); 
const start = subtractDays(end, 30);
const range = getDateRange(start, end);

Which returns something like this:

[
  2022-03-09T01:13:10.769Z,
  2022-03-10T01:13:10.769Z,
  2022-03-11T01:13:10.769Z,
  2022-03-12T01:13:10.769Z,
  2022-03-13T01:13:10.769Z,
  ...
]

It seems like I have all the pieces, but I’m having trouble putting all this together to do what I need in an efficient way. Any push in the right direction would be appreciated.

Answers

Whenever one has to work with date/time arithmetic then I recommend a library like moment.js

const end = moment().startOf('day').toDate(); 
const start = moment().startOf('day').subtract(30, 'day').toDate();

In MongoDB version 5.0 you can use $dateTrunc(), which is shorter than $dateToParts and { year: "$date.year", month: "$date.month", day: "$date.day" }

You need to put all data in an array ({$group: {_id: null, data: { $push: "$$ROOT" }}) and then at missing elements with $ifNull:

event.aggregate([
   {
      $match: {
         timestamp: { $gte: start, $lte: end }
      }
   },
   {
      $group: {
         _id: { $dateTrunc: { date: "$timestamp", unit: "day" } },
         count: { $sum: 1 }
      }
   },
   { $project: {timestamp: "$_id", count: 1, _id: 0} },
   {
      $group: {
         _id: null,
         data: { $push: "$$ROOT" }
      }
   },
   {
      $set: {
         data: {
            $map: {
               input: { $range: [0, 30] },
               as: "i",
               in: {
                  $let: {
                     vars: {
                        day: { $dateAdd: { startDate: start, amount: "day", unit: "$$i" } }
                     },
                     in: {
                        $ifNull: [
                           {
                              $first: {
                                 $filter: {
                                    input: "$data",
                                    cond: { $eq: ["$$this.timestamp", "$$day"] }
                                 }
                              }
                           },
                           { timestamp: "$$day", count: 0 }
                        ]
                     }
                  }
               }
            }
         }
      }
   },
   { $unwind: "$data" }
])

$range operator supports only integer values, that’s the reason for using $let. Otherwise, if you prefer to use the external generated range, it would be

{
   $set: {
      data: {
         $map: {
            input: range,
            as: "day",
            in: {
               $ifNull: [
                  {
                     $first: {
                        $filter: {
                           input: "$data",
                           cond: { $eq: ["$$this.timestamp", "$$day"] }
                        }
                     }
                  },
                  { timestamp: "$$day", count: 0 }
               ]
            }
         }
      }
   }
}

And for MongoDB version 5.1 you may have a look at $densify

Use aggregation stage densify if you’re using MongoDB version 5.1 or later. But for lower version, below query can be used.

db.collection.aggregate([
  {
    $match: {
      timestamp: {
        $gte: {
          "$date": "2022-03-01T00:00:00.000Z"
        },
        $lte: {
          "$date": "2022-03-31T23:59:59.999Z"
        },
      }
    }
  },
  {
    $project: {
      date: {
        $dateToParts: {
          date: "$timestamp"
        }
      },
    }
  },
  {
    $group: {
      _id: {
        date: {
          year: "$date.year",
          month: "$date.month",
          day: "$date.day"
        }
      },
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$group": {
      "_id": null,
      "originData": {
        "$push": "$$ROOT"
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "data": {
        "$concatArrays": [
          {
            "$map": {
              "input": {
                "$range": [
                  0,
                  30,
                  1
                ]
              },
              "in": {
                "$let": {
                  "vars": {
                    "date": {
                      "$add": [
                        {
                          "$date": "2022-03-01T00:00:00.000Z"
                        },
                        {
                          "$multiply": [
                            "$$this",
                            86400000
                          ]
                        }
                      ]
                    }
                  },
                  "in": {
                    "_id": {
                      "date": {
                        "day": {
                          "$dayOfMonth": "$$date"
                        },
                        "month": {
                          "$month": "$$date"
                        },
                        "year": {
                          "$year": "$$date"
                        }
                      }
                    },
                    "count": 0
                  }
                }
              }
            }
          },
          "$originData"
        ]
      }
    }
  },
  {
    "$unwind": "$data"
  },
  {
    $group: {
      _id: {
        date: {
          year: "$data._id.date.year",
          month: "$data._id.date.month",
          day: "$data._id.date.day"
        }
      },
      "count": {
        "$sum": "$data.count"
      }
    }
  },
  {
    "$sort": {
      "_id.date.year": 1,
      "_id.date.month": 1,
      "_id.date.day": 1
    }
  }
])

Link to online playground. https://mongoplayground.net/p/5I0I04HoHXm

Please signup or login to give your own answer.

Click here to cancel reply.

Mongodb – Fetching last 30 days of Mongo records, summing by date, and filling in missing dates in range

Answers