How do I replace string type with date type in MongoDB for an object array?

Question

I know the method for converting dates for the first level

db.collection.find().forEach(function(x){ 
x.DateField = ISODate(x.DateField); 
db.collection.save(x);})

This works with first level fields, but what should I do if i want to change fields of objects in arrays such as CreatedTime in the below json sample:

{
    "Pharmacy": "b",
    "Medicine": [
      {
        "MedName": "MedB",
        "Quantity": 60,
        "CreatedTime" : "2006-05-05T11:44:47.86Z"
      },
      {
        "MedName": "MedC",
        "Quantity": 34,
        "CreatedTime" : "2006-11-23T12:28:44.86Z"
      }
    ]
}

s7vr · Accepted Answer · 2018-07-19 13:16:34Z

You can use the cursor from the aggregate query and bulk updates to update documents in 4.0 version.

Use $map with $toDate to keep the existing values and convert the string date to date type.

Here is the shell sample.

var bulk = db.collection.initializeUnorderedBulkOp();
var count = 0;
var batch = 50; // Change batch size as you need

db.collection.aggregate([
  {"$project":{
       "Medicine":{
         "$map":{
            "input":"$Medicine",
            "in":{
                "MedName":"$$this.MedName",                                  
                "Quantity":"$$this.Quantity",
                "CreatedTime":{"$toDate":"$$this.CreatedTime"}
            }
          }
       }
   }}
]).forEach(function(doc){ 
    bulk.find( {"_id" : doc._id}).updateOne(
     { "$set": {"Medicine" : doc.Medicine}} 
   ); 
    count++;  
    if (count == batch) { 
        bulk.execute(); 
        bulk = db.collection.initializeUnorderedBulkOp(); 
        count = 0;
    } 
});

if (count > 0) { 
   bulk.execute(); 
}

Hardik Shah · Accepted Answer · 2018-07-19 13:38:57Z

1

db.getCollection('test').find({}).forEach(function(doc) {
    doc.Medicine.forEach(function(doc1){
        var dbl = new Date(doc1.CreatedTime);
        doc1.CreatedTime = dbl;
    });
    db.test.save(doc);
});

EDITED: ======= Second approach: Source

db.logmessages.aggregate( [ {
    $project: {
       date: {
          $dateFromString: {
             dateString: '$date',
             timezone: 'America/New_York'
          }
       }
    }
} ] )

edited Jul 19, 2018 at 13:38

answered Jul 19, 2018 at 13:09

Hardik Shah

4,2082 gold badges22 silver badges45 bronze badges

5 Comments

Hardik Shah Over a year ago

@Acex, This is tested query.

Acex Over a year ago

Wait what was the operator that allows me to read a string as a date?

Hardik Shah Over a year ago

Sorry? Didn't get you.

Hardik Shah Over a year ago

Your CreatedTime is in String. You just need to iterate on each document, get all String date and then just assign Date to CreatedTime. and then just save it. It will change your String date to Date object

Acex Over a year ago

The second approach that you erased, is actually more helpful for me. Although it does not answer the question completely

Collectives™ on Stack Overflow

How do I replace string type with date type in MongoDB for an object array?

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related