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-10 12:46:07Z

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, 2017 at 12:46

answered Oct 7, 2017 at 15:49

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

Edit Over a year ago

Hello i want generate remaining date also in the o/p so how can i do that can you please know that?

Edit Over a year ago

[ { _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 Over a year ago

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?

s7vr Over a year ago

I'm afraid it's not possible. You can only get what is matched.

Edit Over a year ago

ohh mean can't generate new dates right? juts generate matched dates with the object

|

Collectives™ on Stack Overflow

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

1 Answer 1

15 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related