Exception: can't convert from BSON type EOO to Date

Question

I am getting an issue for running the following aggregate query:

db.snippets.aggregate([ { '$project': { month: { '$month': '$created_at' }} } ])

The error message for the same is:

assert: command failed: {
        "errmsg" : "exception: can't convert from BSON type EOO to Date",
        "code" : 16006,
        "ok" : 0 } : aggregate failed

How do I get around this issue? I found a related question: MongoDB: can't convert from BSON type EOO to Date.

But it doesn't tell me how to get things done.

In case someone lands here looking for the opposite, eg. all documents have milliseconds in date field, take a look at: http://stackoverflow.com/questions/29892152/convert-miliseconds-to-date-in-mongodb-aggregation-pipeline-for-group-by — Wtower, Nov 25 '16 at 11:15

JohnnyHK · Accepted Answer · 2015-06-15T16:29:17.517

68

You likely have one or more docs with a created_at value that's not a BSON Date and you'll need to fix that by converting those values to Date or removing them.

You can find those docs with a $not query that uses the $type operator like:

db.snippets.find({created_at: {$not: {$type: 9}}})

If the created_at values are date strings, you can find the docs that need updating and then update them in the shell using code like:

db.snippets.find({created_at: {$not: {$type: 9}}}).forEach(function(doc) {
    // Convert created_at to a Date 
    doc.created_at = new Date(doc.created_at);
    db.snippets.save(doc);
})

edited Jun 15 '15 at 16:29

answered Feb 09 '15 at 18:36

JohnnyHK

305,182
66
621
471

4

You made my day man. I was stuck because of this for quite some time. – jsbisht Feb 09 '15 at 18:38
@JohnnyHK Wouldn't this query just find the docs that don't have a BSON date? How can I find and update all the non-BSON dates? – user137717 May 08 '15 at 00:03
1

@user137717 extrapolate `.find(conditions)` into `.update(conditions, update)` – bloudermilk May 17 '15 at 02:45
@JohnnyHK Thank You. You made my day. – rahulroy9202 May 16 '16 at 07:56
@JohnyHK Please help me in my question: https://stackoverflow.com/q/72785793/6097074 – ankit Jun 28 '22 at 11:44

score 6 · Answer 2 · answered Jul 04 '16 at 11:32

6

try this one, its help for me above problem.

db.snippets.aggregate([{
'$project': {
    month: { $substr: ["$created_at", 5, 2] }
}
 }]);

above code get month wise

data is entered into the database in ISO format which can then be easily worked with.

answered Jul 04 '16 at 11:32

Anurag Pandey

744
1
7
14

score 4 · Answer 3 · answered Jan 23 '16 at 23:23

In some situations, some documents are supposed to have empty Date fields. In those cases, you could try this (using your example):

db.snippets.aggregate([ { '$project': { month:  
 { $cond: [{ $ifNull: ['$created_at', 0] }, { $month: '$created_at' }, -1] }} } ])

In this example, we would get -1 in the cases whenever no field '$created_at' is found. For all the other cases, we would get the Date month.

score 3 · Answer 4 · answered Jul 26 '16 at 18:02

I had a related issue, but in my case the Date fields were the members of an array, so the error was "can't convert BSON type Object to Date".

I needed to get the day of week from the dates in the possibleTripDateTimes array.

Sample document:

{
"possibleTripDateTimes" : [
    {
        "tripDateTime" : ISODate("2015-08-01T06:00:00.000-0700")
    }
]
}

The fix was simply to use dot notation to address the array member fields.

db.trips.aggregate([
  {
       $project: {
         departTime: {
           $map: {
             input: "$possibleTripDateTimes.tripDateTime",
             as: "dateTime",
             in: { $dayOfWeek: "$$dateTime" }
           }
   }
  }
}
]
);

I hope this helps someone who also gets zero search results on the "BSON type Object" search

Ahh!! This is so cool. You saved me a lot of stupid searching hours! Thanks man :) — Uzumaki Naruto, May 01 '17 at 14:14

facumedica · Answer 5 · 2018-03-12T19:16:14.347

I had a similar problem, and solved it checking if the date existed.

db.users.aggregate([
{$project:{day:  { $cond: ["$bd", { $dayOfMonth: "$bd" }, -1] },
           month:  { $cond: ["$bd", { $month: "$bd" }, -1] },
           year:  { $cond: ["$bd", { $year: "$bd" }, -1] }
           }},
{$match:{"month":1, "day":15}}
])

My date field is bd and with that match I'm getting all users that have their birthday on January 15th.

score 2 · Answer 6 · answered Sep 14 '17 at 22:06

I had the same problem, I figured that the date field is missing for some of the documents causing the conversion to fail. I just added a match clause to filter these out. But ofcourse i am investigating on my app side why they are not being populated.

db.snippets.aggregate([
  {
    '$match': {
      'created_at': {
        "$exists": true
      }
    }
  },
  {
    '$project': {
      month: {
        '$month': '$created_at'
      }
    }
  }
])

score 0 · Answer 7 · answered Dec 17 '17 at 12:15

This error can also appear if you have incorrectly named your properties in your aggregation relative to what they are in your database.

For example my code was

$group: {
        _id: {$week: "$projects.timeStamp"},
        total: { $sum: "$projects.hours"  }
    }

But I hadn't camelcased timestamp in my database so simply modifying to projects.timestamp fixed it.

score 0 · Answer 8 · answered Jul 05 '18 at 13:04

First, you need to check whether the data type is in ISODate. IF not you can change the data type as the following example.

db.collectionName.find().forEach(function(each_object_from_collection){each_object_from_collection.your_date_field=new ISODate(each_object_from_collection.your_date_field);db.collectionName.save(each_object_from_collection);})

Now you can find it in two ways

db.collectionName.find({ $expr: {$eq: [{ $year: "$your_date_field" }, 2017]}});

Or by aggregation

db.collectionName.aggregate([{$project: {field1_you_need_in_result: 1,field12_you_need_in_result: 1,your_year_variable: {$year: '$your_date_field'}, your_month_variable: {$month: '$your_date_field'}}},{$match: {your_year_variable:2017, your_month_variable: 3}}])

score 0 · Answer 9 · answered Nov 09 '21 at 11:15

First, you can identify the particular field which is causing an issue as follows:


    db.collectionName.find( { 'dateField' : { $type : 2 } } )

The above line checks and finds all the documents with field name 'dateField' having type as String(referred as $type - 2).

Once it is identified and verified, we can modify those records as follows:


    db.collectionName.find( { 'dateField' : { $type : 2 } } ).forEach( function (x) {   
      x.dateField = new ISODate(x.dateField);
      db.collectionName.save(x);
    });

score 0 · Answer 10 · answered Dec 02 '21 at 09:03

0

In my case I had to use "$toDate" and it worked:

db.snippets.aggregate([ { '$project': { month: { '$month': {$toDate: '$created_at'} }} } ])

answered Dec 02 '21 at 09:03

Buda Sergiu Flavius

210
1
3
13

Exception: can't convert from BSON type EOO to Date

10 Answers10

Linked

Related