$elemMatch with update in aggregation

dar84 · 2023-07-22T19:24:00.396Z

Hi,

I have a findOneAndUpdate() query composed by:

findOneAndUpdate(
    {_id: id, movies: {$elemMatch: {_id: movieID}}}, // there is an index **_id_movies_id**
    { $set: {
          'movies.$.status': 'Available'
       }
    }
)

This query works perfectly, but now I’m trying to rewrite this query using updates aggregation.

findOneAndUpdate(
    { _id: id, movies: {$elemMatch: {_id: movieID}}},
    [
       { 
         $set: {
            'movies.$.status': 'Available' // << this fails
         }
       },
      // other operations where the value depends on the field in the collection
      {
          $set: {
               isDone: { $eq: [ '$total', '$available' ]}
         }
      }
  ]
)

It fails because I can’t reference “movies.$.status”.

Is there a way for using “$elemMatch”?
I can use $map with $eq and $merge to replace “elemMatch”, but will it be more CPU intensive considering that I can’t rely on the index anymore in that case?

Thanks.
d.

Aasawari · 2023-07-25T07:07:55.446Z

Hi @dar84 and welcome to MongoDB community forums!!

In order to understand the requirement in more details, it would be helpful if you could share a few details of the deployment.

A sample data on which the the above queries are being operated.
What is the objective of using the second query ?
Based on the sample document shared, how would you want the output of the query to look like?
The mongoDB version you are one?

However, please note that, findOneAndUpdate updates the first matching value for the given condition and will not perform any update if there are no matching documents.

Regards
Aasawari

dar84 · 2023-07-25T10:00:48.400Z

Hi,
thanks for your reply.

The query fails and I’m looking for a way to fix it.

Regarding the dataset you asked, I thought it was already clear:
{_id: ObjectID, movies: [{status: String}], isDone: Number}

What part of my question is not clear?

Aasawari · 2023-07-26T08:14:52.312Z

Hi @dar84

I am looking for a sample document which would help me reproduce query in my local system and help you with the correct or updated query for the result you are looking for.

As MongoDB is a document-oriented flexible schema database, the document’s structure and datatypes cannot be determined beforehand. This is why we need some example document to be able to reproduce what you’re seeing.

Thanks
Aasawari

John_Sewell · 2023-07-26T09:28:23.903Z

Have you looked at arrayFilter?

/Edit
Checking docs and SO seems this may not work with comparing two fields together, in those cases other options are used.

Be interesting if anyone can come up with an example of using an arrayFilter to compare two elements within an array object to each other if it’s possible…

steevej · 2023-07-26T12:53:38.516Z

About

Aasawari:

A sample data on which the the above queries are being operated.

and

dar84:

Regarding the dataset you asked, I thought it was already clear:
{_id: ObjectID, movies: [{status: String}], isDone: Number}

It is customary to ask for sample documents. While a schema is clear, it forces us to create our own documents to help you solve your problem. This is extra work that we won’t need to do when you provide sample documents.

dar84:

I can’t rely on the index anymore in that case?

The query parts is the same in both case so the index will be used to find the document. No matter how you do the update the resource intensive part is to find the document and then write back the document after the update. But if you find $map to intensive, you can avoid it by using a combination of $concatArrays and $slice using $indexOfArray to find where to $slice.

dar84 · 2023-07-26T13:17:43.717Z

Hi John,
ArrayFilters doesn’t work because it still uses $position as my problem statement suggest, thanks anyway.

dar84 · 2023-07-26T13:19:56.696Z

Thanks Steeve, I’m fine with a workaround and I ended up with a workaround at the moment.

My question is just and only focused to check if it expected for MongoDB to not work in that scenario.
In that case I will open a ticket on MongoDB JIRA.

dar84 · 2023-07-26T18:51:55.367Z

No matter how you do the update the resource intensive part is to find the document and then write back the document after the update

I think this might be incorrect.

With an index on the “movies._id”, the search will be faster. So it’s not the same of using $filter+$map
$map and $filter is much slower than "movies.$.status: ‘Available’. Reason: MongoDB uses in-place updates for “movies.$.status”, so it will NOT write the whole array back on the disk but just the element that got affected by the update.

My questions is:
Why does MongoDB in-place update stop working with MongoDB update aggregate?
To me that seems a bug.

steevej · 2023-07-26T19:25:21.580Z

dar84:

Reason: MongoDB uses in-place updates for “movies.$.status”, so it will NOT write the whole array back on the disk but just the element that got affected by the update.

I do not think that this is correct. What I understand is that when any part of a document is modified, the whole document is written back to disk.

steevej · 2023-07-26T19:26:30.678Z

dar84:

With an index on the “movies._id”, the search will be faster.

The index is used to locate the document. The same index can be used what ever you do to update the document.

dar84 · 2023-07-28T07:32:35.361Z

This is not how WiredTiger is supposed to work, according to the documentation this is called in-place updates. It might be a good question for a MongoDB engineer, but I’m pretty sure it only write the element got changed in the array. NOT the whole array.

steevej · 2023-07-29T20:21:50.315Z

if my memory serves me well is that in place updates was something MMAP could do.

The little I know about wiredTiger is that data is written compressed on disk. I do not know a compression algorithm that can compress effectively a simple value and replace it in a compressed block. So I think the whole document, non just the array, is written back. something like copy-on-write is implemented.

@MaBeuLux88_xxx, @Jason_Tran, @chris, @wan, can someone point us to some documentation to settle this out.

chris · 2023-07-30T18:18:00.379Z

Hi @steevej

WiredTiger will perform a copy on write.

My notes say it is covered in MongoDB Training OC700. I don’t have any other reference.

MVCC controls

Keeps old versions of the document until no longer required.
New documents will be written instead of updating them.