MongoDB updating the wrong subdocument in array

Question

I've recently started using MongoDB using Mongoose (from NodeJS), but now I got stuck updating a subdocument in an array. Let me show you...

I've set up my Restaurant in MongoDB like so:

_id: ObjectId("5edaaed8d8609c2c47fd6582")
name: "Some name"
tables: Array
  0: Object
    id: ObjectId("5ee277bab0df345e54614b60")
    status: "AVAILABLE"
  1: Object
    id: ObjectId("5ee277bab0df345e54614b61")
    status: "AVAILABLE"

As you can see a restaurant can have multiple tables, obviously. Now I would like to update the status of a table for which I know the _id. I also know the _id of the restaurant that has the table. But....I only want to update the status if we have the corresponding tableId and this table has the status 'AVAILABLE'.

My update statement:

const result = await Restaurant.updateOne(
  {
    _id: ObjectId("5edaaed8d8609c2c47fd6582"), 
    'tables._id': ObjectId("5ee277bab0df345e54614b61"), 
    'tables.status': 'AVAILABLE' 
  },
  { $set: { 'tables.$.status': 'CONFIRMED' } }
);

Guess what happens when I run the update-statement above? It strangely updates the FIRST table (with the wrong table._id)! However, when I remove the 'tables.status' filter from the query, it does update the right table:

const result = await Restaurant.updateOne(
  {
    _id: ObjectId("5edaaed8d8609c2c47fd6582"), 
    'tables._id': ObjectId("5ee277bab0df345e54614b61")
  },
  { $set: { 'tables.$.status': 'CONFIRMED' } }
);

Problem here is that I need the status to be 'AVAILABLE', or else it should not update! Can anybody point me in the wright direction with this?

Answer 1

according to the docs, the positional $ operator acts as a placeholder for the first element that matches the query document

so you are updating only the first array element in the document that matches your query

you should use the filtered positional operator $[identifier]

so your query will be something like that

const result = await Restaurant.updateOne(
  {
    _id: ObjectId("5edaaed8d8609c2c47fd6582"),
    'tables._id': ObjectId("5ee277bab0df345e54614b61"),
    'tables.status': 'AVAILABLE'
  },
  {
    $set: { 'tables.$[table].status': 'CONFIRMED' } // update part
  },
  { 
    arrayFilters: [{ "table._id": ObjectId("5ee277bab0df345e54614b61"), 'table.status': 'AVAILABLE' }] // options part
  }
);

by this way, you're updating the table element that has that tableId and status

hope it helps