MongoDB - Query all documents createdAt within last hours, and group by minute?

Question

From reading various articles out there, I believe this should be possible, but I'm not sure where exactly to start.

This is what I'm trying to do:

I want to run a query, where it finds all documents createAt within the last hour, and groups all of them by minute, and since each document has a tweet value, like 5, 6, or 19, add them up for each one of those minutes and provides a sum.

Here's a sample of the collection:

{
  "createdAt": { "$date": 1385064947832 },
  "updatedAt": null,
  "tweets": 47,
  "id": "06E72EBD-D6F4-42B6-B79B-DB700CCD4E3F",
  "_id": "06E72EBD-D6F4-42B6-B79B-DB700CCD4E3F"
}

Is this possible to do in mongodb?

@zero323 - I first tried just grouping the last hour like so:

db.tweetdatas.group( {
   key: { tweets: 1, 'createdAt': 1 },
   cond: { createdAt: { $gt: new Date("2013-11-20T19:44:58.435Z"), $lt: new Date("2013-11-20T20:44:58.435Z") } },
   reduce: function ( curr, result ) { },
   initial: { }
} )

But that just returns all the tweets within the timeframe, which technically is what I want, but now I want to group them all by each minute, and add up the sum of tweets for each minute.

@almypal

Here is the query that I'm using, based off your suggestion:

db.tweetdatas.aggregate(
    {$match:{ "createdAt":{$gt: "2013-11-22T14:59:18.748Z"}, }},
    {$project: { "createdAt":1, "createdAt_Minutes": { $minute : "$createdAt" }, "tweets":1, }},
    {$group:{ "_id":"$createdAt_Minutes", "sum_tweets":{$sum:"$tweets"} }}
)

However, it's displaying this response:

{ "result" : [ ], "ok" : 1 }

Update: The response from @almypal is working. Apparently, putting in the date like I have in the above example does not work. While I'm running this query from Node, in the shell, I thought it would be easier to convert the var date to a string, and use that in the shell.

Answer 1

Use aggregation as below:

  var lastHour = new Date();
  lastHour.setHours(lastHour.getHours()-1);

  db.tweetdatas.aggregate(
    {$match:{ "createdAt":{$gt: lastHour}, }},
    {$project: { "createdAt":1, "createdAt_Minutes": { $minute : "$createdAt" }, "tweets":1, }},
    {$group:{ "_id":"$createdAt_Minutes", "sum_tweets":{$sum:"$tweets"} }}
  )    

and the result would be like this

 {
    "result" : [
    {
        "_id" : 1,
        "sum_tweets" : 117
    },
    {
        "_id" : 2,
        "sum_tweets" : 40
    },
    {
        "_id" : 3,
        "sum_tweets" : 73
    }
],
"ok" : 1
 }

where _id corresponds to the specific minute and sum_tweets is the total number of tweets in that minute.