How to convert string to date with mango aggregation?

Question

In the collection I keep 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 that were "creared_at"> 2016/01/01 using the aggregation pipeline.

Does anyone have a solution for converting "created_at" today, so can it be combined into an aggregation?

Answer 1

As you already mentioned, you first need to change the schema so that the created_at field contains date objects, not a string, like the current situation, then you can query your collection using either find() or an aggregation structure. The first would be the easiest approach.

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

Take advantage of the Bulk API for bulk updates that offer better performance since you will send operations to the server in batches of 1000, which gives you better performance since you do not send every request to the server only once every 1000 requests.

The following example demonstrates this approach; the first example uses the Bulk API, available in versions MongoDB >= 2.6 and < 3.2 . It updates all 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 following example applies to the new version of MongoDB 3.2 , which has since deprecated from the Mass API and provided a new apis suite 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(bulkUpdateOps); bulkOps = []; } }); if (bulkOps.length > 0) { db.collection.bulkWrite(bulkOps); } 

Once the schema modification is complete, you can request your collection for a date:

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

And if you want to request the use of the aggregation structure, run the next 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 } } } ]) 

Answer 2

If we have documents:

 db.doc.save({ "_id" : 1, "created_at" : "2016/01/01 12:10:10" }) db.doc.save({ "_id" : 2, "created_at" : "2016/01/04 12:10:10" }) 

A simple request:

 db.doc.find({ "created_at" : {"$lte": Date()} }) 

Aggregate request:

 db.doc.aggregate([{ "$match": { "created_at": { "$lte": Date() } } }]) 

• The Date () method, which returns the current date as a string.
• A new Date () constructor that returns a Date object using the ISODate () wrapper.
• ISODate (), which returns a Date object using the ISODate () wrapper method.

Additional date type information here and here.