Mongodb exception can't convert from BSON type EOO to Date

Question

I am trying to group my mongodb query based on year, month, day.

My data is stored in database as below :

{
"installAt" : "2016-08-01T10:24:38.502Z",
"success" : true
"app" : "web"
}

Query using which I am trying to achieve above :

db.sampleCollection.aggregate(
       [
          {
            $group : {
               _id : { month: { $month: "$installAt" }, day: { $dayOfMonth: "$installAt" }, year: { $year: "$installAt" } },
               count: { $sum: 1 }
            }
          }
       ]
    )

But I am getting this error :

 assert: command failed: {
        "errmsg" : "exception: can't convert from BSON type EOO to Date",
        "code" : 16006,
        "ok" : 0
} : aggregate failed
Error: command failed: {
        "errmsg" : "exception: can't convert from BSON type EOO to Date",
        "code" : 16006,
        "ok" : 0
} : aggregate failed
    at Error (<anonymous>)
    at doassert (src/mongo/shell/assert.js:11:14)
    at Function.assert.commandWorked (src/mongo/shell/assert.js:244:5)
    at DBCollection.aggregate (src/mongo/shell/collection.js:1149:12)
    at (shell):1:23
2016-12-07T19:48:25.541+0530 Error: command failed: {
        "errmsg" : "exception: can't convert from BSON type EOO to Date",
        "code" : 16006,
        "ok" : 0
} : aggregate failed at src/mongo/shell/assert.js:13

One reason of this exception may be(I am not sure) because

"installAt" : "2016-08-01T10:24:38.502Z"

is not in mongodb date format but String.

If this is the problem, then one one way of solving this is updating these fields to ISODate format e.g

ISODate("2016-08-01T10:24:38.502Z")

but due to certain reason I can't not modify my database at this time.

Is their any way I can achieve grouping using above code or alternative code, (without updating database)

Answer 1

You can easily acheive this by using $substr to group the year and day of month.

db.sampleCollection.aggregate({
    $group: {
        _id: {
            "year": {
                "$substr": ["$installAt", 0, 4]
            },
            "day": {
                "$substr": ["$installAt", 5, 2]
            }
        },
        count: {
            $sum: 1
        }
    }
})

Answer 2

You can do this by the following way, but this is not the only way

varcursor=db.sampleCollection.find({"installAt": { "$exists": true }});
while(cursor.hasNext()){
  vardoc=cursor.next();
  db.tempcollection.insert({ "_id": doc._id, "installAt": newISODate(doc.installAt), "success": doc.success, "app": doc.web })
};

db.tempCollection.aggregate([
  {
    $group: {
      _id: {
        month: {
          $month: "$installAt"
        },
        day: {
          $dayOfMonth: "$installAt"
        },
        year: {
          $year: "$installAt"
        }
      },
      count: {
        $sum: 1
      }
    }
  }
])

In this approach we are creating a new collection "tempcollection", since the requirement is to not modify/update the existing collection. We can drop this tempcollection once we are done with our operations.

Answer 3

Yes that is the problem. Your date is string.

i inserted a doc like

db.date1.insert(
{ "installAt" : new ISODate("2016-08-01T10:24:38.502Z"), 
"success" : true ,"app" : "web" })

and ran your query

db.date1.aggregate(
       [
          {
            $group : {
               _id : { month: { $month: "$installAt" }, day: { $dayOfMonth: "$installAt" }, year: { $year: "$installAt" } },
               count: { $sum: 1 }
            }
          }
       ]
    )

returns me

{ "_id" : { "month" : 8, "day" : 1, "year" : 2016 }, "count" : 1 }

Now if you are not keen on recreating your collection and convert installAt as date what you can do is you can change the field type using the $type operator.

a sample on how to do that is

var cursor = db.date1.find(); 
while (cursor.hasNext()) { 
  var doc = cursor.next(); 
  db.date1.update({_id : doc._id}, {$set : {installAt: new ISODate(doc.installAt) }});
}