MongoDB Month wise grouping

user_10465501 0
Viewed 65

Can Somebody Please help in getting the last 6 months count of my given data?

This My Data

  {
    "_id" : ObjectId("59815d4704ca1760a45957ca"),
    "ticketId" :"TCKT0HF652Y",
    "createdAt" : ISODate("2020-06-02T05:03:57Z")
},
{
    "_id" : ObjectId("59815d5404ca1760a45957cb"),
    "ticketId" :"TCKT0HF8849A",
    "createdAt" : ISODate("2020-06-02T05:04:11Z")
},
{
    "_id" : ObjectId("5980191d04ca1760a45957cd"),
    "ticketId" :"TCKT0H4953Z",
    "createdAt" : ISODate("2020-05-01T06:00:46Z")
},
  {
    "_id" : ObjectId("59815d4704ca1760a45957ca"),
    "ticketId" :"TCKT0HF339Y",
    "createdAt" : ISODate("2020-05-02T05:03:57Z")
},
{
    "_id" : ObjectId("59815d5404ca1760a45957cb"),
    "ticketId" :"TCKT0HF839A",
    "createdAt" : ISODate("2020-05-02T05:04:11Z")
},
{
    "_id" : ObjectId("5980191d04ca1760a45957cd"),
    "ticketId" :"TCKT0HF9582Z",
    "createdAt" : ISODate("2020-04-01T06:00:46Z")
}

And My Query is 

Tickets.aggregate([
          {
            $project: {
              count: {$sum: 1},
              month: {$month: "$createdAt"},
              year: {$year: "$createdAt"},
            },
          },
          {
            $group: {
              _id: {month: "$month", year: "$year"},
              total: {$sum: "$count"},
            },
          },
        ])

And This is the result I am getting

"data": [
            {
                "_id": {
                    "month": 6,
                    "year": 2020
                },
                "total": 2
            },
            {
                "_id": {
                    "month": 5,
                    "year": 2020
                },
                "total": 3
            },
            {
                "_id": {
                    "month": 4,
                    "year": 2020
                },
                "total": 1
            }
        ]

My Requirement is to generate the last 6 months' data irrespective of whether the month has data or not. So the result I am expecting is 

"data": [
            {
                "_id": {
                    "month": 6,
                    "year": 2020
                },
                "total": 2
            },
            {
                "_id": {
                    "month": 5,
                    "year": 2020
                },
                "total": 3
            },
            {
                "_id": {
                    "month": 4,
                    "year": 2020
                },
                "total": 1
            },
           {
              "_id": {
                    "month": 3,
                    "year": 2020
                },
                "total": 0
            },
           {
               "_id": {
                    "month": 2,
                    "year": 2020
                },
                "total": 0
           },
           {
               "_id": {
                    "month": 1,
                    "year": 2020
                },
                "total": 0
           }
        ]

And can we also display month names instead of month numbers?.

mongoose mongodb-query node.js mongodb javascript
1 Answers

I don't think you you output Months names natively in MongoDB. I suggest Moment.js library. Could be this one:

Tickets.aggregate([
   {
      $group: {
         _id: {
            month: { $month: "$createdAt" },
            year: { $year: "$createdAt" }
         },
         total: { $sum: 1 },
      },
   }
]).forEach(function (row) {
   print(moment.utc(row._id.year + "-" + row._id.month, "YYYY-MM").format("MMMM YYYY") + ": " + row.total);
})
user_30272660
Related