Aggregate query by Year and Month in MongoDB

[Bhanu_Prakash_Thunuguntla]

I am using MongoDB 4.2. I have the following collection and the aggregation query to return count by Year & Month within a date range.

[
  {
    "_id": ObjectId("60096afd7568e5a724966237"),
    "title": "One Hospitals",
    "createdAt": ISODate("2021-01-21T11:52:29.215Z")
  },
  {
    "_id": ObjectId("605492c632016f7bdd74c282"),
    "title": "Customer-5",
    "createdAt": ISODate("2021-07-19T12:02:14.134Z")
  },
  {
    "_id": ObjectId("60eeb32209434f1b24aff594"),
    "title": "UTR-007-HealthEndpoints",
    "createdAt": ISODate("2021-07-14T09:49:22.521Z")
  },
  {
    "_id": ObjectId("613a515cb24382575e7e766b"),
    "title": "UTR-004-005-Team",
    "createdAt": ISODate("2021-09-09T18:24:28.942Z")
  },
  {
    "_id": ObjectId("61605c5192467e75213a4374"),
    "title": "UTR-004-005-GC-Team",
    "createdAt": ISODate("2021-10-08T14:57:21.375Z")
  },
  {
    "_id": ObjectId("61826734c82e4e52c4663e1f"),
    "title": "Two Registry",
    "createdAt": ISODate("2021-11-03T10:40:52.611Z")
  },
  {
    "_id": ObjectId("61b090976a08dda345c15fb2"),
    "title": "Api Customer",
    "createdAt": ISODate("2021-12-08T11:01:43.011Z")
  }
]

Aggregation query is as follows.

db.collection.aggregate([
  {
    $match: {
      $and: [
        {
          "createdAt": {
            $gte: ISODate("2021-07-01")
          }
        },
        {
          "createdAt": {
            $lte: ISODate("2021-12-31")
          }
        }
      ],
      
    }
  },
  {
    $group: {
      _id: {
        $dateToString: {
          "date": "$createdAt",
          "format": "%Y-%m"
        }
      },
      Count: {
        $sum: 1
      },
      
    }
  }
])

The above query is returning the following output.

[
  {
    "Count": 1,
    "_id": "2021-09"
  },
  {
    "Count": 1,
    "_id": "2021-12"
  },
  {
    "Count": 1,
    "_id": "2021-11"
  },
  {
    "Count": 2,
    "_id": "2021-07"
  },
  {
    "Count": 1,
    "_id": "2021-10"
  }
]

Can anyone help to achieve the following please?

1. The input documents do not have any data for 2021-08 so add 0 count for missing months within the date range mentioned in the match group
2. Add a new field called TITLE with MMM YYYY (like Jul 2021)
3. sort the output based on _id above

Overall, the expected output should be as below.

[
  {
    "Count": 2,
    "_id": "2021-07",
	"Title": "Jul 2021"
  },
  {
    "Count": 0,
    "_id": "2021-08",
	"Title": "Aug 2021"
  },
  {
    "Count": 1,
    "_id": "2021-09",
	"Title": "Sep 2021"
  },
  {
    "Count": 1,
    "_id": "2021-10",
	"Title": "Oct 2021"
  },  {
    "Count": 1,
    "_id": "2021-11",
	"Title": "Nov 2021"
  },  
  {
    "Count": 1,
    "_id": "2021-12",
	"Title": "Dec 2021"
  }
]

I have the MongoDB Playground here for your reference.

Your help is very much appreciated.

[steevej (Steeve Juneau)]

@Bhanu_Prakash_Thunuguntla

The input documents do not have any data for 2021-08 so add 0 count for missing months within the date range mentioned in the match group

Some operations like the above are better handle with the client or application server. Do not complicate your aggregation for such a goody.

@Bhanu_Prakash_Thunuguntla

Add a new field called TITLE with MMM YYYY (like Jul 2021)

Same as above, looks like a presentation layer NTH thing that can be easily handle at the application layer. Keep your data layer ASAP. It scales better at the mid-layer than at the server.

@Bhanu_Prakash_Thunuguntla

sort the output based on _id above

As simple as

sort_stage = { '$sort' : { _id:1} }

If you have an index on createdAt it might be more efficient to $sort right after $match and before $group rather than at the end.

[steevej (Steeve Juneau)]

@Bhanu_Prakash_Thunuguntla, please followup on your post. If my answer help you please mark my post as the solution. This will help this forum helpful and efficient. It is simply courtesy.