Mongo make migration to set value based on value of a subdocument in array

Working with Data
1

I have this document structure:

_id: "xx"
statuses: [{
  status: "pending",
  timestamp: "1 january 10 pm",
}, {
  status: "accepted",
  timestamp: "2 january 2 am",
}]

now i would like to add the timestamp of accepted in the root structure.

_id: "xx"
statuses: [{
  status: "pending",
  timestamp: "1 january 10 pm",
}, {
  status: "accepted",
  timestamp: "2 january 2 am",
}]
last_accepted_at: "2 january 2 am"

I know how to set value based on another root field, but not from an item of array that I have to filter.

I tried:

db.task.findOneAndUpdate( { last_accepted_at: { $exists: false } }, { $set: { "last_accepted_at": "statuses.$[element].timestamp" }}, { arrayFilters: [ { "element.status": "accepted" } ] },  )

it has error

uncaught exception: Error: findAndModifyFailed failed: {
    "ok" : 0,
    "errmsg" : "The array filter for identifier 'element' was not used in the update { $set: { last_accepted_at: \"statuses.$[element].timestamp\" } }",
    "code" : 9,
    "codeName" : "FailedToParse"
} :

I also tried:

db.task.findOneAndUpdate( { last_accepted_at: { $exists: false } }, { $set: { "last_accepted_at": "statuses.$[element].timestamp" }}, { arrayFilters: [ { "element.$.status": "accepted" } ] },  )
uncaught exception: Error: findAndModifyFailed failed: {
    "ok" : 0,
    "errmsg" : "The array filter for identifier 'element' was not used in the update { $set: { last_accepted_at: \"statuses.$[element].timestamp\" } }",
    "code" : 9,
    "codeName" : "FailedToParse"
} :

How to achieve this? also I would like to use updateMany since its a migration file to update all old data. Thanks

2

Hello @Ariel_Ariel, you can try this update using Aggregation Pipeline. This can be used with updateMany also. Note that this requires MongoDB v4.2 or greater.

db.collection.updateOne(
  { _id: "xx", last_accepted_at: { $exists: false } },
  [
    {
      $set: {
          status_accepted: { 
              $arrayElemAt: [ {
                  $filter: {
                      input: "$statuses",
                      cond: { $eq: [ "accepted", "$$this.status" ] }
                  }
              }, 0 ]
          }
      } 
    },
    { 
      $set: { 
          last_accepted_at: "$status_accepted.timestamp",  
          status_accepted: "$$REMOVE" 
      } 
    }
  ]
)
1 Like
3

This is slightly different way of doing the same update:

db.collection.updateOne(
  { _id: "xx", last_accepted_at: { $exists: false } },
  [
    {
      $set: {
          last_accepted_at: {
              $let: {
                  vars: {
                       status_accepted: { 
                           $arrayElemAt: [ {
                               $filter: {
                                   input: "$statuses",
                                   cond: { $eq: [ "accepted", "$$this.status" ] }
                               }
                           }, 0 ]
                       }
                  },
                  in: "$$status_accepted.timestamp"
              }
          }
       } 
    },
  ]
)