How to replace specific fields in mongodb but return the rest-CodePudding

I am not sure the best way to design this. I have a collection where I want to reinsert into a new collection but I want to keep the structure exactly the same minus a few fields.

{
_id: "123",
date: "1900-01-01T11:00:00.0000000",
name: "joe",
birthday: "1999-01-01"
}

This could contain other fields that I do not know about - but I know for a fact I want to convert all dates to be ISO Date type - so I only want to be specific to only 4-5 fields out of the possible 20 fields.

Example of final:

{
_id: "123",
date: ISO_Date("1900-01-01T11:00:00.0000000"),
name: "joe",
birthday: ISO_Date("1999-01-01T11:00:00.0000000")
}

I was thinking I can create a few new fields with the add option like so:

db.collection.aggregate([
//add
{
    $addFields: {
       convertDate: ISO_date(date) ,
       convertBirthdate: ISO_date(date)
     }
},
//
{
    //stuck here
    //select all
    //replace the date strings with the new 
 $project : 
    { 
      //select * but avoid writing each field out as I could miss a few
      *, 
      //replace date with addfields
      date : $convertDate,  
      //replace birthday
      birthday: $convertBirthdate
    } 
}
])

Please let me know if this is possible or another more efficient way to do it.

CodePudding user response：

Your direction is correct. You can simply reuse the same field names in $addFields. For inserting into another new collection, you may try to use $merge or simply $out.

db.collection.aggregate([
  {
    "$addFields": {
      "date": {
        "$toDate": "$date"
      },
      "birthday": {
        "$toDate": "$birthday"
      }
    }
  },
  {
    "$merge": {
      "into": "collection2",
      "on": "_id",
      "whenMatched": "merge",
      "whenNotMatched": "insert"
    }
  }
])

Here is the Mongo playground for your reference.

CodePudding user response：

Here's a one-stage variation that walks the documents attempting to convert any string it finds to a date and if it cannot, it sets it back to the original value and type. The $merge or $out would be the same as other answers here. This is not a nuanced solution but if you know your datestrings are good and nothing else smells like a datestring, then it could be useful.

db.foo.aggregate([
    {$replaceRoot: {newRoot: {$arrayToObject: {$map: {
        input: {$objectToArray: "$$CURRENT"},
        as:    "z",
        in:    {k:"$$z.k",
                v:{$cond:[{$ne:["string",{$type:"$$z.v"}]},"$$z.v",
                      {$convert: {input: "$$z.v", to: "date", one rror: "$$z.v"}}]}
               }
        }} }
    }}
]);

Here is the solution expanded with comments to explain what is going on.

db.foo.aggregate([
    // Turn the object into a k-v array named X:
    {$project: {X: {$objectToArray: "$$CURRENT"}}}

    //  Use map to walk the X array.  For each k-v encountered, if the
    //  type of v ($$z.v) is NOT a string, set v = $$z.v (itself), else
    //  use the $convert function to try to make it a date.  If THAT fails,
    //  then convert will use the old $$z.v value.
    ,{$addFields: {Z: {$map: {
        input: "$X",
        as:    "z",
            in:    {k:"$$z.k",
                v:{$cond:{if: {$ne:["string",{$type:"$$z.v"}]},
                          then: "$$z.v",
                          else: {$convert: {input: "$$z.v", to: "date", one rror: "$$z.v"}} }}
                   }
                 }}
    }}

    // Turn Z from a k-v array back into an object and "lift" it into
    // the root document.
    ,{$replaceRoot: {newRoot: {$arrayToObject: "$Z"}}}
]);