group by on date and get count date wise using mongodb?

Question

I want to go show data in the chart using date wise last 7 days date with data.and i am using this for database is mongodb and working in nodejs. i have create also one query for that generate date wise count but i am not getting success. i am getting count whole date wise. not apply group by on date. and in the database i have store datetime in timestamp so i need to here work with only date.here i have also generate last 7 day and generate lastdate in the timestamp and apply one condition lastdate greater than or equal date to get count. so here i have listed my code and object so any see and have the idea how can fix it then please let me know.

This is my array of object =>

{
  "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
  "Action" : "Comment",
  "datetime" : 1507099928000 // 4th oct 2017 convert date just for info here write
},
{
   "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
    "Action" : "Comment",
    "datetime" : 1507099928000  // 4th oct 2017 convert date just for info here write
}
{
    "_id" : ObjectId("578fa05a7391bb0d34bd3c30"),
     "Action" : "Comment",
     "datetime" : 1507186328000 // 5th oct 2017 convert date just for info here write
}
{
  "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
  "Action" : "Comment",
  "datetime" : 1507193528000 // 5th oct 2017 convert date just for info here write
},
{
   "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
    "Action" : "Comment",
    "datetime" : 1507279928000 // 6th oct 2017 convert date just for info here write
}
{
    "_id" : ObjectId("578fa05a7391bb0d34bd3c30"),
     "Action" : "Comment",
     "datetime" : 1507020728000 // 3th oct 2017 convert date just for info here write
}
{
  "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
  "Action" : "Comment",
  "datetime" : 1507279928000  // 6th oct 2017 convert date just for info here write
},
 {
  "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
  "Action" : "like",
  "datetime" : 1507279928000  // 6th oct 2017 convert date just for info here write
}

my current o/p=>

{ _id: null, count: 8 }

my expecated o/p =>

{ _id: 3-10-2017, count: 1,Action = "comment" }
{ _id: 4-10-2017, count: 2,Action = "comment" }
{ _id: 5-10-2017, count: 2,Action = "comment" }
{ _id: 6-10-2017, count: 2,Action = "comment" }

here this is my query =>

var lastdate = 1507012285558; // this is last date 3rd oct 2017 now here to greate than date find and get count

   InstaAc.aggregate([
                        {
                            "$match": {
                                "_id": ObjectId("595e3b2033961713940442cf"),
                                "History.datetime": {
                                    "$gte": lastdate
                                }
                            }
                        },
                        {
                            "$unwind": "$History"
                        },
                        {
                            "$match": {
                                "History.datetime": {
                                    "$gte": lastdate
                                },
                                "History.Action": {
                                    $eq: "comment"
                                }
                            }
                        },                  
                        {
                            "$group": {
                                "_id": "$_id",       
                                "count": {
                                    "$sum": 1
                                },
                                "History": {
                                    "$push": "$History"
                                }
                            }
                        },
                        {
                            "$sort": {
                                "_id": 1
                            }
                        }
                        ]).exec(function (err, data) {
                            if (err) {
                                console.log(err); 
                            }
                            else {                                  
                                console.log(data[0]);                                   
                            }
                        })

Possible duplicate of [how to get count date wise using group by from the array object in mongodb?](https://stackoverflow.com/questions/46617530/how-to-get-count-date-wise-using-group-by-from-the-array-object-in-mongodb) — s7vr, Oct 10 '17 at 12:43

score -1 · Answer 1 · edited Oct 10 '17 at 11:22

-1

in query you have to modify this line

{
    "$group": {
        "_id": {datetime:"$datetime",action:"$Action"},
        "count": {
            "$sum": 1
        },
    }
}

in here you have to group dateandtime and also action wise

and in array of object you have to convert your timestamp into new date

{
  "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
  "Action" : "Comment",
  "datetime" :new Date(1507099928000)
},

you get output

{
    "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
    "Action" : "Comment",
    "datetime" : ISODate("2017-10-04T06:52:08.000Z")
}

in projection you could include like

{
    "$project": {
        _id:0,
        "yearMonthDay": { 
            $dateToString: { 
                format: "%d-%m-%Y", 
                date: "$_id.datetime" 
            } 
        },
        action:"$_id.action",
        count:1 
    }
}

edited Oct 10 '17 at 11:22

Florian Koch

1,372
1
30
49

answered Oct 10 '17 at 11:00

Anushka Ahir

136
7

can you please give me some more little bit hint on this post – Edit Oct 10 '17 at 11:21
i am getting error like this "can't convert from BSON type double to Date" – Edit Oct 10 '17 at 11:23
can you send me the code which you write for converting in date?? – Anushka Ahir Oct 10 '17 at 11:28
i have just add your solution in my query then getting error – Edit Oct 10 '17 at 11:32
i don't have write any code for convert date i am just add your code in my query and my query already have in the post – Edit Oct 10 '17 at 11:33
{ "_id" : ObjectId("578fa05a7391bb0d34bd3c28"), "Action" : "Comment", "datetime" : 1507099928000 } – Anushka Ahir Oct 10 '17 at 11:33
in this you only write – Anushka Ahir Oct 10 '17 at 11:34
new Date(1507193528000) and you get output in ISO Date format – Anushka Ahir Oct 10 '17 at 11:35
so you mean i need to here "$gte": 1507012285558 pass like new date() something and put your after solution ? – Edit Oct 10 '17 at 11:35
can you please tell me where i need to pass this new date() ?? – Edit Oct 10 '17 at 11:38
i have mention in array of object you create in db in that you have to edit document manually and convert object one by one using new Date() and you get output in ISODate format in db (not in query) you have to edit collection – Anushka Ahir Oct 10 '17 at 11:54
no it's not possible i can't change in the document – Edit Oct 10 '17 at 12:08
then you done only group by i have answered not projection that you use in my answer – Anushka Ahir Oct 10 '17 at 12:13
and no need to convert into date – Anushka Ahir Oct 10 '17 at 12:13
hi can you help me how can generate remaining dates using this query? – Edit Oct 11 '17 at 06:37
in database?? you have to convert date in ISO date format – Anushka Ahir Oct 11 '17 at 06:46
no i am make one query for that and done with this wave – Edit Oct 11 '17 at 06:47
please look that https://stackoverflow.com/questions/46681304/how-to-generate-remaining-dates-using-mongodb this is my new post – Edit Oct 11 '17 at 06:48
have you look that? do you about it how can do that please help me i need that – Edit Oct 11 '17 at 06:56
you want output only that three days or you want dynamically add last date?? – Anushka Ahir Oct 11 '17 at 07:09
dynamically suppose i have make condition like 1507012285558 this is timestamp of 3rd oct then to last 7 days want like 3,4,5,6,7,8,9 now in my object 7,8,9 dates is not store so here i want this 3 dates with count 0 – Edit Oct 11 '17 at 07:11
you want my current running query? – Edit Oct 11 '17 at 07:11
ohhhh you can go with this link there my current query please visit this post https://stackoverflow.com/questions/46681304/how-to-generate-remaining-dates-using-mongodb – Edit Oct 11 '17 at 07:20
have you got my query? – Edit Oct 11 '17 at 07:30
i have set like this condition "$lte": 1507702039000, "$gte": 1507012285558 now here i have two date start and end now i want generate remaining dates? – Edit Oct 11 '17 at 07:36

group by on date and get count date wise using mongodb?

1 Answers1