How to update date string in array to date format in mongoDB?

user_7768287 0
Viewed 1164

My mongoDB collection looks like this:

[
  {
    "id": "myid",
    "field": {
      "total": 1,
      "subfield": [
        {
          "time": "2020-08-06T08:33:57.977+0530"
    
        },
        {
          "time": "2020-05-08T04:13:27.977+0530"
          
        }
      ]
    }
  },
  {
    "id": "myid2",
    "field": {
      "total": 1,
      "subfield": [
        {
          "time": "2020-07-31T10:15:50.184+0530"
        }
      ]
    }
  }
]

I need to update all the documents and convert date string in the field time available in the subfieldarray to mongoDB ISO date format.

I have thousands of documents and hundreds of objects in subfield array

I'm aware of the aggregate function $todate and $convert. But I don't want to use aggregation because,

  1. To use $todate or $convert, I need to unwind the field.subfield array which is again an expensive operation.

  2. I want to update my document and save it with the date format.

My MongoDB server version: 4.0.3

I tried the following but it doesn't seem to work and also doesn't return any errors.

db.collection.find().forEach(function(doc) { 
doc.field.subfield.time=new ISODate(doc.field.subfield.time);
db.collection.save(doc); 
})
isodate mongodb-query mongodb date javascript
1 Answers

You missed a loop for subfield, because its an array,

db.collection.find().forEach(function(doc) { 
    doc.field.subfield.forEach(function(r) {
        r.time = new ISODate(r.time);
    })
    db.collection.save(doc); 
})

If this is for one time then time does not matter, i think both will take same time if you do with aggregation or forEach.

If you are planing to update MongoDb version then form 4.2, a option you can update with updateMany() using update with aggregation pipeline,

db.collection.updateMany({},
    [{
        $set: {
            "field.subfield": {
                $map: {
                    input: "$field.subfield",
                    as: "r",
                    in: {
                        $mergeObjects: [
                            "$$r",
                            { time: { $toDate: "$$r.time" } }
                        ]
                    }
                }
            }
        }
    }]
)
user_89871280
Related