GroupBy date + mongodb

Question

I have to aggregate the result based on the month of the given document. Consider the following as my document:

{
    "_id" : ObjectId("5b3314a12b05b1b247366f48"),
    "email" : "abc@gmail.com",
    "qwerty":[{
            "id" : "5ba4ebbad1b5eaf038841302",
            "status" : "inprogress",           
            "Date" : "2018-08-20"
        }, 
        {
            "id" : "5ba4ebbad1b5eaf038841303",
            "status" : "inprogress",           
            "Date" : "2018-08-20"
        }]

Following is my query:

var query =[
        { $match: {"email":email} },
        {$unwind: "$courses" },
        {$group:{_id:{$substrCP: ["$qwerty.Date", 5, 2]},count:{$sum:1}}}
    ];

Its working properly. But i $substrCP: ["$qwerty.Date", 5, 2] is based on the date format is "2018-08-20", what if "20-08-2018"?? So its possible to change the above query to accomodate of nay type.

Also i tried with new Date("").getMonth() but its showing as "NaN", i get to know that its not possible to use inside group.

Please suggest your ideas.

https://docs.mongodb.com/manual/reference/operator/aggregation/month/ — Shubham Srivastava, Oct 08 '18 at 05:50
Possible duplicate of [Group by hour and count MongoDB](https://stackoverflow.com/questions/52639932/group-by-hour-and-count-mongodb) — Ashh, Oct 08 '18 at 06:00

Akrion · Accepted Answer · 2018-10-08T06:43:36.280

0

You can utilize $month in combination with $dateFromString to get what you need:

db.collection.aggregate([
  {
    $match: {
      "email": "abc@gmail.com"
    }
  },
  {
    $unwind: "$qwerty"
  },
  {
    $group: {
      _id: {
        $month: {
          $dateFromString: {
            dateString: "$qwerty.Date"
          }
        }
      },
      count: {
        $sum: 1
      }
    }
  }
])

You can see it here with the two different date formats.

To group per the date you can do the same without the $month:

db.collection.aggregate([
  {
    $match: {
      "email": "abc@gmail.com"
    }
  },
  {
    $unwind: "$qwerty"
  },
  {
    $group: {
      _id: {
        $dateFromString: {
          dateString: "$qwerty.Date"
        }
      },
      count: {
        $sum: 1
      }
    }
  }
])

See this version here

edited Oct 08 '18 at 06:43

answered Oct 08 '18 at 05:58

Akrion

18,117
1
34
54

Thanks its working.. But if i change `$month` to `$date`it throwing error `$date` is not recognised – Subburaj Oct 08 '18 at 06:34
Why would you do that? In your question you ware working with the month no? – Akrion Oct 08 '18 at 06:37
I need for `month` ad `date` as separate aggregation query. For month its working perfectly with your post, but for date its not?? – Subburaj Oct 08 '18 at 06:40

GroupBy date + mongodb

1 Answers1