Get count for each day in mongodb using aggregate or map reduce

Question

How do i convert the below mysql query to mongodb query: SELECT count(*) as count , DATE_FORMAT( timestamp, '%d-%c-%Y' ) as day, timestamp as tm FROM visits WHERE 1 GROUP BY day ORDER BY tm. I want to use this on a nodejs so i am using native mongodb.

Get the number of pageviews for each day in mongodb where each pageview is stored along with the timestamp.

score 7 · Accepted Answer · edited Mar 03 '20 at 15:13

7

Your question lacks any effort on your part and we rarely just "give" people the answer like this, however, this one time:

NB: you cannot yet manipulate dates to cast them to different formats without some manual work yourself of picking the parts out and rejoining them. Because of this I have left out the date formatting you did and just used it as an object.

db.visits.aggregate([
    {
        $project: {
            date: {day: {$dayOfMonth: '$timestamp'}, month: {$month: '$timestamp'}, year: {$year: '$timestamp'}},
            //day: {concat: [date.day,date.mont,date.year]}
        }
    },
    {$group: {_id: '$date', tm: '$timestamp', count: {$sum:1}}}
])

edited Mar 03 '20 at 15:13

Fiodorov Andrei

1,778
1
11
26

answered Mar 25 '13 at 09:48

Sammaye

43,242
7
104
146

Hey congrats on 10K+ reps :) – Sushant Gupta Mar 25 '13 at 10:45
2

I get the ERROR: MongoError: exception: the group aggregate field 'tm' must be defined as an expression inside an object – peter Mar 26 '13 at 05:06
@peter weird I don't get that – Sammaye Mar 26 '13 at 07:54
The below query seems to work perfectly for me: db.visits.aggregate( { $project: { day: { years: {$year: '$timestamp'}, months: {$month: '$timestamp'}, days: {$dayOfMonth: '$timestamp'}, }, }}, { $group: { _id: { day: '$day' }, count: { $sum: 1} }},{ $sort:{_id:1}}); Got the answer from [link](http://stackoverflow.com/questions/14120123/mongodb-aggregation-php-group-by-hours) – peter Mar 26 '13 at 11:58
@peter It would be better to try and put the sort at the beginning and use `$first` or `$last` to get the first or last of the sorted set otherwise the sort will be in-memory and not index based, hmm but you can't avoid that – Sammaye Mar 26 '13 at 12:04
@Sammaye I needed to get all the entries and not only the last or the first one. My above query seems to be working perfectly for my requirement, thanks anyways – peter May 10 '13 at 08:01
@peter indeed it will work, it might just be slow under some occasions :) – Sammaye May 10 '13 at 08:03

score 0 · Answer 2 · edited May 11 '16 at 10:15

I found a working mongodb query using mapreduce which gives me the output time as unix time rather than the format I had mentioned in the question. But this was the query that was sorting the time properly. I had tried mongo group query but it did not sort according to time. The working mongo query is :

db.visits.mapReduce(
function(){
  day = Date.UTC(this.timestamp.getFullYear(), this.timestamp.getMonth(), this.timestamp.getDate());

  emit({day: day}, {count: 1});
}, 
function(key, values) {
  var count = 0;

  values.forEach(function(v) {
    count += v['count'];
  });

  return {count: count};
},
             {
              out : {inline:1},
              sort:{_id:1}
             }
);

Get count for each day in mongodb using aggregate or map reduce

2 Answers2