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-26T09:42:52.510

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 '22 at 09:42

answered Sep 26 '22 at 07:20

Yong Shun

35,286
4
24
46

1

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 Sep 26 '22 at 08:27
1

It's because the query should be `updateMany` – mal Sep 26 '22 at 08:30
Yes, will be `updateMany`, sorry for typo error. – Yong Shun Sep 26 '22 at 08:31
OK, thank you for this, I now have another issue, which I didn't specify in my original question, so I'll mark your answer as correct. But not all `basicData` objects have a `devices` list, so I need to exclude those from the query, otherwise the query above will add a `devices` field to `basicData`. I know in an ideal world these objects would be well specified, but unfortunately that's not the case here. – mal Sep 26 '22 at 08:40
I had to remove the correct mark I'm afraid, while this does update `equipmentID` it also deletes all other fields in the `relatedJson` and `basicData` objects. I'll clarify my example in the question – mal Sep 26 '22 at 09:09
1

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. – mal Sep 26 '22 at 09:40
1

Done update the answer, you need `$mergeObjects` operator. – Yong Shun Sep 26 '22 at 09:43

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

1 Answers1