Fill missing records in mongo aggregate

Question

I have a collection request

{
    _Id: '5b8c0f3204a10228b00a1745,
    createdAt: '2018-09-07T17:18:40.759Z',
    type: "demo" , //["demo","free-try","download",...]
}

And I have a query for fetching the daily number for a specific type.

Query

Model.aggregate([
      {
        $match: { $expr: { $and: filters } },
      },
      {
        $project: {
          day: { $substr: ["$createdAt", 0, 10] },
          type: 1,
          createdAt: 1,
        },
      },
      {
        $group: {
          _id: {
            day: "$day",
            type: "$type",
          },
          total: { $sum: 1 },
        },
      },
      {
        $sort: { _id: 1 },
      },
      {
        $project: {
          _id: "$_id.day",
          date: "$_id.day",
          type: "$_id.type",
          total: 1,
        },
      }
    ])

So I get these results :

[
    {
        "total": 1,
        "_id": "2021-01-06",
        "date": "2021-01-06",
        "type": "print"
    },
    {
        "total": 1,
        "_id": "2021-01-13",
        "date": "2021-01-13",
        "type": "download"
    },
    {
        "total": 1,
        "_id": "2021-03-09",
        "date": "2021-03-09",
        "type": "test"
    },
    {
        "total": 2,
        "_id": "2021-03-29",
        "date": "2021-03-29",
        "type": "demo"
    },
    {
        "total": 1,
        "_id": "2021-04-20",
        "date": "2021-04-20",
        "type": "test"
    },
    {
        "total": 1,
        "_id": "2021-04-21",
        "date": "2021-04-21",
        "type": "download"
    },
    {
        "total": 1,
        "_id": "2021-04-21",
        "date": "2021-04-21",
        "type": "renew"
    },
    {
        "total": 1,
        "_id": "2021-04-22",
        "date": "2021-04-22",
        "type": "print"
    },
    {
        "total": 2,
        "_id": "2021-04-26",
        "date": "2021-04-26",
        "type": "renew"
    },
    {
        "total": 1,
        "_id": "2021-05-03",
        "date": "2021-05-03",
        "type": "test"
    },
    {
        "total": 1,
        "_id": "2021-05-05",
        "date": "2021-05-05",
        "type": "print"
    },
    {
        "total": 1,
        "_id": "2021-05-05",
        "date": "2021-05-05",
        "type": "test"
    },
    {
        "total": 2,
        "_id": "2021-05-31",
        "date": "2021-05-31",
        "type": "demo"
    },
    {
        "total": 1,
        "_id": "2021-06-03",
        "date": "2021-06-03",
        "type": "renew"
    }
]

up to here, everything is fine, but when I need to fill the missing record, so for example if in '2021-06-03' I don't have any request of type "demo" I need to insert this object with a total of 0

{
    "total": 0,
    "_id": "2021-05-31",
    "date": "2021-05-31",
    "type": "demo"
}

so I add this pipeline based on a solution proposed in here

Model.aggregate([
      {
        $match: { $expr: { $and: filters } },
      },
      {
        $project: {
          day: { $substr: ["$createdAt", 0, 10] },
          type: 1,
          createdAt: 1,
        },
      },
      {
        $group: {
          _id: {
            day: "$day",
            type: "$type",
          },
          total: { $sum: 1 },
        },
      },
      {
        $sort: { _id: 1 },
      },
      {
        $project: {
          _id: "$_id.day",
          date: "$_id.day",
          type: "$_id.type",
          total: 1,
        },
      },
      {
        $group: {
          _id: null,
          stats: { $push: "$$ROOT" },
        },
      },
      {
        $project: {
          stats: {
            $map: {
              input: ["2018-09-01", "2018-09-02", "2018-09-03", "2018-09-04", "2018-09-05", "2018-09-06"],
              as: "date",
              in: {
                $let: {
                  vars: { dateIndex: { $indexOfArray: ["$stats._id", "$$date"] } },
                  in: {
                    $cond: {
                      if: { $ne: ["$$dateIndex", -1] },
                      then: { $arrayElemAt: ["$stats", "$$dateIndex"] },
                      else: { _id: "$$date", date: "$$date", total: 0,type: "download" },
                    },
                  },
                },
              },
            },
          },
        },
      },
      {
        $unwind: "$stats",
      },
      {
        $replaceRoot: {
          newRoot: "$stats",
        },
      },
    ])

but this solution adds only a single object by missing day, and I need an object per type, so any solution would be appreciated

Answer 1

You can simply do it with $facet

• $facet helps to categorize the incoming data. So I get two arrays. One is match dates and another one is non match dates. In the match dates we need to add the condition
• $concatArrays to join multiple arrays into one
• $unwind to deconstruct the array
• $replaceRoot to make it to root

Here is the code

db.collection.aggregate([
  {
    "$facet": {
      "matchDate": [
        {
          $match: {
            date: { $in: [ "2021-01-13","2021-04-21" ] }
          }
        },
        {
          $addFields: {
            total: { $cond: [{ $eq: [ "$type", "demo" ]}, 0, "$total" ] }
          }
        }
      ],
      "nonMatchDate": [
        {
          $match: {
            date: { $nin: [ "2021-01-13", "2021-04-21" ] }
          }
        }
      ]
    }
  },
  {
    $project: {
      combined: {
        "$concatArrays": [ "$matchDate", "$nonMatchDate" ]
      }
    }
  },
  { "$unwind": "$combined" },
  { "$replaceRoot": { "newRoot": "$combined" }}
])

Working Mongo playground