Aggregate query by Year and Month in MongoDB

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.

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

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.

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.

@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.

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.