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']}})

当我对橙子和香蕉执行或查询时，我看到了记录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有carrot和banana

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 以便对其执行操作，因为旧版本缺少无需更改即可处理数组的较新运算符.然后，该过程会单独查看每个值，如果 $ 或 匹配可能的值然后 $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<的误解/code> 匹配文档中作为数组的属性.相反，反向"情况是预期的用法，您提供一个参数列表"来匹配给定的属性，该属性是一个数组或只是一个值.

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屋！