Here is the collection:

db.employees.insertMany([
   {
    "data": {
      "category": [
        {
          "name": "HELLO",
          "subcategory": [
            "EDUCATION",
            "ART",
            
          ]
        },
         {
          "name": "HELLO",
          "subcategory": [
            "GG",
            "ART",
            
          ]
        },
        {
          "name": "HELLO",
          "subcategory": [
            "EDUCATION",
            "SHORE",
            
          ]
        }
      ]
    }
  },
  {
    "data": {
      "category": [
        {
          "name": "HELLO",
          "subcategory": [
            "EDUCATION",
            "HELLO",
            
          ]
        }
      ]
    }
  },
  {
    "data": {
      "category": [
        {
          "name": "HELLO",
          "subcategory": [
            "GG",
            "ART",
            
          ]
        }
      ]
    }
  }
]);

What I want is to locate the elements in 'category' with a 'subcategory' that contains 'EDUCATION' and replace 'EDUCATION' with another string, let's say 'SPORTS'.

I tried a couple of commands but nothing really did the job:

db.employees.updateMany({
  "data.category.subcategory": "EDUCATION"
},
{
  "$set": {
    "data.category.$": {
      "subcategory": "SPORTS"
    }
  }
})

What I saw is that it doesn't update the element by replacing it and it doesn't replace every element that meets the criteria.

CodePudding user response：

Think that MongoDB Update with Aggregation Pipeline fulfills your scenario.

1. $set - Set data.category value.

   1.1. $map - Iterate each element in data.caetgory and return an array.

   1.1.1. $mergeObjects - Merge current document with the document with subcategory field from 1.1.1.1.

   1.1.1.1 $map - Iterate each value from subcategory array. With $cond to replace the word EDUCATION with SPORTS if fulfill, else use existing value ($$this).

db.employees.updateMany({
  "data.category.subcategory": "EDUCATION"
},
[
  {
    "$set": {
      "data.category": {
        $map: {
          input: "$data.category",
          in: {
            $mergeObjects: [
              "$$this",
              {
                subcategory: {
                  $map: {
                    input: "$$this.subcategory",
                    in: {
                      $cond: {
                        if: {
                          $eq: [
                            "$$this",
                            "EDUCATION"
                          ]
                        },
                        then: "SPORTS",
                        else: "$$this"
                      }
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
]

Sample Mongo Playground