MongoDB update document with date field as string

Question

I have a collection of documents with 2 fields in string format like the below example

{"starts_at": "2019-12-01T18:51:56", "ends_at": "2019-12-05T18:51:56"}
{"starts_at": "2019-12-03T04:38:24", "ends_at": "2019-12-16T04:38:24"}

I want to update the "ends_at" field to be updated by 1 extra day if the difference between the "start_at" and "end_at" is greater than 7. The expected output is like second document "ends_at" get changed.

{"starts_at": "2019-12-01T18:51:56", "ends_at": "2019-12-05T18:51:56"}
{"starts_at": "2019-12-03T04:38:24", "ends_at": "2019-12-17T04:38:24"}

I tried the below approach

First I created a new field with date difference using command

db.myCollection.aggregate([{$addFields: {
DateDiff:{$trunc:{
$divide:[{
$subtract:[{$dateFromString:{dateString:"$ends_at"}},{$dateFromString:{ dateString: "$starts_at"}}]},
1000*60*60*24]}}
}}])

When I tried to update the collection I am not able to find the newly created field. For updating I use

db.myCollection.update({DateDiff:{ $gte:7}},
{$set:{"ends_at":{$add:[{$dateFromString:{dateString:"$ends_at"}},1*24*60*60*1000]}}})

How should I update the "ends_at" field as "String" data type itself by adding extra 1 day in single MongoDB command.

prasad_ prasad_ · Accepted Answer · 2019-12-05T03:13:22

I want to update the "ends_at" field to be updated by 1 extra day if the difference between the "start_at" and "end_at" is greater than 7.

The expected output is like second document "ends_at" get changed.

The following aggregate query will do the update. The date operators $dateFromString and $dateToString are used to convert the string date to a date field, do the comparison / arithmetic, and then convert back to string. Note the query works with MongoDB version 4.2.

db.test.updateMany(
  { },
  [
    { $set: { ends_at: {
                      $cond: [ { $gt: [
                                       { $subtract: [ 
                                              { $dateFromString: { dateString: "$ends_at" } }, 
                                               { $dateFromString: { dateString: "$starts_at" } } 
                                          ] 
                                        },
                                        { $multiply: [ 7, 86400000 ] }
                                   ] 
                                },
                                { $dateToString: { 
                                            date: { $add: [ { $dateFromString: { dateString: "$ends_at" } }, 86400000 ] },
                                            format: "%Y-%m-%dT%H:%M:%S" 
                                 } },
                                 "$ends_at" 
                        ]

    } } },
  ]
)

MongoDB update document with date field as string

1 Answers