MongoDB: operator to match arrays whose elements are all contained in a specified array

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?

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".

StackExchange Database Administrators Q#203042, answer score: 7