Moving average aggregation MongoDB

Question

I'm looking for a simple way to calculate the 90-day moving average for a given date. I have a document called Response which has the given structure:

{
 _id: ObjectId("60e4cf6e783e125bd80f1cf5"),
 createdAt: 2021-07-06T21:47:26.282+00:00,
 value: 8
}

I know how to calculate the 90-day moving average for a given date, for example 2021-08-10, but what I want to achieve is an array of this type:

[ 
  {date: 2021-08-10, average: 6.7}, 
  {date: 2021-08-09, average: 6.8},
  {date: 2021-08-08, average: 6.5},
  ...
]

I've tried to calculate the moving average for a set of dates inside a loop but the performance is absolutely atrocious considering I need to calculate this for as much as to 365 differente dates.

Any ideas on how to solve this? Would really appreciate some help.

Answer 1

If you are not running Monog 5.0 yet, then this would be a starting point:

db.collection.aggregate([
   { $group: { _id: null, data: { $push: "$$ROOT" } } },
   {
      $set: {
         data: {
            $map: {
               input: { $range: [0, { $size: "$data" }] },
               as: "idx",
               in: {
                  createdAt: { $arrayElemAt: ["$data.createdAt", "$$idx"] },
                  window: {
                     $filter: {
                        input: "$data",
                        cond: {
                           $and: [
                              { $gte: ["$$this.createdAt", { $arrayElemAt: ["$data.createdAt", "$$idx"] }] },
                              { $lt: ["$$this.createdAt", { $add: [{ $arrayElemAt: ["$data.createdAt", "$$idx"] }, 1000 * 60 * 60 * 24 * 90] }] }
                           ]
                        }
                     }
                  },
               }
            }
         }
      }
   },
   {
      $set: {
         data: {
            $map: {
               input: "$data",
               in: {
                  createdAt: "$$this.createdAt",
                  window:  "$$this.window.value",
                  average: {$avg: "$$this.window.value"}
               }
            }
         }
      }
   }
])