4

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
Improve this question
5
  • The update you're trying works fine when I try it with 3.0.5. It's worth noting that you don't need to use $in here as 'food.fruit': 'apple' does the same thing, but the update works fine either way for me. Commented Nov 13, 2015 at 20:50
  • @JohnnyHK, it worked using the $in operator? I need to edit my example, because I look for more than just apple. Example: {$in : ['apple', 'orange']}. Using your suggestion, I could do 'food.fruit' : 'apple', 'food.fruit' : 'orange' which should be equivalent to using $in. Commented Nov 13, 2015 at 21:01
  • Whoops. Actually, this wouldn't work. I attempt to see if food.fruit has apple or orange. Just needs to have at least one of them. Commented Nov 13, 2015 at 21:04
  • It did work using $in, as it should, because the limitation in the docs you reference is talking about trying to use $ within a nested array (something besides the first level of the array). Commented Nov 13, 2015 at 21:07
  • Yeah, when I wrote this wouldn't work I meant the query {'food.fruit' : 'apple', 'food.fruit' : 'orange'} is not equivalent to {'$in' : ['apple', 'orange']} Commented Nov 13, 2015 at 21:12

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks Seven. I just updated my question. I only now realized the problem I was having, wasn't reproducible with the information I provided. I'll read your answer now.
@matt Since you are using the same cookie cutter response then it seems clear that you have not read anything that people actually responded with. You have not changed no detail in your question that makes any difference to the response, and nor should you. Answers are provided in the context of the question "as it is asked". If you have a different question then Ask another question. This answers the question as asked.
After inspired answered, I read his/her answer, investigated a bit more, and realized I needed to edit my question to include a bit more structure to reproduce my problem. So in Stack Overflow, am I supposed to start a new question? Or simply edit my current question. It seems editing my current question is reasonable. I'll now read yours.
@matt What is the question and how can it be new? Your data structure is either what you presented or it is not. This answer shows you how to remove your required element from the structure provided, therefore it answers that question. If your structure is actually different or the the requred removal is different, then you have a new question and should not alter this one.
0

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
                }
            ]
        }
    ]
}
Improve this answer

1 Comment

Thanks for the answer. I've updated my question which should now have the reproducible problem. Apologies. I tried to mimic the structure of the actual document I'm working with since I can't post its data on a public forum.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.