MongoDB - Match multiple values in array

Question

I want to be able to find multiple documents that have three or more matching values in an array. Let's say we the following documents:

   [{
       name: 'John',
       cars: [1, 2, 3, 4]
   },
   {
       name: 'Jane',
       cars: [1, 2, 3, 8]
   },
   {
       name: 'Smith',
       cars: [1, 8, 10]
   }]

And we want to find documents that have at least three of the values (in cars) in the following array:

   [1, 2, 3, 4, 5, 6, 7]

The results would then be:

   [{
       name: 'John',
       cars: [1, 2, 3, 4]
   },
   {
       name: 'Jane',
       cars: [1, 2, 3, 8]
   }]

Anyone know how to achieve this?

Answer 1

You can have a $in query issued and then by code filter the record having 3 or more entries in the desired array. (Here is some samle python code)

def dennisQuestion():
    permissibleCars = [1,2,3,4,5,6,7]
    cursor = db.collection.find({"cars": {"$in": permissibleCars}})
    for record in cursor:
       if len(set(permissible) & set(record["cars"]))) >= 3
          yield record

Answer 2

This is a good question, and I don't think there's a simple way to do it with the usual operators that MongoDB gives you. However I can think of the following methods to achieve this:

1. New Field

Calculate this in app code and maintain the result in a new field on the document.

2. Brute Force

db.Collection.find( { $or: [
    { cars: $all [ 1, 2, 3 ] },
    { cars: $all [ 2, 3, 4 ] },
    ... list out all 35 combinations
] } )

3. Use $where

db.Collection.find( { cars: { $in: [1,2,3,4,5,6,7] }, $where: function() {
    var numMatches = 0;
    for (var i = 1; i <= 7; i++)
        if (this.cars.indexOf(i) > -1) numMatches++;
    return numMatches >= 3;
} } );