I have the following sample of data:

{
  _id: 1,
  seniorityDate: '2001-01-01T00:00:00Z',
  assigned: [
    {
      groupId: 11,
      system: 'Dep',
      effectiveDate: null
    },
    {
      groupId: 12,
      system: 'Team',
      effectiveDate: null
    },
    ...
  ]
}

and I would like to update the object effectiveDate based on seniorityDate in the array of assigned where system:'Team' only:

db.collection.updateMany({}, 
[{
    $set: {
        'assigned.$[elem].effectiveDate': '$seniorityDate'
    }
}], {
    arrayFilters: [{
        "elem.system": "Team"
    }]
})

but I got the following error:

arrayFilters may not be specified for pipeline-syle updates

The expected result will be:

{
  _id: 1,
  seniorityDate: '2001-01-01T00:00:00Z',
  assigned: [
    {
      groupId: 11,
      system: 'Dep',
      effectiveDate: null
    },
    {
      groupId: 12,
      system: 'Team',
      effectiveDate: '2001-01-01T00:00:00Z'
    },
    ...
  ]
}

How can I achieve it?

CodePudding user response：

You can't use the arrayFilters with the aggregation pipeline at the same time. While you are updating the value from another field, hence you can only achieve with aggregation pipeline.

1. $set - Set assigned field.

   1.1. $map - Iterate element in assigned array and return new array.

   1.1.1. $mergeObjects - Merge current iterated document with the document from 1.1.1.1.

   1.1.1.1. Document with effectiveDate field. With the $cond operator, if matches the condition, use the seniorityDate value, else remain the existing value.

db.collection.updateMany({},
[
  {
    $set: {
      "assigned": {
        $map: {
          input: "$assigned",
          in: {
            $mergeObjects: [
              "$$this",
              {
                effectiveDate: {
                  $cond: {
                    if: {
                      $eq: [
                        "$$this.system",
                        "Team"
                      ]
                    },
                    then: "$seniorityDate",
                    else: "$$this.effectiveDate"
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Thanks to @rickhg12hs' suggestion, always limit the document for better performance, as you know which document/field should be updated by condition.

Hence your update query with query condition will be as below:

db.collection.updateMany({
  "assigned.system": "Team"
},
[
  ...
])

Demo @ Mongo Playground