how to get count date wise using group by from the array object in mongodb?

Question

i wanna go show daily count show in the chart using mongodb. i am first generate today to last 5 days date in the chart. and in the my mongodb i have the object store daily to daily. show i now i want to get count date wise only using group by but i have no idea how can use group by. i have write one query but this query return all the object and does't return any count so any one have idea how can do that please tell me. i have one date also so i want to start count using this date to equal or greater than.

here this is my query =>

 app.get(prefix + '/GetAnalyticsByUserId', function (req, res, next) {
    var instaid = req.query.instaid;
    var lastdate = req.query.lastdate; // this is my date in the timestamp i want to using this equal or greater date and count 
    InstaAc.aggregate(
                { $match: { _id: ObjectId("595f6bcdeb3db12064f26336") } },
                { $unwind: '$History' },
                { $match: { 'History.Action': { $eq: "Comment" } } },
                { $match: { 'History.datetime': { $gte: "datetime" } } },
                { $group: { _id: '$_id', History: { $push: '$History' } } }).exec(function (err, data) {
                    if (err) {
                        console.log(err); res.send(err)
                    }
                    else {                            
                            console.log(data);
                            res.send(data);                           
                    }
                })
});

this is my array in the db =>

{
  "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
  "Action" : "Comment",
  "datetime" : 1507549688000
},
{
   "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
    "Action" : "Comment",
    "datetime" : 1507553288000
}
{
    "_id" : ObjectId("578fa05a7391bb0d34bd3c30"),
    "Action" : "Comment",
    "datetime" : 1507466888000
}

my expecated o/p =>

 {
  "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
  "Action" : "Comment",
  "datetime" : 1507549688000
  "count":2
},    
{
    "_id" : ObjectId("578fa05a7391bb0d34bd3c30"),
    "Action" : "Comment",
    "datetime" : 1507466888000
    "count":1
}

here above i have just posted some object like this more object is store in the db so i want to get count using group by date.any one know how can do that then please let me.

s7vr · Accepted Answer · 2017-10-10T12:46:07.270

1

You can try below aggregation.

Consider the below record

{
  "_id": ObjectId("59db7a4d4f40c83c7bcaba0d"),
  "History": [
    {
      "_id": ObjectId("578fa05a7391bb0d34bd3c28"),
      "Action": "Comment",
      "datetime": millis 
    },
    {
      "_id": ObjectId("578fa05a7391bb0d34bd3c21"),
      "Action": "Comment",
      "datetime": millis 
    },
    {
      "_id": ObjectId("578fa05a7391bb0d34bd3c22"),
      "Action": "Comment",
      "datetime": millis 
    },
    {
      "_id": ObjectId("578fa05a7391bb0d34bd3c22"),
      "Action": "Comment",
      "datetime": millis 
    }
  ]
}

Aggregation:

InstaAc.aggregate([
  {
    "$match": {
      "_id": ObjectId("59db7a4d4f40c83c7bcaba0d"),
      "History.datetime": {
        "$gte": millis 
      }
    }
  },
  {
    "$unwind": "$History"
  },
  {
    "$match": {
      "History.datetime": {
        "$gte": millis 
      }
    }
  },
  {
  "$addFields": {
     "History.datetime": {
       "$add": [
         new Date(0),
         "$History.datetime"
        ]
      }
    }
  },
  {
    "$group": {
      "_id": {
        "$dateToString": {
          "format": "%Y-%m-%d",
          "date": "$History.datetime"
        }
      },
      "count": {
        "$sum": 1
      },
      "History": {
        "$push": "$History"
      }
    }
  },
  {
    "$sort": {
      "_id": 1
    }
  },
  {
    "$limit": 1
  }
])

Output:

{
  "_id": "2016-07-26",
  "count": 2,
  "History": [
    {
      "_id": ObjectId("578fa05a7391bb0d34bd3c21"),
      "Action": "Comment",
      "datetime": millis 
    },
    {
      "_id": ObjectId("578fa05a7391bb0d34bd3c22"),
      "Action": "Comment",
      "datetime": millis 
    }
  ]
}

edited Oct 10 '17 at 12:46

answered Oct 07 '17 at 15:49

s7vr

73,656
11
106
127

Hello i want generate remaining date also in the o/p so how can i do that can you please know that? – Edit Oct 11 '17 at 05:49
[ { _id: '03-10-2017', count: 1 }, { _id: '04-10-2017', count: 2 }, { _id: '05-10-2017', count: 2 }, { _id: '06-10-2017', count: 2 }, { _id: '07-10-2017', count: 0 },// this 3 dates 7,8,9 is not store in my object so i want also this date with count 0. { _id: '08-10-2017', count: 0 },// { _id: '09-10-2017', count: 0 },]// is that possible and i want also action = "like" get count with this query in the same date for ex 3rd oct comment and like both diff diff object store than want also get count so it's possible? i have look that many link so there saying it's not possibl – Edit Oct 11 '17 at 06:45
i have set like this condition "$lte": 1507702039000, "$gte": 1507012285558 here now using this condtion i want reamining date also in the o/p so it's possible? – Edit Oct 11 '17 at 08:02
I'm afraid it's not possible. You can only get what is matched. – s7vr Oct 11 '17 at 08:40
ohh mean can't generate new dates right? juts generate matched dates with the object – Edit Oct 11 '17 at 08:42
and i want also count like count = 1 for date 6th oct so how can do? mean { _id: '06-10-2017', countcomment: 2,countlike : 1 } – Edit Oct 11 '17 at 08:45
in my object one object in action is = "like" on date 6th oct so i want count also this in the my o/p so how can do that?here i want work with mutlipale action – Edit Oct 11 '17 at 08:47
Just include $in operator in match stage with both actions. Something like Actions: {$in:["comment", "like"]} – s7vr Oct 11 '17 at 08:49
"count": { "$sum": 1 } but here need some changes? then where is show like count? right now i am just show comment count – Edit Oct 11 '17 at 08:52
Change in both match stages – s7vr Oct 11 '17 at 08:53
mean one for comment and onther for like? – Edit Oct 11 '17 at 08:53
https://stackoverflow.com/questions/46681304/how-to-work-with-multiple-action-and-get-count-using-mongodb this is my new post please check this one – Edit Oct 11 '17 at 08:56
Something like ` { "$match": { "_id": ObjectId("59db7a4d4f40c83c7bcaba0d"), "History.datetime": { "$gte": millis }, Action: {$in:["comment", "like"]}}}` – s7vr Oct 11 '17 at 08:56
have you look that? – Edit Oct 11 '17 at 08:57
Hello i am getting one issue using npm package xlsx for generate excel file from array. but i am not getting data in file so do you have any idea how can fix it This is my post https://stackoverflow.com/questions/47349195/how-to-generate-excel-file-using-node-js?noredirect=1#comment81651393_47349195 – Edit Nov 17 '17 at 12:25

how to get count date wise using group by from the array object in mongodb?

1 Answers1

Linked