How to update a field of array's element using aggregate pipeline

Question

I'm using MongoDB v4.2.13. I have a collection Transaction with documents like this:

{
    "id": 1,
    "product": [
        {
            "name": "Book",
            "price": 100
        },
        {
            "name": "Pen",
            "price": 10
        }
    ]
}

Now I want to add a new field paid into the first element of product, which equal it's price.

{
    "name": "Book",
    "price": 100,
    "paid": 100
}

I try to use updating with aggregate pipeline $addFields/$set, but result is not as expected.

Query (I only want to use one query):

db.Transaction.updateOne(
    { id: 1 },
    [
        {
            $addFields: {
                "product.0.paid": {
                    $arrayElemAt: ["$product.price", 0]
                }
            }
        }
    ]
)

Expected result:

{
    "id": 1,
    "product": [
        {
            "name": "Book",
            "price": 100,
            "paid": 100
        },
        {
            "name": "Pen",
            "price": 10
        }
    ]
}

Actual result:

{
    "id": 1,
    "product": [
        {
            "name": "Book",
            "price": 100,
            "0" : {
                "paid" : 100
            }
        },
        {
            "name": "Pen",
            "price": 10,
            "0" : {
                "paid" : 100
            }
        }
    ]
}

How to get the expected result?

so you want to update product named Book? how to identify which element — cmgchess, Apr 02 '23 at 10:55
maybe https://stackoverflow.com/questions/67787485/update-mongodb-array-field-value-using-value-of-another-field helps — cmgchess, Apr 02 '23 at 11:22

score 1 · Accepted Answer · answered Apr 02 '23 at 19:48

Here is another solution , when you dont know in advance the value of price , but you need to update only the first array element with product.0.paid:"$product.0.price"

 db.collection.update({},
 [
 {
  $set: {
  p1: {
    $arrayElemAt: [
      "$product",
       0
     ]
    }
   }
  },
  {
    "$set": {
      "p1.paid": "$p1.price"
    }
  },
 {
  $project: {
    product: {
      $concatArrays: [
      [
        "$p1"
      ],
      {
        "$slice": [
          "$product",
          1,
          {
            $size: "$product"
          }
        ]
      }
    ]
  }
 }
}
])

Explained:

Get 1st element from product array and assign to p1 temporary variable.
Set the paid value in p1 equal to price value from p1.
Concatenate product array removing old 1st element with with the newly formed p1 object. ( projecting only product you remove the p1 temporary variable)

Playground

R2D2 · Answer 2 · 2023-04-02T17:46:08.190

0

The easiest is to use update/arrayFilters , you dont need to use aggregate for this task:

db.collection.update({"product.0.price":100},
{
 $set: {
   "product.$[x].paid": 100
 }
 },
 {
  arrayFilters: [
  {
    "x.price": 100
  }
 ]
})

Playground(arrayFilters)

Here is option with aggregate:

db.collection.update({
  "product.0.price": 100
},
[
{
"$addFields": {
  "product": {
    "$map": {
      "input": "$product",
      "as": "p",
      "in": {
        "$mergeObjects": [
          "$$p",
          {
            "$cond": {
              "if": {
                "$eq": [
                  "$$p.price",
                  100
                ]
              },
              "then": {
                "paid": "$$p.price"
              },
              "else": {}
            }
          }
        ]
      }
    }
    }
   }
  }
 ])

Playground2(aggregate)

If you need to update only 1st element in array it is even simpler:

  db.collection.update({
    "product.0.price": 100
  },
  {
   $set: {
   "product.0.paid": 100
   }
 })

Playground(simple positional update)

edited Apr 02 '23 at 17:46

answered Apr 02 '23 at 11:15

R2D2

9,410
2
12
28

maybe he doesnt want to hardcode the 100. don't kwo for sure – cmgchess Apr 02 '23 at 11:16
@cmgchess : "which equal it's price" -> I guess this is the expected condition , but this can be variable in arrayFilters and in aggregation if needed ... – R2D2 Apr 02 '23 at 11:19
1

@R2D2 Yep, i mean i would add new field `paid` with value equals `price`'s value to the first element of `product`. I modified my query, it should be: `$arrayElemAt: ["$product.price", 0]` – Tan Nguyen Apr 02 '23 at 15:33
check 3th playground in my answer , maybe something like this? – R2D2 Apr 02 '23 at 17:47

How to update a field of array's element using aggregate pipeline

2 Answers2