The data for a collection (named meetings) in the MongoDB database is as follows: there are three types of avatar fields at different levels: organizer.avatar; meetings[].owner.avatar (this means that there is an array under meetings with multiple owner.avatars); meetings[].participants[].avatar (there is an array under the array).

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "eventName": "Welcome Event",
    "meetings": [
      {
        "owner": {
          "avatar": "https://old.com/dwight-schrute.png",
          "name": "Dwight Schrute"
        },
        "participants": [
          {
            "avatar": "https://old.com/kevin-malonoe.png",
            "name": "Kevin Malonoe"
          },
          {
            "avatar": "https://old.com/creed-bratton.png",
            "name": "Creed Bratton"
          }
        ]
      },
      {
        "owner": {
          "avatar": "https://old.com/jim-halpert.png",
          "name": "Jim Halpert"
        },
        "participants": [
          {
            "avatar": "https://old.com/pam-beesly.png",
            "name": "Pam Beesly"
          }
        ]
      }
    ],
    "organizer": {
      "avatar": "https://old.com/michale-scott.png",
      "name": "Michael Scott"
    }
  }
]

Requirement: Change the beginnings of all avatar field values from https://old.com/…. to https://new.com/….
Expected results:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "eventName": "Welcome Event",
    "meetings": [
      {
        "owner": {
          "avatar": "https://new.com/dwight-schrute.png",
          "name": "Dwight Schrute"
        },
        "participants": [
          {
            "avatar": "https://new.com/kevin-malonoe.png",
            "name": "Kevin Malonoe"
          },
          {
            "avatar": "https://new.com/creed-bratton.png",
            "name": "Creed Bratton"
          }
        ]
      },
      {
        "owner": {
          "avatar": "https://new.com/jim-halpert.png",
          "name": "Jim Halpert"
        },
        "participants": [
          {
            "avatar": "https://new.com/pam-beesly.png",
            "name": "Pam Beesly"
          }
        ]
      }
    ],
    "organizer": {
      "avatar": "https://old.com/michale-scott.png",
      "name": "Michael Scott"
    }
  }
]

Writing a MongoDB query with too many levels can be quite challenging, as shown below:

db.collection.update({},
[
  {
    "$set": {
      "meetings": {
        $map: {
          input: "$meetings",
          in: {
            $mergeObjects: [
              "$$this",
              {
                owner: {
                  $mergeObjects: [
                    "$$this.owner",
                    {
                      avatar: {
                        $replaceOne: {
                          input: "$$this.owner.avatar",
                          find: "https://old.com/",
                          replacement: "https://new.com/"
                        }
                      }
                    }
                  ]
                }
              },
              {
                participants: {
                  $map: {
                    input: "$$this.participants",
                    as: "p",
                    in: {
                      $mergeObjects: [
                        "$$p",
                        {
                          avatar: {
                            $replaceOne: {
                              input: "$$p.avatar",
                              find: "https://old.com/",
                              replacement: "https://new.com/"
                            }
                          }
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

SPL supports dot operator for hierarchical access, which can make data modification in multi-layer structures very concise and easy to read:

	A
1	=mongo_open@d("mongodb://127.0.0.1:27017/local")
2	=mongo_shell@d(A1, "{'find':'meetings','projection':{'_id':0}}")
3	=mongo_close(A1)
4	=A2.run(organizer.run(avatar="https://new.com/"+mid(avatar,17)), meetings.run(owner.run(avatar="https://new.com/"+mid(avatar,17)), participants.run(avatar="https://new.com/"+mid(avatar,17)) ) )
5	=json(A4)

The first three lines are readings, the fourth line modifies data, and the fifth line returns the result to the JSON string.

Question source：https://stackoverflow.com/questions/78476290/replace-substring-in-array-of-objects-with-nested-objects