Positional operator targets wrong element of array field in `findOneAndUpdate` update query

user_12009306 0
Viewed 21

I have a document

{
    "_id": "62ac8190ddb08e6ee5f2c7dd",
    "status": "NEW",
    "vendor": "62ac8171ddb08e6ee5f2c7ca",
    "productsInOrder": [
      {
        "product": "62ac8176ddb08e6ee5f2c7cd",
        "amount": 1
      },
      {
        "product": "62ac8181ddb08e6ee5f2c7d0",
        "amount": 1
      }
    ],
    "createdAt": "2022-06-17T13:28:48.815Z",
    "updatedAt": "2022-06-17T13:39:44.073Z",
    "orderNumber": 82,
    "__v": 2
  }

And a query to update second element of productsInOrder array in the document

db.collection.findOneAndUpdate({
  status: "NEW",
  vendor: "62ac8171ddb08e6ee5f2c7ca",
  "productsInOrder.product": "62ac8181ddb08e6ee5f2c7d0",
  "productsInOrder.amount": {
    $lte: 3
  },
  
},
{
  $inc: {
    "productsInOrder.$.amount": 1
  }
})

After executing the update query I expect product with id "62ac8181ddb08e6ee5f2c7d0" to be updated, i.e it's amount increased by 1

{
    "_id": "62ac8190ddb08e6ee5f2c7dd",
    "status": "NEW",
    "vendor": "62ac8171ddb08e6ee5f2c7ca",
    "productsInOrder": [
      {
        "product": "62ac8176ddb08e6ee5f2c7cd",
        "amount": 1
      },
      {
        "product": "62ac8181ddb08e6ee5f2c7d0",
        "amount": 2 // increased by 1
      }
    ],
    "createdAt": "2022-06-17T13:28:48.815Z",
    "updatedAt": "2022-06-17T13:39:44.073Z",
    "orderNumber": 82,
    "__v": 2
  }

but instead product with id "62ac8176ddb08e6ee5f2c7cd" gets updated as seen here https://mongoplayground.net/p/UEU5LCAVjD0

I don't understand why positional operator selects the first element of the array instead of the element specified in find query

How can I update array element of the same id that is specified in find query in `"productsInOrder.product"?

mongoose mongodb-query mongodb
1 Answers

Refer to the docs:

The positional $ update operator behaves ambiguously when filtering on multiple array fields.

When the server executes an update method, it first runs a query to determine which documents you want to update. If the update filters documents on multiple array fields, the subsequent call to the positional $ update operator doesn't always update the required position in the array.

Since your query specify productsInOrder.product and productsInOrder.amount, it's considered to be filtering on multiple array fields. In your case, you should use $elemMatch instead:

db.collection.update({
  status: "NEW",
  vendor: "62ac8171ddb08e6ee5f2c7ca",
  productsInOrder: {
    $elemMatch: {
      product: "62ac8181ddb08e6ee5f2c7d0",
      amount: {
        $lte: 3
      }
    }
  }
},
{
  $inc: {
    "productsInOrder.$.amount": 1
  }
})

MongoPlayground

user_117113160
Related