MongoDB: Removing a field from ALL subdocuments in an array field

Question

I have thousands of documents in this format:

{
"_id" : ObjectId("51e98d196b01c2085c72d731"),
"messages" : [
    {
        "_id" : ObjectId("520167056b01c20bb9eee987"),
        "id" : ObjectId("520167056b01c20bb9eee987"),

    },
    {
        "_id" : ObjectId("520167056b01c20bb9eee988"),
        "id" : ObjectId("520167056b01c20bb9eee988"),

    },
    {
        "_id" : ObjectId("520167056b01c20bb9eee989"),
        "id" : ObjectId("520167056b01c20bb9eee989"),
    }
],
}

I need to remove the duplicate "id" field. This is what I have tried:

db.forum_threads.update({}, {$unset: {"messages.$.id": 1}}, {multi: true});

This is the error I am getting:

Cannot apply the positional operator without a corresponding query field containing an array.

Tenedos

Answer 1

The reason you're getting that error is because you don't have any predicate in the filter clause. You can do this:

mongos> db.test.update({"messages.id": {$exists: true}}, {$unset: {"messages.$.id":true}}, {multi:true})

And you won't get an error - in fact one of the documents will have the id attribute removed. The problem is that the positional operator only matches the FIRST element of the array that matches your predicate, it doesn't match all elements. The bigger issue is that it's not currently possible to update all the elements in an array in MongoDB (https://jira.mongodb.org/browse/SERVER-1243).

You'll either need to iterate through each element in the array using the numerical position ("messages.0.id", "messages.1.id", etc.) or you can pull the array into your application, loop through the elements and update them, and then save the array back out.

You can see from the JIRA ticket that this issue has been open for quite awhile but 10gen doesn't seem to consider it very high priority.

Walke

I had a hunch it wasn't possible. Guess I have to go the alternative route, thanks! – Tenedos

Thanks for the explanation, I was missing the ".$." in the $unset clause. It is unexpected behaviour that it only removes the attribute from the first subdoc it encounters, but this still helps in a pinch, I just ran it multiple times until I had them all removed. – Rickety

Answer 2

With Mongo 3.6 you can use the new positional identifier and do something like:

db.test.update({"messages.id": {$exists: true}}, {$unset: {"messages.$[].id":true}}, {multi:true})

Intelligible

Answer 3

This seems to have worked well for me (2023). Note that mongoDB might trip over a missing array in some documents, hence the condition.

db.test.updateMany(
  { messages: { $exists: true } },
  { $unset: { "messages.$[].id": "" } } 
)

Tails