[MongoDB] Solution for exception: can't convert from BSON type EOO to Date
I encountered an “exception: can’t convert from BSON type EOO to Date” error when applying $year and $month to a Date field with no values in MongoDB aggregate, and I’ll introduce the solution.
MongoDB Data Prerequisites
Let’s assume there’s a users collection with the following data stored in MongoDB.
// document with createdAt field
db.users.save( { name: 'hoge', createdAt: ISODate('2017-01-01') })
db.users.save( { name: 'fuga', createdAt: ISODate('2017-02-02') })
db.users.save( { name: 'mogu', createdAt: ISODate('2017-02-20') })
// document without createdAt field
db.users.save( { name: 'spam })
Solution for can't convert from BSON type EOO to Date
Query that causes error
An error occurs when applying $year and $month operators even though no value is stored in the createdAt field.
db.users.aggregate({
$project: {
createdAt: 1
}
}, {
$group: {
_id: {
year: {
$year: "$createdAt"
},
month: {
$month: "$createdAt"
}
},
count: {
$sum: 1
}
}
});
// Execution result below
assert: command failed: {
"ok" : 0,
"errmsg" : "can't convert from BSON type EOO to Date",
"code" : 16006
} : aggregate failed
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:290:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1312:5
@(shell):1:1
2018-02-02T23:04:34.794+0900 E QUERY [thread1] Error: command failed: {
"ok" : 0,
"errmsg" : "can't convert from BSON type EOO to Date",
"code" : 16006
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:290:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1312:5
@(shell):1:1
Query that doesn't cause error
Use $type: 9 to check that createdAt is a Date Type. For simpler comparisons, you can also use $ne: null or $exists: true.
When using operators like $year and $month that expect Date values, you should specify conditions with $match.
db.users.aggregate({
$match: {
createdAt: {
$type: 9 // Can also use $ne: null or $exists: true simply
}
}
}, {
$project: {
createdAt: 1
}
}, {
$group: {
_id: {
year: {
$year: "$createdAt"
},
month: {
$month: "$createdAt"
}
},
count: {
$sum: 1
}
}
});
// Execution result below
{ "_id" : { "year" : 2017, "month" : 2 }, "count" : 2 }
{ "_id" : { "year" : 2017, "month" : 1 }, "count" : 1 }
That’s all from the Gemba where I encountered an error with MongoDB’s aggregate method.