How to delete an element from an array that is nested inside of another array using MongoDB

Question

The Problem

Suppose I have a document as follows:

doc = {
"_id" : ObjectId("56464c726879571b1dcbac79"),
"food" : {
    "fruit" : [
        "apple",
        "orange"
    ]
},
"items" : [
    {
        "item_id" : 750,
        "locations" : [
            {
                "store#" : 13,
                "num_employees" : 138
            },
            {
                "store#" : 49,
                "num_employees" : 343
            }
        ]
    },
    {
        "item_id" : 650,
        "locations" : [
            {
                "store#" : 12,
                "num_employees" : 22
            },
            {
                "store#" : 15,
                "num_employees" : 52
            }
        ]
    }
]
}

I would like to delete the element

    {'#store#' : 12, 'num_employees' : 22} 

but only if the following conditions are true:

• food.fruit contain the values apple or orange
• item_id has the id 650

My Attempted Solution

I tried the following:

     db.test.update({"food.fruit" : {"$in" : ["apple", "orange"]}, "items.item_id":650},{$pull:{'items.$.locations':{'store#':12,'num_employees':22}}})

The update does not work. Interestingly, if the $in operator part of the query is removed, it works. I'm using MongoDB v3.0.6 and consulted the MongoDB manual for the use of $(update):

https://docs.mongodb.org/manual/reference/operator/update/positional/

The docs contain a passage of interest:

  Nested Arrays
  The positional $ operator cannot be used for queries which traverse more than one array, such as queries that traverse arrays nested within other arrays, because the replacement for the $ placeholder is a single value

My query, of course, traverses more than one array. Indeed, if I remove 'food.fruit' : {$in : ['apple']} from the query, it works. However, this does not solve my problem, because of course, I need that query. I'm looking for a solution that preferably:

• Does NOT require a schema change
• Executes in one query/update statement

Answer 1

If you need to match more than one possible value in "food.fruit" and therefore other more than one possible document ( the only case where this makes sense ) then you can always replace your $in with JavScript logic in $where:

db.test.update(
    {
        "items.item_id": 650,
        "$where": function() {
            return this.food.fruit.some(function(el) {
                return ["apple","orange"].indexOf(el) != -1;
            });
        }
    },
    { "$pull": { "items.$.locations": { "store#": 12 } } },
    { "multi": true }
)

Which essentially applies the same test, though not as efficiently as "food.fruit" values cannot be tested in an index, but hopefully the other field of "items.item_id" is a sufficient match at least to not make this a real problem.

On the other hand, testing this against a MongoDB server version 3.1.9 ( development series ), the following works without problem:

db.test.update(
    { "food.fruit": { "$in": ["orange","apple"] }, "items.item_id": 650 },
    { "$pull": { "items.$.locations": { "store#": 12 } } },
    { "multi": true }
)

I would also suggest though that if you intend to include _id in your query, then you are only matching a single document anyway, and as such you need only supply the match on the array you wish to $pull from:

db.test.update(
    { "_id": 123, "items.item_id": 650 },
    { "$pull": { "items.$.locations": { "store#": 12 } } }
)

Which is fairly simple and provides no conflict, unless you really need to be sure that the required "food.fruits" values are actually present in order to apply the update. In which case, follow the former examples.

Answer 2

Matt,

I used your sample data and the following query works:

db.pTest.update(
    {"food.fruit" : "apple","items.item_id":650},
    {$pull:{'items.$.locations':{'store#':12,'num_employees':22}}}
)

No need for the $in(unless your inputting more than one value for array) nor the $elemMatch. Also for two-level deep arrays you can use {$pull: {"someArray.$.someNestedArray": {"key": "value to delete"}}}.

What you referenced from the docs.

Nested Arrays The positional $ operator cannot be used for queries which traverse more than one array, such as queries that traverse arrays nested within other arrays, because the replacement for the $ placeholder is a single value.

Translates to, mean that you can't use the $ Positional Operator twice.

Sample Data:

{
    "_id" : ObjectId("56464c726879571b1dcbac79"),
    "food" : {
        "fruit" : [
            "apple",
            "orange"
        ]
    },
    "items" : [
        {
            "item_id" : 650,
            "locations" : [
                {
                    "store#" : 12,
                    "num_employees" : 22
                },
                {
                    "store#" : 15,
                    "num_employees" : 52
                }
            ]
        }
    ]
}

Results in:

{
    "_id" : ObjectId("56464c726879571b1dcbac79"),
    "food" : {
        "fruit" : [
            "apple",
            "orange"
        ]
    },
    "items" : [
        {
            "item_id" : 650,
            "locations" : [
                {
                    "store#" : 15,
                    "num_employees" : 52
                }
            ]
        }
    ]
}