MongoDB - Modify the field name in a nested list of lists

Question

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.

Yong Shun · Accepted Answer · 2022-09-26 09:42:52Z

1

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

edited Sep 26, 2022 at 9:42

answered Sep 26, 2022 at 7:20

Yong Shun

54.5k6 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

mal Over a year ago

Thank you for this, OK, this didn't quite work. I have 35 fields that need to be modified, running this query update 1. It updated it correctly, but it ignored all the others for some reason.

mal Over a year ago

It's because the query should be updateMany

Yong Shun Over a year ago

Yes, will be updateMany, sorry for typo error.

mal Over a year ago

So the issue here is because this stuff is so nested, I need use $mergeObjects with every nested object (I think). So in your solution because you're only merging with owners all the nested objects we refer to get overwritten with the new object which just contains the path down to devices.

Yong Shun Over a year ago

Done update the answer, you need $mergeObjects operator.

|

Collectives™ on Stack Overflow

MongoDB - Modify the field name in a nested list of lists

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related