MongoDB change optional sub-field inside array from integer to list

Question

My mongo db collection contains documents having array and each of the array elements have sub fields as below:

Sample document:

{
  "_id" : ObjectId("63fcb6654732c97e476e642d"),
  "name" : "test1",
    "appliedRules" : [
    {
      "id" : "rul1",
      "type" : "RULE_TYPE_1"
    },
    {
      "id" : "rul1",
      "type" : "RULE_TYPE_2"
    },
    {
      "id" : "rul1",
      "type" : "RULE_TYPE_2",
      "mId" : NumberInt(10006),
    }
    ]
}

If doc.appliedRules.type is RULE_TYPE_2 then it may have optional field "mId" which is integer.

If "mid" field is present inside array field then I need to change it to Array/List for existing documents in the collection.

I tried few options and shared below one of the way close to solution but that is also not working. How this can be resolved in mongo db 3.6.0?

Note: Mongo DB Server is AWS DocumentDB version 3.6.0

Below one looks close to what I need, but mongo shell is giving error for this too.

MongoServerError: Wrong type for parameter u

db.reports_new_test.updateMany(
   { $and: [
      { 'appliedRules.type': "RULE_TYPE_2" }, 
      { 'appliedRules.mid': { $exists: true } }
   ] },
   [
      {
         $set: {
            'appliedRules': {
               $map: {
                  input: '$appliedRules',
                  as: 'rule',
                  in: {
                     $mergeObjects: [
                        '$$rule',
                        {
                           mid: {
                              $cond: {
                                 if: { $isArray: "$$rule.mid" },
                                 then: "$$rule.mid",
                                 else: [ "$$rule.mid" ]
                              }
                           }
                        }
                     ]
                  }
               }
            }
         }
      }
   ]
);

how can this be resolved?

Answer 1

You are trying to update using an aggregation pipeline, which is not currently supported with Amazon DocumentDB. With MongoDB is possible since version 4.2. What I would do is probably use the aggregation framework to create a cursor matching your condition and then iterate over the documents using $set to update them.