Find documents with array field that only contains specific values and nothing else

user_7987987 0
Viewed 1468

Consider this collection:

{
  { name: 'first', toys: ['doll', 'car', 'doll'],
  { name: 'second', toys: ['doll', 'car'],
  { name: 'third', toys: ['doll', 'car', 'bricks'],
  { name: 'fourth', toys: ['doll', 'bricks'],
  { name: 'fifth', toys: []
}

I want to query for documents whose toys field is an array that only contains doll and car. In this case, both first and second should match. first matches because doll and car can be repeated in the array, third doesn't match because no other value must be present in the array, and fourth and fifth don't match because they don't contain both doll and car.

Using $all and $in doesn't work for me because they match third. How can I achieve this? Thanks!

mongoose mongodb
2 Answers

A better approach would be to use the aggregate operator $setEquals which compares two or more arrays and returns true if they have the same distinct elements and false otherwise:

db.collection.find({
    '$expr': { 
        '$setEquals': ['$toys', ['doll', 'car']] 
    }
})

Another alternative is $setDifference which takes two arrays and performs a relative complement of the second array relative to the first and this operation does not require elements to be in order.

In your case use the result from $setDifference to check if its empty and set that as basis for your query.

For example the operation

{ $setDifference: [ ['doll', 'car', 'doll'], ['doll', 'car'] ] } => []

and this

{ $setDifference: [ ['car', 'doll', 'doll'], ['doll', 'car'] ] } => []

as well as

{ $setDifference: [ ['car', 'doll'], ['doll', 'car'] ] } => []

or

{ $setDifference: [ ['doll', 'car'], ['doll', 'car'] ] } => []

but

{ $setDifference: [ ['doll', 'car', 'bricks'], ['doll', 'car'] ] } => ['bricks']

Using the logic above, as a helper you can take the length of the array result with $size and check if it's 0 for your query expression with $expr

Final query:

db.collection.find({
    '$expr': {
        '$eq': [
            { '$size': { 
                '$setDifference': [ 
                    '$toys', 
                    ['doll', 'car'] 
                ] 
            } },
            0
        ]
    }
})
user_1220050

Following query retrieves if the document is having either car or doll or both. But it won't retrieve empty array records.

db.getCollection('toys').find({
    $and: [{
        toys: {$elemMatch: {$in: ["doll", "car"]}}
    },
    {
        toys: {$not: {$elemMatch: {$nin: ["doll", "car"]}}}
    }]})
user_74588870
Related