Mongo按数组中的匹配数排序 [英] Mongo Sort by Count of Matches in Array

问题描述

假设我的测试数据是

db.multiArr.insert({"ID" : "fruit1","Keys" : ["apple", "orange", "banana"]})
db.multiArr.insert({"ID" : "fruit2","Keys" : ["apple", "carrot", "banana"]})

为了得到像胡萝卜这样的个别水果

to get individual fruit like carrot i do

db.multiArr.find({'Keys':{$in:['carrot']}})

当我对橙子和香蕉进行 or 查询时，我看到记录fruit1 和fruit2

when i do an or query for orange and banana, i see both the records fruit1 and then fruit2

db.multiArr.find({ $or: [{'Keys':{$in:['carrot']}}, {'Keys':{$in:['banana']}}]})

输出的结果应该是fruit2，然后是fruit1，因为fruit2既有胡萝卜又有香蕉

Result of the output should be fruit2 and then fruit1, because fruit2 has both carrot and banana

推荐答案

要真正首先回答这个问题，您需要计算"与给定条件匹配的数量，以便对结果进行排序"，以优先返回最匹配的结果.

To actually answer this first, you need to "calculate" the number of matches to the given condition in order to "sort" the results to return with the preference to the most matches on top.

为此，您需要聚合框架，这是您在 MongoDB 中用于计算"和操作"数据的工具:

For this you need the aggregation framework, which is what you use for "calculation" and "manipulation" of data in MongoDB:

db.multiArr.aggregate([
  { "$match": { "Keys": { "$in": [ "carrot", "banana" ] } } },
  { "$project": {
    "ID": 1,
    "Keys": 1,
    "order": {
      "$size": {
        "$setIntersection": [ ["carrot", "banana"], "$Keys" ]
      }
    }
  }},
  { "$sort": { "order": -1 } }
])

在版本 3 之前的 MongoDB 上，您可以使用更长的形式:

On an MongoDB older than version 3, then you can do the longer form:

db.multiArr.aggregate([
  { "$match": { "Keys": { "$in": [ "carrot", "banana" ] } } },
  { "$unwind": "$Keys" },
  { "$group": {
    "_id": "$_id",
    "ID": { "$first": "$ID" },
    "Keys": { "$push": "$Keys" },
    "order": {
      "$sum": {
        { "$cond": [
          { "$or": [
           { "$eq": [ "$Keys", "carrot" ] },
           { "$eq": [ "$Keys", "banana" ] }
         ]},
         1,
         0
        ]}
      }
    }
  }},
  { "$sort": { "order": -1 } }
])

在任何一种情况下，这里的功能都是首先通过提供带有 $in.获得结果后，您希望将数组中匹配元素的数量计数"到提供的可能值的列表"中.

In either case the function here is to first match the possible documents to the conditions by providing a "list" of arguments with $in. Once the results are obtained you want to "count" the number of matching elements in the array to the "list" of possible values provided.

在现代形式中 $setIntersection 运算符比较两个列表"，返回一个仅包含唯一"匹配成员的新数组.由于我们想知道有多少匹配，我们只需返回 该列表的 $size.

In the modern form the $setIntersection operator compares the two "lists" returning a new array that only contains the "unique" matching members. Since we want to know how many matches that was, we simply return the $size of that list.

在旧版本中，您使用 $ 拆分文档数组unwind 以便对其执行操作，因为旧版本缺少可以在不更改的情况下使用数组的新运算符.然后，该过程会单独查看每个值，如果 $or 中的任一表达式 匹配可能的值，然后 $cond 三元组将 1 的值返回给 $sum 累加器，否则为 0.最终结果与现代版本显示的匹配计数"相同.

In older versions, you pull apart the document array with $unwind in order to perform operations on it since older versions lacked the newer operators that worked with arrays without alteration. The process then looks at each value individually and if either expression in $or matches the possible values then the $cond ternary returns a value of 1 to the $sum accumulator, otherwise 0. The net result is the same "count of matches" as shown for the modern version.

最后就是简单地$sort 结果基于返回的匹配计数"，因此最多匹配位于顶部".这是降序"，因此您提供 -1 来表明这一点.

The final thing is simply to $sort the results based on the "count of matches" that was returned so the most matches is on "top". This is is "descending order" and therefore you supply the -1 to indicate that.

对于初学者来说，您对 MongoDB 查询有一些误解.$in 运算符实际上是用于这样的参数列表":

You are misunderstanding a couple of things about MongoDB queries for starters. The $in operator is actually intended for a "list" of arguments like this:

{ "Keys": { "$in": [ "carrot", "banana" ] } }

这本质上是在属性'Keys'中匹配'carrot'或'banana'"的简写方式.甚至可以写成这样的长格式:

Which is essentially the shorthand way of saying "Match either 'carrot' or 'banana' in the property 'Keys'". And could even be written in long form like this:

{ "$or": [{ "Keys": "carrot" }, { "Keys": "banana" }] }

如果它是一个奇异"匹配条件，那么你只需提供要匹配属性的值:

Which really should lead you to if it were a "singular" match condition, then you simply supply the value to match to the property:

{ "Keys": "carrot" }

所以这应该涵盖您使用 $in 匹配文档中的数组属性.相反，反向"情况是预期的用法，您提供参数列表"来匹配给定属性，该属性是数组还是单个值.

So that should cover the misconception that you use $in to match a property that is an array within a document. Rather the "reverse" case is the intended usage where instead you supply a "list of arguments" to match a given property, be that property an array or just a single value.

MongoDB 查询引擎不区分相等或类似操作中的单个值或值数组.

The MongoDB query engine makes no distinction between a single value or an array of values in an equality or similar operation.

这篇关于Mongo按数组中的匹配数排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！