MongoDB排序vs数组索引聚合$ sort [英] MongoDB sort vs aggregate $sort on array index

问题描述

具有包含以下文档的MongoDB集合test:

With a MongoDB collection test containing the following documents:

{ "_id" : 1, "color" : "blue", "items" : [  1,  2,  0 ] }
{ "_id" : 2, "color" : "red", "items" : [  0,  3,  4 ] }

如果我基于items数组中的第二个元素以相反的顺序对它们进行排序，则使用

if I sort them in reversed order based on the second element in the items array, using

db.test.find().sort({"items.1": -1})

它们将正确地排序为:

{ "_id" : 2, "color" : "red", "items" : [  0,  3,  4 ] }
{ "_id" : 1, "color" : "blue", "items" : [  1,  2,  0 ] }

但是，当我尝试使用aggregate函数对它们进行排序时:

However, when I attempt to sort them using the aggregate function:

db.test.aggregate([{$sort: {"items.1": -1} }])

即使该查询被接受为有效查询，它们也无法正确排序:

They will not sort correctly, even though the query is accepted as valid:

{
    "result" : [
        {
            "_id" : 1,
            "color" : "blue",
            "items" : [
                1,
                2,
                0
            ]
        },
        {
            "_id" : 2,
            "color" : "red",
            "items" : [
                0,
                3,
                4
            ]
        }
    ],
    "ok" : 1
}

这是为什么?

推荐答案

聚合框架不会像通常对.find()查询所应用的方式那样对数组进行处理".这不仅适用于.sort()之类的操作，而且适用于其他运算符，即

The aggregation framework just does not "deal with" arrays in the same way as is applied to .find() queries in general. This is not only true of operations like .sort(), but also with other operators, and namely $slice, though that example is about to get a fix ( more later ).

因此，几乎不可能使用带有数组位置索引的点表示法"形式来处理任何事情.但这是可以解决的.

So it pretty much is impossible to deal with anything using the "dot notation" form with an index of an array position as you have. But there is a way around this.

您可以做的基本上是计算出第n个数组元素实际上是一个值，然后将其作为可以排序的字段返回:

What you "can" do is basically work out what the "nth" array element actually is as a value, and then return that as a field that can be sorted:

  db.test.aggregate([
    { "$unwind": "$items" },
    { "$group": { 
      "_id": "$_id",
      "items": { "$push": "$items" },
      "itemsCopy":  { "$push": "$items" },
      "first": { "$first": "$items" }
    }},
    { "$unwind": "$itemsCopy" },
    { "$project": {
      "items": 1,
      "itemsCopy": 1,
      "first": 1,
      "seen": { "$eq": [ "$itemsCopy", "$first" ] }
    }},
    { "$match": { "seen": false } },
    { "$group": {
      "_id": "$_id",
      "items": { "$first": "$items" },
      "itemsCopy": { "$push": "$itemsCopy" },
      "first": { "$first": "$first" },
      "second": { "$first": "$itemsCopy" }
    }},
    { "$sort": { "second": -1 } }
  ])

这是一种可怕且可迭代"的方法，实际上，您可以通过获取 $first 使用

It's a horrible and "iterable" approach where you essentially "step through" each array element by getting the $first match per document from the array after processing with $unwind. Then after $unwind again, you test to see if that array elements are the same as the one(s) already "seen" from the identified array positions.

这太糟糕了，对于想要移动的更多位置来说更糟，但是确实得到了结果:

It's terrible, and worse for the more positions you want to move along, but it does get the result:

{ "_id" : 2, "items" : [ 0, 3, 4 ], "itemsCopy" : [ 3, 4 ], "first" : 0, "second" : 3 }
{ "_id" : 1, "items" : [ 1, 2, 0 ], "itemsCopy" : [ 2, 0 ], "first" : 1, "second" : 2 }
{ "_id" : 3, "items" : [ 2, 1, 5 ], "itemsCopy" : [ 1, 5 ], "first" : 2, "second" : 1 }

幸运的是，即将发布的MongoDB版本(如当前在开发版本中可用)获得了修复".它可能不是您想要的完美"解决方案，但确实可以解决基本问题.

Fortunately, upcoming releases of MongoDB ( as currently available in develpment releases ) get a "fix" for this. It may not be the "perfect" fix that you desire, but it does solve the basic problem.

那里有一个新的$slice运算符可用于聚合框架，它将从索引位置返回数组的所需元素:

There is a new $slice operator available for the aggregation framework there, and it will return the required element(s) of the array from the indexed positions:

  db.test.aggregate([
    { "$project": {
      "items": 1,
      "slice": { "$slice": [ "$items",1,1 ] }
    }},
    { "$sort": { "slice": -1 } }
  ])

哪个会产生:

{ "_id" : 2, "items" : [ 0, 3, 4 ], "slice" : [ 3 ] }
{ "_id" : 1, "items" : [ 1, 2, 0 ], "slice" : [ 2 ] }
{ "_id" : 3, "items" : [ 2, 1, 5 ], "slice" : [ 1 ] }

因此您可以注意到，作为切片"，结果仍然是数组"，但是聚合框架中的$sort始终使用数组的第一位置"来对内容进行排序.这意味着从索引位置提取一个奇异值(就像上面的长过程一样)，那么结果将按照您的期望进行排序.

So you can note that as a "slice", the result is still an "array", however the $sort in the aggregation framework has always used the "first position" of the array in order to sort the contents. That means that with a singular value extracted from the indexed position ( just as the long procedure above ) then the result will be sorted as you expect.

这里的最终情况就是它的工作方式.要么从上面执行您需要的那种操作，以在数组的索引位置进行操作，要么等待"，直到有了更好的运算符可以为您提供一个全新的闪亮版本为止.

The end cases here are that is just how it works. Either live with the sort of operations you need from above to work with a indexed position of the array, or "wait" until a brand new shiny version comes to your rescue with better operators.

这篇关于MongoDB排序vs数组索引聚合$ sort的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！