mongodb exception: can’t convert from BSON type String to Date

Problem: When attempting to do an aggregation on timestamps which are stored as strings, mongodb is unable to do the conversion

repmongo:SECONDARY> db.collection.aggregate(
…     { $match : { "type" : "Review"}},
…     { $group : {
…         _id: {
…             year : { $year : "$created" },
…             month : { $month : "$created" },
…             day : { $dayOfMonth : "$created" },
…         },
…         count: { $sum: 1 }
…     }},
…     { $sort : { _id : 1}}
… );
assert: command failed: {
        "errmsg" : "exception: can’t convert from BSON type String to Date",
        "code" : 16006,
        "ok" : 0
} : aggregate failed

To see which fields are of which type:

repmongo:SECONDARY> typeof db.collection.findOne().created;
string
repmongo:SECONDARY> typeof db.collection.findOne().updated;
string

Solution 1: The best thing to do is to resolve the data type inconsistency at the application layer so that data is entered into the database in ISO format which can then be easily worked with. This would require the existing data set to be changed to [date] data type.

Solution 2: For immediate analysis of the data use regex and substring to extract the date portions.

db.collection.aggregate(
    { $match : { "created" : {$in : [/2014-10/]}}},
    { $group : {
        _id: {
            year :  { $substr : ["$created", 0, 4 ] },   
            month : { $substr : ["$created", 5, 2 ] },                                      
            day :   { $substr : ["$created", 8, 2 ] },        
        },
        count: { $sum: 1 }
    }
              },
    { $sort : { _id : -1}}
);

The above returns data grouped by Year, Month & Day with a sum. The Regex conditions acts as a like clause on the date string.

{ "_id" : { "year" : "2014", "month" : "10", "day" : "21" }, "count" : 1 }

{ "_id" : { "year" : "2014", "month" : "10", "day" : "06" }, "count" : 1 }

If doing in SQL Language, the above would probably look something like this:

SELECT 
TO_DATE(created, 'YYYY'), 
TO_DATE(created, 'MM'), 
TO_DATE(created, 'DD'), 
count(_id)
FROM collection
WHERE created like '%2014-10%'
GROUP BY 
TO_DATE(created, 'YYYY'), 
TO_DATE(created, 'MM'), 
TO_DATE(created, 'DD') 
ORDER BY _id DESC
Advertisements