Mongo db 4.2: replace field of an array item with the value of another field of the matched document

Question

Consider a mongo collection containing the following documents:

{ name: "Enrico", age: 32, items: [{ type: "cat", color: "blue" }, { type: "dog", color: "red" }]},
{ name: "Francesca", age: 33, items: [{ type: "foo", color: "bar" }, { type: "hello", color: "world" }]},
{ name: "Mario", age: 40, items: [{ type: "cat", color: "green" }, { type: "dog", color: "white" }]}

I need to update al the documents where the items array contains a cat, by changing the value of the color property for the matching array item. The new value of the color property must be equal to the value of the name field of the matched document.

The desired result is the following:

{ name: "Enrico", age: 32, items: [{ type: "cat", color: "Enrico" }, { type: "dog", color: "red" }]},
{ name: "Francesca", age: 33, items: [{ type: "foo", color: "bar" }, { type: "hello", color: "world" }]},
{ name: "Mario", age: 40, items: [{ type: "cat", color: "Mario" }, { type: "dog", color: "white" }]}

By using the $ update operator for arrays it's really easy to perform a similar update, by replacing the color property for the matching array item with a static value. For instance, the following update will replace the color property for the matching array item with the "changed-color" string literal:

db.people.updateMany(
    {"items.type": "cat"},
    {$set: {"items.$.color": "changed-color"}}
)

The problem here is that I would like to use the value of another field of the matched document (name) as the new value for the color property of the matched array item.

I know that since MongoDB 4.2 it is possible to use an aggregation pipeline inside of an update query, as documented here. By using the $set aggregation pipeline operator it is possible to replace a document field with another value of the same document, as documented here.

So my attempt has been to combine the $ update operator for arrays with the usage of an aggregation pipeline inside of an update query and I ended up with the following query:

db.people.updateMany(
    {"items.type": "cat"},
    [
        {$set: {"items.$.color": "$name"}}
    ]
)

Unfortunately this query doesn't work: mongo db complains that the $set stage is invalid, because the $ sign can't be used inside of a field path. It seems that the aggregation framework is not aware of the $ update operator for arrays.

Is it possible to perform the update that I want by using a query ? Should I opt for a script instead ?

turivishal turivishal · Accepted Answer · 2020-10-22T17:20:56

You can try,

$map to iterate loop of items array, check condition if type is cat then return color from $name and merge objects using $mergeObjects,

db.people.updateMany(
  { "items.type": "cat" },
  [{
    $set: {
      items: {
        $map: {
          input: "$items",
          in: {
            $mergeObjects: [
              "$$this",
              {
                $cond: [
                  { $eq: ["$$this.type", "cat"] },
                  { color: "$name" },
                  {}
                ]
              }
            ]
          }
        }
      }
    }
  }]
)

Playground

Mongo db 4.2: replace field of an array item with the value of another field of the matched document

1 Answers