CODEWITHSUNDEEP
node.jsmongooseaggregation-framework
tbhaxor
tbhaxor

Reputation: 1943

Adding Missing Dates in MongoDB Aggregation

In this problem, I want to get the user subscription for the following time range

For this time range, i want to display the $group of all possible dates. 

[
  { Dates: '2020-05-21', Count: 3, AverageIncome: 30 },
  { Dates: '2020-05-22', Count: 10, AverageIncome: 30 },
  { Dates: '2020-05-24', Count: 1, AverageIncome: 37 },
  { Dates: '2020-05-25', Count: 1, AverageIncome: 30 },
  { Dates: '2020-05-26', Count: 2, AverageIncome: 65 }
]

I want to add the missing dates as well with accumulator data set to 0. For example, it should be

[
  { Dates: '2020-05-21', Count: 3, AverageIncome: 30 },
  { Dates: '2020-05-22', Count: 10, AverageIncome: 30 },
  { Dates: '2020-05-23', Count: 0, AverageIncome: 0 },
  { Dates: '2020-05-24', Count: 1, AverageIncome: 37 },
  { Dates: '2020-05-25', Count: 1, AverageIncome: 30 },
  { Dates: '2020-05-26', Count: 2, AverageIncome: 65 },
  { Dates: '2020-05-27', Count: 0, AverageIncome: 0 },
  { Dates: '2020-05-28', Count: 2, AverageIncome: 0 },
]

PS: At this time the UTC time would be 28th may 11:45 pm

The aggregation pipeline stages i am using

const $agg = await Subscription.aggregate([
    {
      $match: {
        createdOn: {
          $gte: moment(req.query.from).toDate(),
          $lte: moment(req.query.to).toDate()
        },
        isCancelled: false
      }
    },

    {
      $group: {
        _id: {
          $dateToString: {
            format: "%Y-%m-%d",
            date: "$createdOn"
          }
        },
        count: { $sum: 1 },
        avgAmount: { $avg: "$amountPaid" }
      }
    },
    {
      $project: {
        _id: false,
        sortKey: {
          $dateFromString: {
            dateString: "$_id"
          }
        },
        Dates: "$_id",
        Count: "$count",
        AverageIncome: "$avgAmount"
      }
    },
    {
      $sort: {
        sortKey: 1
      }
    },
    {
      $project: {
        sortKey: false
      }
    }
  ]);

Upvotes: 2

Views: 451

Answers (1)

Chintan Palan
Chintan Palan

Reputation: 1106

I had faced the similar issue some time back, and here is the approach that I followed:

function fillEmptyDates($agg, $defaultData, from, to) {
  const $aggObj = {}; //Taking this as an object for easy checking

  $agg.map((agg) => {
    $aggObj[agg.Date] = agg; //Making an object from array entries with Date as key
  });

  const $loopDate = moment(from);
  const $endDate = moment(to);

  //Starting from from date to to date, checking if any date does not have entry in $aggObje
  while ($endDate.isSameOrAfter($loopDate)) {
    let $aggDate = $loopDate.format("YYYY-MM-DD");

    if (!$aggObj.hasOwnProperty($aggDate)) {
      //If any date does not have entry, creating a new entry from default aggregates and giving it the date as current date
      $aggObj[$aggDate] = {
        ...$defaultData,
        Date: $aggDate
      };
    }
    $loopDate.add(1, "day"); //Incrementing aggregate date
  }

  //Converting back to array and sorting by date
  return Object.values($aggObj).sort((a, b) =>
    moment(a.Date).isBefore(moment(b.Date)) ? -1 : 1
  );
};

//You can call this function like so
$agg = fillEmptyDates(
            $agg, 
            { Count: 0, AverageIncome: 0 },
            req.query.from,
            req.query.to,
        )

Upvotes: 3

Related Questions