Mongodb 数据库中某 collection（名为 meetings）数据如下：不同的层级上都有 avatar 这个字段，共 3 种：organizer.avatar；meetings[].owner.avatar（这表示 meetings 下是数组，有多个 owner.avatar）；meetings[].participants[].avatar（数组下有数组）。

[
  {
    "_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"
    }
  }
]

要求：把所有 avatar 字段值的开头，从https://old.com/… 改成 https://new.com/…

期望结果：

[
  {
    "_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"
    }
  }
]

mongodb query 写出来层级太多，很费劲，如下

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 支持点操作符逐级访问，可以把多层结构的数据修改写得很简洁易读:

	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)

前三行是读数，第四行修改数据，第五行把结果转回 json 串

问题来源：https://stackoverflow.com/questions/78476290/replace-substring-in-array-of-objects-with-nested-objects