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

user_11263733 0
Viewed 166

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

database-migration mongodb document
1 Answers

"The array filter for identifier 'element' was not used in the update { $set: { last_accepted_at: "statuses.$[element].timestamp" } }",

The error says you can not use arrayFilters as the value of any field, you can only use it as part of the key, for more details refer $[<identifier>],

Second, the normal update query will not allow internal field as the value of another field, you need to use an update with aggregation pipeline starting from MongoDB 4.2,

  • $filter to iterate loop of statuses array and get "accepted" status result
  • $arrayElemAt to get first element from above filtered result
  • $set to get timestamp from return object from above arrayElemAt operation
db.task.updateMany({
  last_accepted_at: { $exists: false },
  "statuses.status": "accepted"
},
[
  {
    $set: {
      last_accepted_at: {
        $arrayElemAt: [
          {
            $filter: {
              input: "$statuses",
              cond: { $eq: ["$$this.status", "accepted"] }
            }
          },
          0
        ]
      }
    }
  },
  {
    $set: { last_accepted_at: "$last_accepted_at.timestamp" }
  }
])

Playground

user_89871280
Related