How to bulk replace substring in mongoDB array field with keyword?

user_1613047 0
Viewed 344
{
    "_id": ObjectId("5f08e58ae1f788a8beb30519"),
    "__v": NumberInt("0"),
    "dimensions": {
        "height": NumberInt("720"),
        "width": NumberInt("1080")
    },
    "likes": NumberInt("164"),
    "src": [
        "http://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFfUrBufC7T3R48ehjSrVSQ7JbfkgFybzjLs4tNMXhX7YmuWicPe2NUiaKQ/0",
        "http://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFfkopmyeak2h1oGPkiaHcgcg4DX5swbBHRg6TWibl2ocvpgIaAng0koWMQ/0",
        "http://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFfkxTnwtOurQQkajmtlQzIokIM0Ms6qyYh2FCWaCnZqmxOAyEYZyUgdA/0",
        "http://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFflamticYWfNkheTMtzcz3wSGib01EsurUA3Royy1A1P0W8muEicMcbbegw/0",
        "http://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFfRc3IBJE1amD5BPoELdYjoIvkQ2iaHiaUu0HexWD8niaAJq6fTFIqDgibOA/0"
    ],
    "recommended": true
}

Let's say I have above data structure in mongoDB in a collection "discoveries" and I want to replace http:// in src to https:// like such:

{
    "_id": ObjectId("5f08e58ae1f788a8beb30519"),
    "__v": NumberInt("0"),
    "dimensions": {
        "height": NumberInt("720"),
        "width": NumberInt("1080")
    },
    "likes": NumberInt("164"),
    "src": [
        "https://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFfUrBufC7T3R48ehjSrVSQ7JbfkgFybzjLs4tNMXhX7YmuWicPe2NUiaKQ/0",
        "https://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFfkopmyeak2h1oGPkiaHcgcg4DX5swbBHRg6TWibl2ocvpgIaAng0koWMQ/0",
        "https://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFfkxTnwtOurQQkajmtlQzIokIM0Ms6qyYh2FCWaCnZqmxOAyEYZyUgdA/0",
        "https://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFflamticYWfNkheTMtzcz3wSGib01EsurUA3Royy1A1P0W8muEicMcbbegw/0",
        "https://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFfRc3IBJE1amD5BPoELdYjoIvkQ2iaHiaUu0HexWD8niaAJq6fTFIqDgibOA/0"
    ],
    "recommended": true
}

Is there anyway I can do this without going through each record and then update them one by one? (I have millions of records it would take massive among of time, also it would seem inefficient...)

I tried:

db.getCollection("discoveries").updateMany(
  { src: { $exists: true } },
  [{
    $set: { src: {
      $replaceOne: { input: "src.$[]", find: "http://", replacement: "https://" }
    }}
  }]
)

It returns:

[Error] Error: collection.updateOne requires update operator
mongodb
1 Answers

This solution is more of (ask specific than generic IMO) takes an assumption that all strings in src originally starts with http and are needed to replaced with https.

Starting in MongoDB 4.2, you can use the aggregation pipeline for update operations.

Idea: Is to use an aggregation pipe using $map where all urls in src splitted at 4th index forward joined with https using $concat and $substr. And update src property using $set.

db.collection.updateMany({ src: { $exists: true } }, [
  {
    $set: {
      src: {
        $map: {
          input: "$src",
          as: "u",
          in: {
            $concat: [
              "https",
              {
                $substr: ["$$u", 4, -1],
              },
            ],
          },
        },
      },
    },
  },
]);

NOTE: $split can also be used to tweak this, instead of $substr.

EDIT/UPDATE: Based on comments that some urls could already be starting with https.

db.collection.updateMany({ src: { $exists: true } }, [
  {
    $set: {
      src: {
        $map: {
          input: "$src",
          as: "u",
          in: {
            $cond: {
              if: {
                $eq: [
                  {
                    $size: {
                      $split: ["$$u", "http:"],
                    },
                  },
                  2,
                ],
              },
              then: {
                $concat: [
                  "https:",
                  {
                    $arrayElemAt: [
                      {
                        $split: ["$$u", "http:"],
                      },
                      1,
                    ],
                  },
                ],
              },
              else: "$$u",
            },
          },
        },
      },
    },
  },
]);
user_60822800
Related