Update $pull on arrays takes too long

Question

Hi, I am running below mongo pull query. My collection has about 550k records.  
Performance is really slow when I run this query against AWS document DB (most of the time it takes more than 60 seconds)whereas it's really quick against Mongo DB.  
  
Collection:  
{   
   "_id" : 1,  
  "authors": {  "id": "john", "name": "john" }, { "id": "mike", "name": "mike"}   
}  
authors is array.  
  
Query to remove author :  
  
db.collection.updateMany(  
{ "authors" : { "$elemMatch" : { "id" : "john"}}},  
{ $pull : { "authors" : { "id" : "john" }}}  
);  
  
We are using AWS document DB version 3.6.   
Please let me know how can I improve the performance.  
  
Edited by: AWSpriyanka1 on Nov 29, 2021 9:29 AM

Mihai A · Answer

Amazon DocumentDB does not currently support the ability to use indexes with $elemMatch - reference.

The workaround for your case would be to add an index on {"authors.id":1} and change the update query to:

db.collection.updateMany(
{ "authors.id" : "john"},
{ $pull : { "authors" : { "id" : "john" }}}
);

The workaround for your case would be to add an index on {"authors.id":1} and change the update query to:

```
db.collection.updateMany(
{ "authors.id" : "john"},
{ $pull : { "authors" : { "id" : "john" }}}
);
```

Update $pull on arrays takes too long

Relevant content