Update the DataType for a field inside the nested array embedded document

[Amarendra_Krishna]

Hello Everyone,

Below is my document. Here a field by name exceptionDate is available inside the tag and tagExceptions. I want to update the datatype of the exceptionDate from String to Date.

{
  "_id": {
    "$oid": "631ed2b5e54db93b2196ccc7"
  },
  "customerId": {
    "$numberLong": "10003014"
  },
  "vehicleId": {
    "$numberLong": "1006"
  },
  "isPlateNumber": false,
  "plateNo": "MH43AJ411",
  "tag": {
    "tagAccountNumber": 20000046,
    "tagExceptions": [
      {
        "reasonCode": "TAGINACTV",
        "excCode": "03",
        "exceptionDate": "2015-09-16 14:58:34.000 +05:30"
      }
    ]
  }
}

Reason for Updating: Using Zappysys tool we are migrating data from sql server to mongodb. For Datetime fields even though in sql server is date time, once after migration it is coming as string. So, we are trying to update specific field in the document to Date data type from String.

Now I am looking for a query to update the Data type in mongodb or using C# to update the Data type from String to Date.

Please let me how to fix this issue. Thank you in Advance.

[Asya_Kamsky]

Hi there,

It’s possible to do this using pipeline updates, I’ll show what it looks like in JS/mongo shell and you can translate it to C# then:

db.uptype.update(
     {"tag.tagExceptions.exceptionDate":{$type:"string"}}, 
     [{$set:{"tag.tagExceptions":{$map:{
         input:"$tag.tagExceptions", 
         in:{$cond:{
             if:{$eq:["string",{$type:"$$this.exceptionDate"}]}, 
             then:{$mergeObjects:["$$this", {exceptionDate:{$toDate:"$$this.exceptionDate"}}]}, 
             else:"$$this"
         }}
    }}}}])

Asya