How to get average order data for days of week between two dates in mongodb aggregate?

Question

I'm trying to get all orders between two dates, group them by day of week, then average them. Currently the code looks like this:

export const getOrderValuesBetweenTwoDates = async (
  from: number,
  to: number,
) => {
  // from, and to are guaranteed to be Mondays, 00:00
  const orders = await OrderModel.find({
    createdAt: { $lt: to, $gte: from },
  }).exec();

  const totalOfDaysOfWeek = [0, 0, 0, 0, 0, 0, 0];

  orders.forEach((order) => {
    const daysSinceFrom = (order.createdAt - from) / dayInMilliseconds;
    const dayOfWeek = Math.floor(daysSinceFrom) % 7;

    totalOfDaysOfWeek[dayOfWeek] =
      (totalOfDaysOfWeek[dayOfWeek] || 0) + order.value;
  });

  const numberOfWeeks = Math.floor((to - from) / dayInMilliseconds / 7);
  const averageOfDaysOfWeek = totalOfDaysOfWeek.map((v) =>
    Number((v / numberOfWeeks).toFixed(2)),
  );
  return averageOfDaysOfWeek;
};

However, this is not really performant, and I guess if it could be written in aggregation, it would be. Is that possible to convert the above into aggregation?

Sample input (2 weeks):

[
  // 1st mon (total 5)
  { createdAt: 345600000, value: 2 },
  { createdAt: 345600000, value: 3 },
  // 1st tue
  { createdAt: 432000000, value: 1 },
  // 1st wed
  { createdAt: 518400000, value: 1 },
  // 1st thu
  { createdAt: 604800000, value: 1 },
  // 1st fri
  { createdAt: 691200000, value: 1 },
  // 1st sat
  { createdAt: 777600000, value: 1 },
  // 1st sun (2 total)
  { createdAt: 864000000, value: 2 },
  // 2nd mon (1 total)
  { createdAt: 950400000, value: 1 },
  // 2nd tue
  { createdAt: 1036800000, value: 1 },
  // 2nd wed
  { createdAt: 1123200000, value: 1 },
  // 2nd thu
  { createdAt: 1209600000, value: 1 },
  // 2nd fri
  { createdAt: 1296000000, value: 1 },
  // 2nd sat
  { createdAt: 1382400000, value: 1 },
  // 2nd sun (4 total)
  { createdAt: 1468800000, value: 1 },
  { createdAt: 1468800000, value: 1 },
  { createdAt: 1468800000, value: 2 },
]

In the above example I've made 2 special cases, for Monday, and Sunday. There are multiple orders for those days.

For the first Monday there is an order with value 2, and 3, to 5 in total. For the second Monday there is only one order with value 1. The average should be 3.

For Sunday, the first one, there's an order with value 2, and for the second Sunday, there are 3 orders with total value of 4. I'm expecting the average to be 3.

I'm expecting the result to be [3,1,1,1,1,1,3]

Answer 1

1. format the date using $dateToString
2. use $sum to get sum of same day of week
3. get day of week by $dayOfWeek
4. group by days of week and get average by $avg
5. project to get data as desired format

weekDay in output will be number between 1 (Sunday) and 7 (Saturday).

test it at mongoPlayground

db.collection.aggregate([
  {
    "$addFields": {
      createdAt: {
        "$dateToString": {
          "date": {
            "$toDate": "$createdAt"
          },
          "format": "%Y-%m-%d"
        }
      }
    }
  },
  {
    "$group": {
      "_id": "$createdAt",
      "value": {
        "$sum": "$value"
      }
    }
  },
  {
    "$addFields": {
      "createdAt": {
        $dayOfWeek: {
          "$toDate": "$_id"
        }
      }
    }
  },
  {
    "$group": {
      "_id": "$createdAt",
      "average": {
        "$avg": "$value"
      }
    }
  },
  {
    "$project": {
      _id: 0,
      weekDay: "$_id",
      average: 1
    }
  }
])