As the title says, the field I need to modify is nested like this:

basicData.owners.relatedJson.basicData.devices.equipmentID

which owners and devices are both lists.

The object looks like this:

{
  "basicData": {
    "owners": [
      {
        "relatedJson": {
          "basicData": {
            "devices": [
              {
                "equipmentID": "abcd",
                "type": "camera"
              }
            ],
            "otherFieldsBasicData": "other values",
            "evenMoreFieldsBasicData": "other values"
          },
          "otherFieldsRelatedJson": "other values",
          "evenMoreFieldsRelatedJson": "other values"
        }
      }
    ]
  }
}

I want to rename equipmentID to equipmentId.

I've also asked this question, and I've been trying to create a query using that as a starting point, but with no success.

I was able to build a query that could get as far down as the devices list, but. then I wanted to call $set on that list, and I get an error because you can't call set inside $mergeObjects.

I thought there was some way I could use $[] to help iterate through the first array, but I can't get it to work. I think this approach is wrong.
This is what I've ended up with, which doesn't work:

db.myCollection.updateMany({"basicData.owners.relatedJson.basicData.devices.equipmentID": {$exists: true}},
        [
            {
                $set: {
                    "basicData.owners$[].relatedJson.basicData.devices": {
                        $map: {
                            input: "$basicData.owners$[].relatedJson.basicData.devices", in: {
                                $mergeObjects: ["$$this",
                                    {equipmentId: "$$this.equipmentID",}]
                            }
                        }
                    }
                }
            },
            {
                $unset: "basicData.owners.relatedJson.basicData.devices.equipmentID"
            }
    
        ])

Any advice would be greatly appreciated.

CodePudding user response：

Think you need two $map (with nested) operators.

First $map to iterate basicData.owners array while another $map is used to iterate relatedJson.basicData.devices array.

db.collection.updateMany({
  "basicData.owners.relatedJson.basicData.devices.equipmentID": {
    $exists: true
  }
},
[
  {
    $set: {
      "basicData.owners": {
        $map: {
          input: "$basicData.owners",
          in: {
            $mergeObjects: [
              "$$this",
              {
                "relatedJson": {
                  $mergeObjects: [
                    "$$this.relatedJson",
                    {
                      "basicData": {
                        $mergeObjects: [
                          "$$this.relatedJson.basicData",
                          {
                            "devices": {
                              $map: {
                                input: "$$this.relatedJson.basicData.devices",
                                in: {
                                  $mergeObjects: [
                                    "$$this",
                                    {
                                      equipmentId: "$$this.equipmentID",
                                      
                                    }
                                  ]
                                }
                              }
                            }
                          }
                        ]
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $unset: "basicData.owners.relatedJson.basicData.devices.equipmentID"
  }
])

Demo @ Mongo Playground