How can convert string to date with mongo aggregation?

Question

In a collection, I store this kind of document

{
    "_id" : 1,
    "created_at" : "2016/01/01 12:10:10",
    ...
}.
{
    "_id" : 2,
    "created_at" : "2016/01/04 12:10:10",
    ...
}

I would like to find documents have "creared_at" > 2016/01/01 by using aggregation pipeline.

Anybody have solution to convert "created_at" to date so can conpare in aggregation?

Answer 1

All the above answers use cursors but however, mongodb always recommend to use aggregation pipeline. With the new $dateFromString in mongodb 3.6, its pretty much simple. https://docs.mongodb.com/manual/reference/operator/aggregation/dateFromString/

db.collection.aggregate([
    {$project:{ created_at:{$dateFromString:{dateString:'$created_at'}}}}
])

Answer 2

As you have mentioned, you need to first change your schema so that the created_at field holds date objects as opposed to string as is the current situation, then you can query your collection either using the find() method or the aggregation framework. The former would be the most simple approach.

To convert created_at to date field, you would need to iterate the cursor returned by the find() method using the forEach() method, within the loop convert the created_at field to a Date object and then update the field using the $set operator.

Take advantage of using the Bulk API for bulk updates which offer better performance as you will be sending the operations to the server in batches of say 1000 which gives you a better performance as you are not sending every request to the server, just once in every 1000 requests.

The following demonstrates this approach, the first example uses the Bulk API available in MongoDB versions >= 2.6 and < 3.2. It updates all the documents in the collection by changing the created_at fields to date fields:

var bulk = db.collection.initializeUnorderedBulkOp(),
    counter = 0;

db.collection.find({"created_at": {"$exists": true, "$type": 2 }}).forEach(function (doc) {
    var newDate = new Date(doc.created_at);
    bulk.find({ "_id": doc._id }).updateOne({ 
        "$set": { "created_at": newDate}
    });

    counter++;
    if (counter % 1000 == 0) {
        bulk.execute(); // Execute per 1000 operations and re-initialize every 1000 update statements
        bulk = db.collection.initializeUnorderedBulkOp();
    }
})
// Clean up remaining operations in queue
if (counter % 1000 != 0) { bulk.execute(); }

The next example applies to the new MongoDB version 3.2 which has since deprecated the Bulk API and provided a newer set of apis using bulkWrite():

var cursor = db.collection.find({"created_at": {"$exists": true, "$type": 2 }}),
    bulkOps = [];

cursor.forEach(function (doc) { 
    var newDate = new Date(doc.created_at);
    bulkOps.push(         
        { 
            "updateOne": { 
                "filter": { "_id": doc._id } ,              
                "update": { "$set": { "created_at": newDate } } 
            }         
        }           
    );   

    if (bulkOps.length === 1000) {
        db.collection.bulkWrite(bulkOps);
        bulkOps = [];
    }
});         

if (bulkOps.length > 0) { db.collection.bulkWrite(bulkOps); }

Once the schema modification is complete, you can then query your collection for the date:

var dt = new Date("2016/01/01");
db.collection.find({ "created_at": { "$gt": dt } });

And should you wish to query using the aggregation framework, run the following pipeline to get the desired result. It uses the $match operator, which is similar to the find() method:

var dt = new Date("2016/01/01");
db.collection.aggregate([
    {
        "$match": { "created_at": { "$gt": dt } }
    }
])