3

The $all operator selects the documents where the value of a field is an array that contains all the specified elements.

In my situation I need the opposite: an operator that selects the documents where the value of a field is an array whose elements are all contained in the specified array.

For example, consider the following collection:

{"name": 1, "vals": ["A", "B"]}
{"name": 2, "vals": ["D", "C"]}
{"name": 3, "vals": ["A", "D"]}

I want to select all the documents whose vals field contains values in ["A", "B", "D"]; that is, documents 1 and 3 (not 2 because "C" is not contained in the specified array).

I cannot use {vals: {$all: ["A", "B", "D"]}}, nor {vals: ["A", "B", "D"]} as both of these operations select the documents whose vals field contains all the specified values, which is not what I want.

Is what I need possible?

  • 1
    {vals: {$in: ["A", "B", "D"]}} will select all documents. not at {vals: {$all: ["A", "B", "D"]}}. – Md Haidar Ali Khan Apr 5 '18 at 6:09
5

Depending on your version of MongoDB server there are a few ways to approach this.

In MongoDB 3.6+ you can use the $setIsSubset expression (which is true only when the first array given is a subset of the second) via the $expr operator:

> db.haystack.find({$expr:{$setIsSubset:["$vals",["A","B","D"]]}},{_id:0})
{
    "name": 1,
    "vals": [
        "A",
        "B"
    ]
}
{
    "name": 3,
    "vals": [
        "A",
        "D"
    ]
}

For earlier versions of MongoDB a similar outcome is possible (but less obvious) using double negation with the $elemMatch operator:

> db.haystack.find({$nor:[{vals:{$elemMatch:{$nin:["A","B","D"]}}}]},{_id:0})
{
    "name": 1,
    "vals": [
        "A",
        "B"
    ]
}
{
    "name": 3,
    "vals": [
        "A",
        "D"
    ]
}

Roughly translated: "find all documents where it is not the case that the vals array includes a value other than one in the provided list".

  • 1
    Nice explanation of $setIsSubset & $elemMatch with respect to OP question , +1 – Md Haidar Ali Khan Apr 5 '18 at 5:59
  • 1
    Ahh nice trick with $elemMatch! I had tried a similar thing with {$not: {vals: {$nin: ["A", "B", "D"]}}} but it didnt' work because $not cannot be used like that. Fortunately I'm on MongoDB 3.6.4, so I'll use the first solution which is really straightforward. – rubik Apr 5 '18 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.