MongoDb:从查询中的两个数组中查找公共元素 [英] MongoDb : Find common element from two arrays within a query

问题描述

假设我们在数据库中有以下结构的记录.

Let's say we have records of following structure in database.

{
  "_id": 1234,
  "tags" : [ "t1", "t2", "t3" ]
}

现在，我要检查数据库是否包含具有数组tagsArray which is [ "t3", "t4", "t5" ]

Now, I want to check if database contains a record with any of the tags specified in array tagsArray which is [ "t3", "t4", "t5" ]

我了解$in运算符，但我不仅想知道数据库中的任何记录是否具有在tagsArray中指定的任何标签，我还想知道数据库中的记录的哪个标签与任何在tagsArray中指定的标签. (即在上述记录中为t3)

I know about $in operator but I not only want to know whether any of the records in database has any of the tag specified in tagsArray, I also want to know which tag of the record in database matches with any of the tags specified in tagsArray. (i.e. t3 in for the case of record mentioned above)

也就是说，我想比较两个数组(记录中的一个和我给的另一个)，并找出公共元素.

That is, I want to compare two arrays (one of the record and other given by me) and find out the common element.

我需要将此表达式与查询中的许多表达式一起使用，因此投影运算符(如$，$ elematch等)不会有太大用处. (或者有没有一种方法可以使用它而不必遍历所有记录?)

I need to have this expression along with many expressions in the query so projection operators like $, $elematch etc won't be of much use. (Or is there a way it can be used without having to iterate over all records?)

我认为我可以使用$where运算符，但是我认为这不是最好的方法. 该问题如何解决?

I think I can use $where operator but I don't think that is the best way to do this. How can this problem be solved?

推荐答案

有几种方法可以做您想要的，这仅取决于您的MongoDB版本.只需提交外壳程序响应.内容基本上是JSON表示形式，对于Java中的DBObject实体或要在服务器上执行的JavaScript来说，不难翻译，因此实际上不会改变.

There are a few approaches to do what you want, it just depends on your version of MongoDB. Just submitting the shell responses. The content is basically JSON representation which is not hard to translate for DBObject entities in Java, or JavaScript to be executed on the server so that really does not change.

第一个也是最快的方法是使用MongoDB 2.6和更高版本，在其中您可以进行新的设置操作:

The first and the fastest approach is with MongoDB 2.6 and greater where you get the new set operations:

var test = [ "t3", "t4", "t5" ];

db.collection.aggregate([
   { "$match": { "tags": {"$in": test } }},
   { "$project": {
       "tagMatch": {
           "$setIntersection": [
               "$tags",
               test
           ]
       },
       "sizeMatch": {
           "$size": {
               "$setIntersection": [
                   "$tags",
                   test
               ]
           }
       }
   }},
   { "$match": { "sizeMatch": { "$gte": 1 } } },
   { "$project": { "tagMatch": 1 } }
])

其中的新运算符为 $setIntersection 是主要工作，也是 运算符，可测量数组大小并帮助进行后一种过滤.最终，这是对集合"的基本比较，以便找到相交的项.

The new operators there are $setIntersection that is doing the main work and also the $size operator which measures the array size and helps for the latter filtering. This ends up as a basic comparison of "sets" in order to find the items that intersect.

如果您使用的是MongoDB的早期版本，这仍然可以实现，但是您需要再执行几个阶段，这可能会影响性能，具体取决于您是否拥有大型数组:

If you have an earlier version of MongoDB then this is still possible, but you need a few more stages and this might affect performance somewhat depending if you have large arrays:

var test = [ "t3", "t4", "t5" ];

db.collection.aggregate([
   { "$match": { "tags": {"$in": test } }},
   { "$project": {
      "tags": 1,
      "match": { "$const": test }
   }},
   { "$unwind": "$tags" },
   { "$unwind": "$match" },
   { "$project": {
       "tags": 1,
       "matched": { "$eq": [ "$tags", "$match" ] }
   }},
   { "$match": { "matched": true }},
   { "$group": {
       "_id": "$_id",
       "tagMatch": { "$push": "$tags" },
       "count": { "$sum": 1 }
   }}
   { "$match": { "count": { "$gte": 1 } }},
   { "$project": { "tagMatch": 1 }}
])

或者，如果所有这些似乎都涉及到或者您的数组足够大以至于可以提高性能，那么总会有 mapReduce :

Or if all of that seems to involved or your arrays are large enough to make a performance difference then there is always mapReduce:

var test = [ "t3", "t4", "t5" ];

db.collection.mapReduce(
    function () {
      var intersection = this.tags.filter(function(x){
          return ( test.indexOf( x ) != -1 );
      });
      if ( intersection.length > 0 ) 
          emit ( this._id, intersection );
   },
   function(){},
   {
       "query": { "tags": { "$in": test } },
       "scope": { "test": test },
       "output": { "inline": 1 }
   }
)

请注意，在所有情况下 $in > 运算符仍然可以帮助您减少结果，即使它不是完全匹配的结果.另一个常见的元素是检查相交结果的大小"以减少响应.

Note that in all cases the $in operator still helps you to reduce the results even though it is not the full match. The other common element is checking the "size" of the intersection result to reduce the response.

所有代码都很容易编写，如果您还没有获得最佳结果，请说服老板改用MongoDB 2.6或更高版本.

All pretty easy to code up, convince the boss to switch to MongoDB 2.6 or greater if you are not already there for the best results.

这篇关于MongoDb:从查询中的两个数组中查找公共元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！