MongoDB - Update a field based on another field within the same doc in the array

Question

I have a collection of data like this:

{
    "items": [
      {
        "product": {
          "name": "prod1",
          "price": {
            "pledge": 1,
            "cost": 19
          }
        }
      },
      {
        "product": {
          "name": "prod2",
          "price": {
            "pledge": 2,
            "cost": 10
          }
        }
      }
    ]
  }

I want to update the whole collection have them like this:

{
    "items": [
      {
        "product": {
          "name": "prod1",
          "price": {
            "pledge": 1,
            "deposit": 1,
            "cost": 19
          }
        }
      },
      {
        "product": {
          "name": "prod2",
          "price": {
            "pledge": 2,
            "deposit": 2,
            "cost": 10
          }
        }
      }
    ]
  }

For each price in the items, I want to add a new field named deposit with the value of pledge in the same element. How can I do it in an optimized way?

Answer 1

You should need the update query with aggregation pipeline to reference the field.

1. $map - Iterate the elements in the items array

1.1. $mergeObjects - Merge the current iterated object with the document with price field.

1.1.1. $mergeObjects - Merge the product.price object with the document with deposit field referencing the product.price.pledge value.

db.collection.update({},
[
  {
    $set: {
      items: {
        $map: {
          input: "$items",
          in: {
            product: {
              $mergeObjects: [
                "$$this.product",
                {
                  "price": {
                    $mergeObjects: [
                      "$$this.product.price",
                      {
                        deposit: "$$this.product.price.pledge"
                      }
                    ]
                  }
                }
              ]
            }
          }
        }
      }
    }
  }
])

Demo @ Mongo Playground

Answer 2

You can make use of updateMany() for this.

db.collectionName.updateMany(
  {}, // Match all documents
  {
    $set: {
      "items.$[elem].product.price.deposit": "$items.$[elem].product.price.pledge"
    }
  },
  {
    arrayFilters: [{"elem.product.price.pledge": {$exists: true}}], // Filter to match all documents that have the pledge field
    multi: true // Apply to all documents
  }
)

• updateMany targets all documents in the collection ({} as the first argument).
• The $set operator is used to assign the deposit field a value equal to the pledge field within each price object of each item in the items array.
• arrayFilters specifies the conditions for the elements that the update should apply to. Here, it ensures the operation only applies to elements where the pledge exists.
• The multi: true option, although redundant in updateMany, emphasizes the intention to update multiple documents. It's more relevant in update operations.

NOTE: Replace collectionName with the right name of your collection.

Edit: Making use of map and merge:

db.collection.update({},
// Match all documents
[
  {
    $set: {
      items: {
        $map: {
          input: "$items",
          as: "item",
          in: {
            $mergeObjects: [
              "$$item",
              {
                product: {
                  $mergeObjects: [
                    "$$item.product",
                    {
                      price: {
                        $mergeObjects: [
                          "$$item.product.price",
                          {
                            deposit: "$$item.product.price.pledge"
                          }
                        ]
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      }
    }
  }
])