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

Question

{
    "_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

I think this is exactly what you are looking for: https://stackoverflow.com/a/56556298/8296184 — virgiliogm, Jul 11 '20 at 07:43
@VirgilioGM there is a little bit of problem with this solution, src is an array rather than a string, so $replaceOne doesn't seem to do it... — Aero Wang, Jul 11 '20 at 07:58
I don't have my computer to try it now, sorry, but I thought it would work using 'src.$[]' as the field to be updated — virgiliogm, Jul 11 '20 at 08:46
@VirgilioGM thx, see my updated question. it returns: `collection.updateOne requires update operator` — Aero Wang, Jul 11 '20 at 15:10

ambianBeing · Answer 1 · 2020-07-12T16:37:15.923

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",
            },
          },
        },
      },
    },
  },
]);

`an assumption that all strings in src originally starts with http and are needed to replaced with https`... that's not exactly how it is in reality — Aero Wang, Jul 12 '20 at 03:50
@AeroWang Ermm.. made this query purely based on the example in the question. So u'r saying some urls in `src` may already be starting with `https`? — ambianBeing, Jul 12 '20 at 11:43
u'r saying some urls in src may already be starting with https - yes — Aero Wang, Jul 12 '20 at 15:41
@AeroWang Updated the response! the solution turned out be less than optimal but works. I don't have mongo 4.4 to test out `$replaceOne` which could greatly work here. Hope someone posts a better solution. You might wanna look at [Another way](https://stackoverflow.com/a/55466982/6082280) which I would have gone for in case of huge data. — ambianBeing, Jul 12 '20 at 16:40

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

1 Answers1