MongoDB - Update value with sub-string in embedded array element

Question

Here is my data schema:

{
    _id: ...,
    videos: [
       {
        "url" : <some url>,
        "converted" : true,
         ...
         "resolutions": [
             {
               "file_url": <some_url>,
               "details": {...}
             },
             {
               "file_url": <some_url>,
               "details": {...}
             },
             {
               "file_url": <some_url>,
               "details": {...}
             }
            }, 
            {video_2}, 
            {video_N}
         ]
            
       }

    ]
}

Each element has an array of videos with a nested array of resolutions. I need to update each resolutions.file_url and replace the old domain with the new one.

So I need "old_url.com/" to be replaced with "new_url.com/".

Here is the query I made to do it:

var oldUrl = "old_url.com/";
var newUrl = "new_url.com/";


db.getCollection('projects').update(
    {
        "videos.resolutions.file_url": {$regex: oldUrl},
        "videos.converted": true
    },
    {
        "$set": {
            "videos.$[elem].resolutions.$.file_url": ????
        }
    },
    {
        "arrayFilters": [{"elem.resolutions.file_url": {$regex: oldUrl}}],
        "multi": true
    }
)

It works fine with some static data, I can update all props with "hello world", but not sure how to make this string replace correct based on the current videos.$[elem].resolutions.$.file_url value.

Answer 1

I don't think it can be done in a simple update query especially if you want to replace a substring in the field.

Works with (update) aggregation query, you need to:

1. $map - Iterate the videos array. Overwrite the matched element for the resolutions array.

2. $map - Iterate the resolutions array, and update the file_url field of the document by replacing the matched substring ($replaceOne or $replaceAll) if the value fulfills the regex ($regexMatch). Otherwise, remains as the original value.

db.getCollection('projects').update({
  "videos.resolutions.file_url": {
    $regex: oldUrl
    
  },
  "videos.converted": true
},
[
  {
    $set: {
      videos: {
        $map: {
          input: "$videos",
          as: "video",
          in: {
            $cond: {
              if: {
                $eq: [
                  "$video.converted",
                  true
                ]
              },
              then: {
                $mergeObjects: [
                  "$$video",
                  {
                    resolutions: {
                      $map: {
                        input: "$$video.resolutions",
                        as: "resolution",
                        in: {
                          $cond: {
                            if: {
                              $regexMatch: {
                                input: "$$resolution.file_url",
                                regex: oldUrl
                                
                              }
                            },
                            then: {
                              $mergeObjects: [
                                "$$resolution",
                                {
                                  file_url: {
                                    $replaceAll: {
                                      input: "$$resolution.file_url",
                                      find: oldUrl,
                                      replacement: newUrl
                                    }
                                  }
                                }
                              ]
                            },
                            else: "$$resolution"
                          }
                        }
                      }
                    }
                  }
                ]
              },
              else: "$$video"
            }
          }
        }
      }
    }
  }
])

Demo @ Mongo Playground