MongoDB - Update in second level array

Question

I have the following structure:

 document: {
 'data': {
 'field_1': 'blabla',
 'field_2': 'blabla++',
 'interesting_field': [
 {
 'f_1': 'abc',
 'this_is_interesting': [
 {
 'a1': 'text',
 'a2': 'other_text'
 },
 {
 'a1': 'text 2',
 'a2': 'text 3'
 }
 ]
 },
 'etc': 'etc'
 ]
 },
 'somthing_boring': 'bla'
 }

What I need to do is update all 'a2' from 'this_is_interesting' from 'interesting_field' fields (if they match a certain criteria).

To update in a level 1 array, I can do something like:

{
 '_id': new ObjectId('5621020d78946ac20a000021'), 
 'data.interesting_field' : {
 $elemMatch : {
 'f_1' : 'abc'
 }
 }
},
{$set: {'data.interesting_field.$.something_to_update': 'something'}}

But... seems I can't use match for level 2+ arrays. Any ideas how to do this? (was thinking of a mapReduce... but don't think I can use it in an update query).

• Sede: Your document is invalid. Is this_is_interesting array of subdocuments?

Answer 1

You would normally do this using the positional operator $, as you noted.

Unfortunately, right now the positional operator only supports one level deep of matching for arrays.

There is a JIRA ticket for the sort of behavior that you want: https://jira.mongodb.org/browse/SERVER-831

If you want to be able to change matched elements in this_is_interesting, you have to change the structure of your data - either flatten it, or split it into two collections.

EDIT: Actually, you can use MapReduce for updating, as described in this answer: https://stackoverflow.com/a/33172255/236660

But it would be really, really inefficient. I suggest that you restructure your data instead.

• zozo: I know... but I was thinking of another solution... something like... finding the subdocument I need, use $pull to remove it (pull accepts one more condition, so I expect it to work ?? then push the modified version of it). Basically... 2 queries in 1. Unfortunately I don't manage to make it work. Note: Obvious, subdocument order is not important to me. Or... as I said in question, use map reduce. Note 2: Is an old db, restructuring data would imply refactoring the code and atm is kind of out of the question.
• Dmytro Shevchenko: @zozo, I added a link to an answer for a similar question: stackoverflow.com/a/33172255/236660 — hopefully that helps you
• zozo: Is kind of what I had in mind :). If I manage to make it work should be ok. I'll accept the answer in a few hours - maybe a simpler solution will present itself, if not... is ok.

Answer 2

Well let say you want to update all documents in your collection where a2: 'other_text' and set a2 value to new_other. With the actual structure of your document you will need to loop through each document in your collection then for each element in the interesting_field array then for each element in the this_is_interesting array check if value of a2 match given criteria in which case you update the corresponding sub-document using the so called "dot notation" and "bulk" operations for maximum efficiency. You must build your query dynamically for it to work.

var bulkOp = db.collection.initializeOrderedBulkOp();
var count = 0;
db.collection.find({ "document.data.interesting_field.this_is_interesting.a2": 'other_text' }).forEach(function(doc) { 
 var interesting_field = doc.document.data.interesting_field; 
 var size = interesting_field.length; 
 for(var ind=0; ind < size; ind++) { 
 var this_is_interesting = doc.document.data.interesting_field[ind]['this_is_interesting']; 
 for(var ix=0; ix < this_is_interesting.length; ix++) { 
 if(this_is_interesting[ix]['a2'] === 'other_text') { 
 var updateDocument = {};
 updateDocument['document.data.interesting_field.' + ind + '.this_is_interesting.' + ix + '.a2'] = 'new_other'; 
 bulkOp.find({'_id': doc._id}).update({
 '$set': updateDocument 
 }); 
 count++; 
 } 
 } 
 if(count % 200 === 0) { 
 // Execute per 200 operations and re-init 
 bulkOp.execute(); 
 bulkOp = db.collection.initializeOrderedBulkOp(); 
 } 
 } 
})
// Clean up queues
if(count > 0) { 
 bulkOp.execute(); 
}

But the best thing to do is change your documents structure

Answer 3

You can use the positional filter with array filters, available since MongoDB 3.6.

For instance, to update a2 to "something" where a1 == "text 2", you can use:

db.yourCollection.update(
{
 '_id': new ObjectId('5621020d78946ac20a000021'), 
 'data.interesting_field' : {
 $elemMatch : {
 'f_1' : 'abc'
 }
 }
},
{$set: {'data.interesting_field.$.this_is_interesting.$[elem].a2': 'something'}},
{ arrayFilters: [ {'elem.a1': 'text 2' ] })