MongoDB querying array field with exclusion [duplicate]

Question

I searched around for a bit, but couldn't quite find the way to do this with MongoDB - or maybe I just misunderstand the $all and $in operators.

If I have the following documents:

{
  arr : ["foo","bar","baz"]
},
{
  arr : ["bar","foo"]
},
{
  arr : ["foo"]
}

I would like a query that returns the last two documents. Basically any document that contains any combination of only ["foo", "bar"] but excluding anything that has additional items. It is the exclusion aspect that I can't figure out - basically for a given array, only return documents where the arr field contains only elements in that array.

> db.foo.find({arr : {"$in" : ["foo", "bar"]}})
{ "_id" : ObjectId("532a0ff6907560a1e88a2c0a"), "arr" : [  "foo",  "bar",  "baz" ] }
{ "_id" : ObjectId("532a0ffc907560a1e88a2c0b"), "arr" : [  "foo",  "bar" ] }
{ "_id" : ObjectId("532a0ffe907560a1e88a2c0c"), "arr" : [  "foo" ] }
> db.foo.find({arr : {"$all" : ["foo", "bar"]}})
{ "_id" : ObjectId("532a0ff6907560a1e88a2c0a"), "arr" : [  "foo",  "bar",  "baz" ] }
{ "_id" : ObjectId("532a0ffc907560a1e88a2c0b"), "arr" : [  "foo",  "bar" ] }
>

Is this even possible? I will not know what values are excludable at query time.

Answer 1

You can do this by combining multiple operators:

db.foo.find({arr: {$not: {$elemMatch: {$nin: ['foo', 'bar']}}}})

The $elemMatch with the $nin is finding the docs where a single arr element is neither 'foo' nor 'bar', and then the parent $not inverts the match to return all the docs where that didn't match any elements.

However, this will also return docs where arr is either missing or has no elements. To exclude those you need to $and in a qualifier that ensures arr has at least one element:

db.foo.find({$and: [
    {arr: {$not: {$elemMatch: {$nin: ['foo', 'bar']}}}},
    {'arr.0': {$exists: true}}
]})

Answer 2

You can use the aggregation framework to achieve what you want. The query would be something like:

db.collection.aggregate([
    {$unwind:"$arr"}, 
    {$group:{
        _id:"$_id", 
        // If arr is "foo" or "bar", add 0 to the sum else add 1 to the sum
        exclude:{$sum:{$cond:[{$or:[{$eq:["$arr","foo"]},{$eq:["$arr","bar"]}]},0,1]}}}}, 
    // Exclude all documents where "exclude" count is non-zero
    {$match:{exclude:0}}
])