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?

Sushant Gupta · Accepted Answer · 2013-03-09 09:50:11Z

16

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

answered Mar 9, 2013 at 9:50

Sushant Gupta

9,5185 gold badges46 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zaid Masud · Accepted Answer · 2013-03-09 12:02:32Z

12

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;
} } );

edited Mar 9, 2013 at 12:02

answered Mar 9, 2013 at 1:01

Zaid Masud

13.5k9 gold badges70 silver badges89 bronze badges

Comments

user1568891 · Accepted Answer · 2019-07-19 18:42:54Z

0

I had to slightly modify @Zaid Masud option 3 when the values where strings in Mongo 4.0.3:

db.Collection.find( { cars: { $in: ["s1", "s2", "s3" , "s4", "s5" , "s6" , "s7"] },
    $where: function() {
        var options = ["s1", "s2", "s3" , "s4", "s5" , "s6" , "s7"];
        var numMatches = 0;
        for (var i = 0; i < 7; i++)
            if (this.cars.indexOf(options[i]) > -1) 
                numMatches++;
        return numMatches >= 3;
    } 
} );

(This seemed a bit large for a comment)

answered Jul 19, 2019 at 18:42

user1568891

3956 silver badges14 bronze badges

Comments

Bhakti Vora · Accepted Answer · 2020-10-10 20:25:01Z

0

For Mongo v4.4.1 this query works

[
  {
    $project: {
      name: 1,
      cars: 1,
      show: {
        $let: {
          vars: {
            "b": {
              $gte: [{$size: {$setIntersection: [ [1,2,3,4,5,6,7],"$cars"]}},3]
            }
          },
          in: "$$b"
        }
      }
    }
  },
  {
    $match: {
      show: true,
      
    }
  },
  {
    $project: {
      show: 0
    }
  }
]

answered Oct 10, 2020 at 20:25

Bhakti Vora

362 bronze badges

Collectives™ on Stack Overflow

MongoDB - Match multiple values in array

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related