How can I group my MongoDB collection based on the creation date which I extract form objectId?

Question

I want to use group aggregation in mongoDB in order to group my documents based on their creation date. I didn't save the creation date as a field, but I know that I can extract this date from my ObjectId. So, I tried to write something like this:

db.sales.aggregate(
   [
      {
        $group : {
           _id : { month: {$month: ObjectId("$_id").getTimestamp()}, 
                   day: {$dayOfMonth: ObjectId("$_id").getTimestamp()}, 
                   year: {$year: ObjectId("$_id").getTimestamp()}
           },
           averageQuantity: { $avg: "$quantity" },
           count: { $sum: 1 }
        }
      }
   ]
)

And I get this error: Error: invalid object id: length : @(shell):5:37

It works, when I write a special ObjectId instead of "$_id". For example: ObjectId("507c7f79bcf86cd7994f6c0e").getTimestamp()

And it also works, when I have a date field in which I stored my creation dates and the write something like this:

db.sales.aggregate(
   [
      {
        $group : {
           _id : { month: {$month: "$date"}, 
                   day: {$dayOfMonth: "$date"}, 
                   year: {$year: "$date"}
           },
           averageQuantity: { $avg: "$quantity" },
           count: { $sum: 1 }
        }
      }
   ]
)

But I want to know, how can I do it directly using my ObjectId and getting timestamps.

The group by aggregation and its related data is to be find on this link: https://docs.mongodb.com/manual/reference/operator/aggregation/group/

But I replaced the simple integer id s of this example, with my automatic mongoDB generated id s.

Answer 1

getTimestamp() is a function and it should be used within a javascript function.

Two things you need to correct in the query. One is the retrieval of Id and other one is using getTimestamp() function.

Retrieval of Id

_id : { month: {$month: ObjectId("$_id").getTimestamp()}, 
                   day: {$dayOfMonth: ObjectId("$_id").getTimestamp()}, 
                   year: {$year: ObjectId("$_id").getTimestamp()}
      }

Correct way of retrieving id with month, day and year

_id : { month: "$_id.month",  day: "$_id.day", year: "$_id.year"},

Reason:

month is part of the document id and hence it has to be retrieved this way. similarly the day and year.

Getting the Timestamp

Put the result into a javascript loop and use the getTimestamp() method.

db.sales.aggregate(
   [
      {
        $group : {
           _id : { month: "$_id.month",  day: "$_id.day", year: "$_id.year"},
           averageQuantity: { $avg: "$quantity" },
           count: { $sum: 1 }
        }
      }
   ]
).forEach(function (doc) 
    {
     doc["doc._id.month"]=doc._id.month.getTimestamp();
     doc["doc._id.day"]=doc._id.day.getTimestamp();
     doc["doc._id.year"]=doc._id.year.getTimestamp();
     printjson(doc);
    });

If you have a collection like this

{
        "_id" : {
                "month" : ObjectId("57bd7d3c0da65e3f92328e50"),
                "day" : ObjectId("57bd7d3c0da65e3f92328e51"),
                "year" : ObjectId("57bd7d3c0da65e3f92328e52")
        },
        "quantity" : 200
}

and the result after executing aggregate query with javascript function would give the following result

{
        "_id" : {
                "month" : ObjectId("57bd7d3c0da65e3f92328e50"),
                "day" : ObjectId("57bd7d3c0da65e3f92328e51"),
                "year" : ObjectId("57bd7d3c0da65e3f92328e52")
        },
        "averageQuantity" : 200,
        "count" : 1,
        "doc._id.month" : ISODate("2016-08-24T10:55:56Z"),
        "doc._id.day" : ISODate("2016-08-24T10:55:56Z"),
        "doc._id.year" : ISODate("2016-08-24T10:55:56Z")
}