Adding document with missing sequence date in mongodb aggregation

Question

Here see this Collection here have a record with createdAt with 14Feb and one with 16Feb, Now querying an aggregation on the collection such as I want to add an document of 15Feb as it was missing in collection with viewCount and clickCount as 0.

So looking for some way to add missing date record/document in aggreagation pipeline.

Pipeline I have

          [
            {
              $group: {
                _id: {
                  $dateToString: { format: "%Y-%m-%d", date: "$createdAt" },
                },
                viewCount: { $sum: "$viewCount" },
                clickCount: { $sum: "$clickCount" },
              },
            },
            { $sort: { _id: 1 } },
            {
              $group: {
                _id: null,
                data: {
                  $push: {
                    k: "$_id",
                    v: { viewCount: "$viewCount", clickCount: "$clickCount" },
                  },
                },
              },
            },
            {
              $project: {
                _id: 0,
                data: { $arrayToObject: "$data" },
              },
            },
          ]```

I looking for some operator in mongo or some way to do this

Answer 1

By combination of both javascript and aggregation pipeline, you can achieve the desired result.

// let's say datesArray has all the dates which are required. 
const datesArray = getDates(formattedFromDate, formattedToDate);


// aggregation pipeLine
    const pipeLine = [
  {
    $group: {
      _id: null,
      data: {
        $push: {
          date: '$date',
          viewCount: '$viewCount',
          clickCount: '$clickCount',
        },
      },
    },
  },
  {
    $project: {
      _id: 0,
      data: {
        $map: {
          input: datesArray,
          in: {
            k: '$$this',
            v: {
              $cond: [
                {
                  $in: ['$$this', '$data.date'],
                },
                {
                  viewCount: {
                    $arrayElemAt: [
                      '$data.viewCount',
                      {
                        $indexOfArray: ['$data.date', '$$this'],
                      },
                    ],
                  },
                  clickCount: {
                    $arrayElemAt: [
                      '$data.clickCount',
                      {
                        $indexOfArray: ['$data.date', '$$this'],
                      },
                    ],
                  },
                },
                {
                  viewCount: 0,
                  clickCount: 0,
                },
              ],
            },
          },
        },
      },
    },
  },
];

Answer 2

You can use $densify and $fill stages to fill gaps in your data. As a sample, I have changed your pipeline to not group by strings, but use $dateTrunc to cut of the dates after the day, so all you can group by day but still use an ISODate for easier interpolation afterwards.

Please note that this sample pipeline does only contain the first of the stages of your pipeline and can serve as a starting point. In order to show how you can fill in missing values, I have stopped at this point:

[
  {
    $group: {
      _id: {
        $dateTrunc: {
          date: "$createdAt",
          unit: "day"
        }
      },
      viewCount: {
        $sum: "$viewCount"
      },
      clickCount: {
        $sum: "$clickCount"
      }
    }
  },
  {
    $sort: {
      _id: 1
    }
  },
  {
    $densify: {
      field: "_id",
      range: {
        step: 1,
        bounds: "full",
        unit: "day"
      }
    }
  },
  {
    $fill: {
      output: {
        viewCount: {
          value: 0
        },
        clickCount: {
          value: 0
        }
      }
    }
  }
]

Use this mongoplayground to test.