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