Aggregate query by Year and Month in MongoDB

Question

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?

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
Add a new field called TITLE with MMM YYYY (like Jul 2021)
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.

Wernfried Domscheit · Accepted Answer · 2021-12-09 16:37:28Z

I would recommend to upgrade to Mongo 5.0, it provides some nice functions, $dateTrunc, $dateAdd, $dateDiff which are very useful in this case. If you don't have them available then you need to deal with Milliseconds, $dateFromParts and $dateToParts. It requires a few more code though.

MongoDB has no nativ support for localizes Date format, e.g. months names. Thus you need to use some JavaScript libraries, e.g. moment.js

Or you can build a lookup for Month names with $switch

var ret = db.collection.aggregate([
   { $match: { createdAt: { $gte: ISODate("2021-07-01"), $lte: ISODate("2021-12-31") } } },
   {
      $group: {
         _id: { $dateTrunc: { date: "$createdAt", unit: "month" } },
         Count: { $count: {} }
      }
   },
   {
      $group: {
         _id: null,
         data: { $push: "$$ROOT" }
      }
   },
   {
      $set: {
         month: {
            $dateDiff: {
               startDate: ISODate("2021-07-01"),
               endDate: ISODate("2021-12-31"),
               unit: "month"
            }
         }
      }
   },
   {
      $set: {
         data: {
            $map: {
               input: { $range: [0, { $add: ["$month", 1] }] },
               as: "m",
               in: {
                  $let: {
                     vars: {
                        month: {
                           $dateAdd: {
                              startDate: ISODate("2021-07-01"),
                              unit: "month",
                              amount: "$$m"
                           }
                        }
                     },
                     in: {
                        _id: "$$month",
                        Count: { $filter: { input: "$data", cond: { $eq: ["$$this._id", "$$month"] } } }
                     }
                  }
               }
            }
         }
      }
   },
   {
      $set: {
         data: {
            $map: {
               input: "$data",
               in: { _id: "$$this._id", Count: { $ifNull: [{ $first: "$$this.Count.Count" }, 0] } }
            }
         }
      }
   },
   { $unwind: "$data" },
   { $replaceWith: "$data" },
   { $sort: { _id: 1 } }
]).toArray();

ret.forEach(function (doc) { doc.Title = moment(doc._id).format('MMM YYYY') })
print(tojsononeline(ret))

Tyler2P · Accepted Answer · 2023-08-08 10:22:18Z

0

$lte or $lt has some problems with date filtering, what I did was that:
FIRST: query all $gte: "your date"
SECOND: query all nin dates

this is how you will solve this problem

{
  $and: [
    {
      date: {
        $gt: "Sun Jan 01 2023 05:00:00 GMT+0500 (Pakistan Standard Time)",
      },
    },
    {
      date: {
        $nin: [{
          date: {
            $lt: "Mon Jan 01 2024 05:00:00 GMT+0500 (Pakistan Standard Time)",
          },
        }],
      },
    },
  ],
}

Above query worked for me. I hope it helps :)

edited Aug 8, 2023 at 10:22

Tyler2P

2,37030 gold badges26 silver badges34 bronze badges

answered Aug 2, 2023 at 14:26

Babar Farooq

1

Collectives™ on Stack Overflow

Aggregate query by Year and Month in MongoDB

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related