MongoDb array intersection

Question

Let's assume we have

post1.tags = [ '1', '2', '3' ];

post2.tags = [ '2', '4', '5' ];

post3.tags = [ '1', '3', '4' ];

post4.tags = [ '1', '3', '4', '5', '6' ];

I'm trying to find posts which are containing 2 or more of the given tags [ '1', '3', '5' ]. The result should be post1, post3 and post4. How can write a mongodb query to achieve this?

Some requirements cannot be implemented in queries. If this is part of your application I suggest to to add the functionality there rather than trying to create a query for this. Perhaps you could implement it with javascript on the shell, but this is programming as well. — chaliasos, Jul 01 '13 at 15:53
possible duplicate of [How to check if an array field is a part of another array in MongoDB?](http://stackoverflow.com/questions/15635658/how-to-check-if-an-array-field-is-a-part-of-another-array-in-mongodb) — WiredPrairie, Jul 01 '13 at 17:57
@WiredPrairie it is similar but really what I want. Thanks anyway — ldsenow, Jul 01 '13 at 22:51
If that wasn't a good match, then sorry, I don't think there's an efficient way of doing this within MongoDB. — WiredPrairie, Jul 02 '13 at 00:51

Aaron Dufour · Answer 1 · 2013-07-01T20:40:11.523

2

It sounds like there might be a better way to implement what you're trying to do, but without more information it's difficult to make high-level suggestions.

Here is a query that will get what you're looking for:

{
  $or:
    [   
      {   
        tags:
          {
            $all: ['1', '3']
          }
      },  
      {   
        tags:
          {
            $all: ['3', '5']
          }
      },  
      {   
        tags:
          {
            $all: ['1', '5']
          }
      }   
    ]   
}

You'll notice that it involves listing every combination of pairs of tags that you're searching for, so it won't scale well to larger queries.

Edit: Simplified the query by using $all instead of $and.

edited Jul 01 '13 at 20:40

answered Jul 01 '13 at 18:03

Aaron Dufour

17,288
1
47
69

Actually I thought about this but unfortunately the length of the list is up to 25 and I wanna match at least 20%. – ldsenow Jul 01 '13 at 22:49
@ldsenow That would be a good thing to point out in your question. Maybe if you give an idea of what you're trying from a higher level, someone will be able to help, because this is the best way to do it in just Mongo queries. – Aaron Dufour Jul 02 '13 at 04:35

score -1 · Answer 2 · answered Jul 01 '13 at 16:44

If you want to do this from the shell, you can use javascript. Create a javascropt file with the following code:

use test;

var initialArray = [1, 2, 3];

for (i = 0; i < initialArray.length; i++) {
    for(j = initialArray.length-1; j>=i; j--) {
        if(i!=j) {
            var matchingArray = [initialArray[i].toString(), initialArray[j].toString()];
            print("\nResults using array: " + matchingArray);

            var result = db.posts.find({tags: {$all: matchingArray}});

            while(result.hasNext()){
                printjson(result.next());
            }
        }
    }
}

And run the command

C:\> mongo < query.js

note: you can optimize it to get unique results.

Yeah, I saw it... But in case you add one or two more elements in the given array it will become more complex, won't it? Anyway, I think my answer is just more generic but it is not wrong for downvoting. — chaliasos, Jul 01 '13 at 18:35

MongoDb array intersection

2 Answers2