MongoDB: how to group by date with mongo date aggr

Question

I have been struggling writing to convert a mongo shell command to java query on top of the java mongo driver in order to call it in my webapp project: the command is as follow:

db.post.aggregate(
{
 $match: { dateCreated:
                {
                  "$gt": new ISODate("2013-08-09T05:51:15.000Z"),
                  "$lt": new ISODate("2013-08-09T05:51:20.000Z")
                }
       }
 },
 { 
  $group: {
    _id: {
        hour: {$hour: "$dateCreated"},
        minute: {$minute: "$dateCreated"},
        second: {$second: "$dateCreated"}   
    },
    cnt: {$sum : 1}
   }
 }
)

The query above outputs result in the format below in the mongo shell:

{
"result" : [
    {
        "_id" : {
            "hour" : 5,
            "minute" : 51,
            "second" : 19
        },
        "cnt" : 26
    },
    {
        "_id" : {
            "hour" : 5,
            "minute" : 51,
            "second" : 18
        },
        "cnt" : 29
    },
    {
        "_id" : {
            "hour" : 5,
            "minute" : 51,
            "second" : 17
        },
        "cnt" : 27
    },
    {
        "_id" : {
            "hour" : 5,
            "minute" : 51,
            "second" : 16
        },
        "cnt" : 25
    },
    {
        "_id" : {
            "hour" : 5,
            "minute" : 51,
            "second" : 15
        },
        "cnt" : 16
    }
],
"ok" : 1
 }

I failed in writing the same query in java using java mongo driver . below is my query:

           DBObject matchStart = new BasicDBObject("$match",new BasicDBObject("dateCreated",new BasicDBObject("$gt",startTime).append("$lt",endTime)));
        DBObject field = new BasicDBObject("dateCreated",1);
        field.put("h", new BasicDBObject("$hour","$dateCreated"));
        field.put("m", new BasicDBObject("$minute","$dateCreated"));
        field.put("s", new BasicDBObject("$second","$dateCreated"));

        DBObject project = new BasicDBObject("$project",field);
        DBObject groupField = new BasicDBObject("_id","$s");


        groupField.put("count", new BasicDBObject("$sum",1));
        DBObject group = new BasicDBObject("$group",groupField);

        AggregationOutput output = mongoOperations.getCollection("post").aggregate(matchStart,project,group);

        return output;

it returns a resultset below :

{"result" : [ 
    { "_id" : 19 , "count" : 26} , 
    { "_id" : 18 , "count" : 29} , 
    { "_id" : 17 , "count" : 27} , 
    { "_id" : 16 , "count" : 25} , 
    { "_id" : 15 , "count" : 16}
          ] , 
"ok" : 1.0}

I am having challenges making the query include the minute part and the hour part. How can tweak my query to output the same resultset as in the mongo shell one.

Thanks for looking at that

Answer 1

Java code for given query is as follows :

DBCollection coll = ...

Date startDate = ...
Date endDate = ...

DBObject dateQuery = new BasicDBObject();
dateQuery.put("$gt", startDate);
dateQuery.put("$lt", endDate);

DBObject match = new BasicDBObject();
match.put("dateCreated", dateQuery);

DBObject id = new BasicDBObject();
id.put("hour", new BasicDBObject("$hour", "$dateCreated"));
id.put("minute", new BasicDBObject("$minute", "$dateCreated"));
id.put("second", new BasicDBObject("$second", "$dateCreated"));

DBObject group = new BasicDBObject();
group.put("_id", id);
group.put("cnt", new BasicDBObject("$sum", 1));

AggregationOutput output = coll.aggregate(new BasicDBObject("$match", match), new BasicDBObject("$group", group));

if (output != null) {
    for (DBObject result : output.results()) {
        Integer count = (Integer) result.get("cnt");

        DBObject idObj = (DBObject) result.get("_id");
        Integer hour = (Integer) idObj.get("hour");
        Integer minute = (Integer) idObj.get("minute");
        Integer second = (Integer) idObj.get("second");
    }
}