GroupBy Aggregation including missing dates in mongo

Question

I have an aggregation query that gives me a result grouped by dates when a date range is passed. The sample aggregate pipeline looks something like this.

[{
   $match: {
      orderDate: {
        $gte: new Date('2021-08-01T00:00:00+05:30'),
        $lte: new Date('2021-08-06T23:59:59+05:30'),
      }
    },
  {
    $group: {
      _id: {
        $dateToString: {
          format: '%Y-%m-%d',
          date: '$orderDate',
        }
      }
    }
  }]

This fetches the result that is similar to the one below

[
  {
    _id: '2021-08-01',
  },
  {
    _id: '2021-08-02',
  },
  {
    _id: '2021-08-04',
  },
  {
    _id: '2021-08-05',
  },
]

So, notice the day 03, and 06 is missing since they are not in the collection but is inclusive of the date range passed in the aggregate pipeline.

Is there any way to directly query and get these missing dates by tweaking the above query? Which will result in the right value

Takis · Answer 1 · 2021-08-06T12:34:48.153

Maybe the bellow is what you want.

1)Create one collection with a counter for example here 1-10 for 10 days

  [
  {
    "date": 0
  },
  {
    "date": 1
  },
  {
    "date": 2
  },
  {
    "date": 3
  },
  {
    "date": 4
  },
  {
    "date": 5
  },
  {
    "date": 6
  },
  {
    "date": 7
  },
  {
    "date": 8
  },
  {
    "date": 9
  }
  ]

Update this collection and make the data,10 dates using $add Here we start from date "2021-08-01T00:00:00+00:00" and we will end on date "2021-08-10T00:00:00+00:00"

This update will do it(q=the filter,u=the update,here its pipeline update)

{
  "update": "dates",
  "updates": [
    {
      "q": {},
      "u": [
        {
          "$addFields": {
            "date": {
              "$add": [
                "2021-08-01T00:00:00Z",
                {
                  "$multiply": [
                    "$date",
                    24,
                    60,
                    60000
                  ]
                }
              ]
            }
          }
        }
      ],
      "multi": true
    }
  ]
}

Results in (10 dates,1 day difference)

{
  "_id": {
    "$oid": "610c4bf99ccb15d9c9d67b55"
  },
  "date": {
    "$date": "2021-08-01T00:00:00Z"
  }
},{
  "_id": {
    "$oid": "610c4bf99ccb15d9c9d67b56"
  },
  "date": {
    "$date": "2021-08-02T00:00:00Z"
  }
},{
  "_id": {
    "$oid": "610c4bf99ccb15d9c9d67b57"
  },
  "date": {
    "$date": "2021-08-03T00:00:00Z"
  }
}
...

3)Now we have the dates collection with 10 dates,all with 1 day difference we will make a $lookup with the orders collection

Orders collection

[
  {
    "_id": 1,
    "date": "2021-08-01T00:00:00Z"
  },
  {
    "_id": 2,
    "date": "2021-08-01T00:00:00Z"
  },
  {
    "_id": 3,
    "date": "2021-08-05T00:00:00Z"
  },
  {
    "_id": 4,
    "date": "2021-08-03T00:00:00Z"
  }
]

Query(take the pipeline,first filter the wanted dates,and then join only if order is made the same day)

{
  "aggregate": "dates",
  "pipeline": [
    {
      "$match": {
        "$expr": {
          "$and": [
            {
              "$gte": [
                "$date",
                {
                  "$dateFromString": {
                    "dateString": "2021-08-01T00:00:00"
                  }
                }
              ]
            },
            {
              "$lte": [
                "$date",
                {
                  "$dateFromString": {
                    "dateString": "2021-08-05T00:00:00"
                  }
                }
              ]
            }
          ]
        }
      }
    },
    {
      "$lookup": {
        "from": "orders",
        "let": {
          "datesDate": "$date"
        },
        "pipeline": [
          {
            "$match": {
              "$expr": {
                "$eq": [
                  {
                    "$dateToString": {
                      "format": "%Y-%m-%d",
                      "date": "$$datesDate"
                    }
                  },
                  {
                    "$dateToString": {
                      "format": "%Y-%m-%d",
                      "date": "$date"
                    }
                  }
                ]
              }
            }
          }
        ],
        "as": "found-orders"
      }
    },
    {
      "$project": {
        "_id": 0
      }
    }
  ],
  "cursor": {},
  "maxTimeMS": 1200000
}

Results

[
  {
    "date": "2021-08-01T00:00:00Z",
    "found-orders": [
      {
        "_id": 1,
        "date": "2021-08-01T00:00:00Z"
      },
      {
        "_id": 2,
        "date": "2021-08-01T00:00:00Z"
      }
    ]
  },
  {
    "date": "2021-08-02T00:00:00Z",
    "found-orders": []
  },
  {
    "date": "2021-08-03T00:00:00Z",
    "found-orders": [
      {
        "_id": 4,
        "date": "2021-08-03T00:00:00Z"
      }
    ]
  },
  {
    "date": "2021-08-04T00:00:00Z",
    "found-orders": []
  },
  {
    "date": "2021-08-05T00:00:00Z",
    "found-orders": [
      {
        "_id": 3,
        "date": "2021-08-05T00:00:00Z"
      }
    ]
  }
]

This gives for each date(for each day),the orders that were made that day. If one date no orders were made the array is empty. Here is only 10 days,you can make like 5 years date collection based on your needs.

Could you please create a [Playground](https://mongoplayground.net)? I have also a similar type of requirement. [My Post](https://stackoverflow.com/questions/72264956/mongodb-unmatch-aggregate-lookup) @Takis — Pallab, May 26 '22 at 03:24

GroupBy Aggregation including missing dates in mongo

1 Answers1