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

Question

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,

To use $todate or $convert, I need to unwind the field.subfield array which is again an expensive operation.
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); 
})

Thanks for pointing out. But it is only available from version 4.2. Mine is 4.0 @D.SM — Tiya Jose
– Tiya Jose, Commented Aug 13, 2020 at 5:56
If you have thousands of documents, it should be no problem. In case of millions of documents you may concern — Wernfried Domscheit
– Wernfried Domscheit, Commented Aug 13, 2020 at 7:05

turivishal · Accepted Answer · 2020-11-02 09:51:49Z

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" } }
                        ]
                    }
                }
            }
        }
    }]
)

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related