MongoDB query for values contained in array and results contain only those values

Question

Let's say I have the following DB:

pizzas = [{
  name: "pizza1",
  toppings: ['mushrooms', 'pepperoni', 'sausage']
},
{
  name: "pizza2",
  toppings: ['mushrooms', 'pepperoni']
},
{
  name: "pizza3",
  toppings: ['mushrooms', 'onions']
},
{
  name: "pizza4",
  toppings: ['mushrooms']
}]

Now I want to fetch the pizzas that have 'mushrooms', 'pepperoni', or 'onions' and any combination of those. Then the query could be:

pizzas.find({toppings: ['mushrooms', 'pepperoni', 'onions']})

This would return all four pizzas in my db. But here's the problem. What if I wanted pizzas with any combination of only those three toppings, i.e. a pizza can not contain a different topping like 'sausage'. For this query, I only want "pizza2", "pizza3", and "pizza4" to be returned. I could make a query like:

pizzas.find({$and: [{toppings: ['mushrooms', 'pepperoni', 'onions']}, {$not: {toppings: ['sausage']}}]

The problem is that this requires me to know all of the possible toppings to exclude. Is there a better way to construct this query?

So, it would be pizza's that only include the exact ingredients passed in to the search function? No more or no less ingredients? — Sean Cox
– Sean Cox, Commented Nov 19, 2015 at 3:38
Right. The results don't have to have all of the toppings passed in the search list but it must have some and it must not contain any toppings not included in the list. — ravishi
– ravishi, Commented Nov 19, 2015 at 3:55

Blakes Seven · Accepted Answer · 2015-11-19 04:11:49Z

You basically need to find the "Set Difference" between the stored array and the desired list and see if there are any items stored that are not one of the desired ingredients. Therefore if the returned list is greater than 0 it contains another ingredient in the list.

If you have at least MongoDB 2.6, there is a $setDifference operator you can use in a $redact statement:

db.pizzas.aggregate([
    { "$match": {
        "toppings": { "$in": [ "mushrooms", "pepperoni", "onions" ] }
    }},
    { "$redact": {
        "$cond": {
            "if": {
                "$eq": [
                    { "$size": {
                        "$setDifference": [
                            "$toppings",
                            [ "mushrooms", "pepperoni", "onions" ]
                        ]
                    }},
                    0
                ]
            },
            "then": "$$KEEP",
            "else": "$$PRUNE"
        }
    }}
])

If your MongoDB is older than that, then you can implement the same logic in JavaScript using $where:

db.pizzas.find({
    "toppings": { "$in": [ "mushrooms", "pepperoni", "onions" ] },
    "$where": function() {
        return this.toppings.filter(function(topping) {
            return [ "mushrooms", "pepperoni", "onions" ].indexOf(topping) == -1;
        }).length == 0;
    }
})

Both exclude "pizza1" from results by the same comparison, with the native operators in .aggregate() being faster:

{
        "_id" : ObjectId("564d44a59f28c6e0feabceea"),
        "name" : "pizza2",
        "toppings" : [
                "mushrooms",
                "pepperoni"
        ]
}
{
        "_id" : ObjectId("564d44a59f28c6e0feabceeb"),
        "name" : "pizza3",
        "toppings" : [
                "mushrooms",
                "onions"
        ]
}
{
        "_id" : ObjectId("564d44a59f28c6e0feabceec"),
        "name" : "pizza4",
        "toppings" : [
                "mushrooms"
        ]
}

Noting here that it is still wise to use $in to filter first, as it at least narrows down to possible results, and does not need a brute force match of the whole collection. You use it as opposed to a "raw array" as in your question, since your demonstrated form would match only elements with the exact array, and in order.

Thanks! Is there any way to return a DBCursor from the aggregate query? I need to pass the filtered model to a 3rd party package that is going to chain another .find() call. When I pass it the result of the aggregate query, it says it can't run .find() on it. This question might be mongoose specific.

Collectives™ on Stack Overflow

MongoDB query for values contained in array and results contain only those values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related