MongoDB - How to find and update elements in a nested array

Question

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.

Answer 1

Think that MongoDB Update with Aggregation Pipeline fulfills your scenario.

1. $set - Set data.category value.

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

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

   1.1.1.1 $map - Iterate each value from the subcategory array. With $cond to replace the word EDUCATION with SPORTS if fulfilled, 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

Answer 2

Here's another way to do it using "arrayFilters".

db.collection.update({
  "data.category.subcategory": "EDUCATION"
},
{
  "$set": {
    "data.category.$[].subcategory.$[elem]": "SPORTS"
  }
},
{
  "arrayFilters": [
    { "elem": "EDUCATION" }
  ],
  "multi": true
})

Try it on mongoplayground.net.