MongoDB: upsert sub document array

Question

I have the following 'SaleOrderCol' collection:

{
  _id: ObjectId('1000'),
  products: [
              { _id: ObjectId('1001'), name: 'ProdA', qty: 5},
              { _id: ObjectId('1002'), name: 'ProdB', qty: 10}
            ]
},
{
  _id: ObjectId('2000'),
  products: [
              { _id: ObjectId('2001'), name: 'ProdA', qty: 5},
              { _id: ObjectId('2002'), name: 'ProdC', qty: 10}
            ]
}

I want to do an upsert to change name and qty of subdocument (1002) and try below:

SaleOrderCol.updateOne(
    { 
      "_id": ObjectId('1000'),
      "products._id": ObjectId('1002')
    },
    {
      $set : { "products": { name: 'ProdBB', qty: 15 }
    },
    { upsert: true }
)

It throws error. How to get it to work? Thank you

Answer 1

Use the $ positional operator

For example:

updateOne({ 
      "_id": ObjectId('1000'),
      "products._id": ObjectId('1002')
    },{
       $set: {"products.$.name": 'ProdBB' } // include other fields here
   });
);