Update string to Date object in mongodb

Question

I'm working on mongodb and node.js and i came a across a scenario.

I'm bulk inserting data around 200 million record. In those there are some fields that are saved as string but actually are date.

Is there any way where I can do something like

db.collection.update({}, {$set: {dateField: new Date(dateField)}}, { multi: true })

Where date is in YYYY-MM-DD format

Answer 1

With a Modern MongoDB from version 4.2 or greater, you can actually do this in a single statement:

db.collection.updateMany(
  {},
  [{ "$set": { "dateField": { "$toDate": "$dateField" } }}]
);

This relies on a new feature implemented at that version which allows "aggregation expressions" to be used within a statement which can indeed access the existing values of a document and use them to produce new output.

In this case the $set is used to "merge" new content with the existing document and the $toDate handles the transformation.

---

Old Answer

There is currently no feature in MongoDB that allows you to access the content of another field in an update operation. The only thing that you can do in any language for is iterate the results:

db.collection.find({}).forEach(function(doc) { 
    db.collection.update(
        { "_id": doc._id }, 
        {"$set": { "dateField": new Date(doc.dateField) }}
    );
});

Now, you could run that on the server using db.eval() but beware as there are many dangers to this as documented, and it is likely to have a large impact on a production system.

Failing that your other options are based on getting your update to run as close as possible (in network terms) to the database.

Also on your last writings it appears that your dates were not all in the format you mentioned here, but some entries had only one digit in the month or day. So you will have to deal with that as well if you have not already.