按最大数组字段排序，升序或降序 [英] Sorting by maximum array field, ascending or descending

问题描述

在我的Meteor应用程序中，我具有一系列带有以下子文档的文档集合:

In my Meteor app, I have a collection of documents with an array of subdocuments that look like this:

/* 1 */
{
    "_id" : "5xF9iDTj3reLDKNHh",
    "name" : "Lorem ipsum",
    "revisions" : [ 
        {
            "number" : 0,
            "comment" : "Dolor sit amet",
            "created" : ISODate("2016-02-11T01:22:45.588Z")
        }
    ],
    "number" : 1
}

/* 2 */
{
    "_id" : "qTF8kEphNoB3eTNRA",
    "name" : "Consecitur quinam",
    "revisions" : [ 
        {
             "comment" : "Hoste ad poderiquem",
             "number" : 1,
             "created" : ISODate("2016-02-11T23:25:46.033Z")
        }, 
        {
            "number" : 0,
            "comment" : "Fagor questibilus",
            "created" : ISODate("2016-02-11T01:22:45.588Z")
        }
    ],
    "number" : 2
}

我要做的是查询此集合，并按revisions数组的created字段中的最长日期对结果集进行排序.我还无法完成的任务.我有一些限制条件:

What I want to do is query this collection and sort the result set by the maximum date in the created field of the revisions array. Something I haven't been able to pull off yet. Some constraints I have are:

• 仅按revisions.created排序不会削减它，因为从集合中使用的日期取决于排序方向.无论排序顺序如何，我都必须在集合中使用最大日期.
• 我不能依靠未排序结果集的查询后操作，因此，这必须通过适当的查询或数据库的聚合来完成.
• 不能保证revisions数组将被预先排序.
• 某些文档中可能还有多余的字段，必须填写这些字段，所以请小心使用$project.
• Meteor仍在使用MongoDB 2.6，较新的API功能不好:(

• Just sorting by revisions.created doesn't cut it, because the date used from the collection depends on the sort direction. I have to use the maximum date in the set regardless of sort order.
• I cannot rely on post-query manipulation of an unsorted result set, so, this must be done by a proper query or aggregation by the database.
• There's no guarantee that the revisions array will be pre-sorted.
• There may be extra fields in some documents and those have to come along, so careful with $project.
• Meteor is still using MongoDB 2.6, newer API features are no good :(

推荐答案

您在此处询问的基本问题归结为以下事实:所讨论的数据在数组"内，因此，MongoDB对如何处理这些数据做出了一些基本假设.

The basic problem with what you are asking here comes down to the fact that the data in question is within an "array", and therefore there are some basic assumptions made by MongoDB as to how this gets handled.

如果您以降序"应用排序，则MongoDB会完全按照您的要求进行操作，并按数组中指定字段的最大"值对文档进行排序:

If you applied a sort in "descending order", then MongoDB will do exactly what you ask and sort the documents by the "largest" value of the specified field within the array:

.sort({ "revisions.created": -1 ))

但是，如果相反，您以升序"排序，则当然是相反的，并且将考虑最小"值.

But if instead you sort in "ascending" order then of course the reverse is true and the "smallest" value is considered.

.sort({ "revisions.created": 1 })

因此，唯一的方法就是从数组中的数据中找出最大日期，然后对该结果进行排序.这基本上意味着应用.aggregate()，不幸的是，.aggregate()对于流星是服务器端操作，

So the only way of doing this means working out which is the maximum date from the data in the array, and then sorting on that result. This basically means applying .aggregate(), which for meteor is a server side operation, being unfortunately something like this:

Collection.aggregate([
    { "$unwind": "$revisions" },
    { "$group": {
        "_id": "$_id",
        "name": { "$first": "$name" },
        "revisions": { "$push": "$revisions" },
        "number": { "$first": "$number" }
        "maxDate": { "$max": "$revisions.created" }
    }},
    { "$sort": { "maxDate": 1 }
])

或者最多使用MongoDB 3.2，其中 $max 可以直接应用于数组表达式:

Or at best with MongoDB 3.2, where $max can be applied directly to an array expression:

Collection.aggregate([
    { "$project": {
        "name": 1,
        "revisions": 1,
        "number": 1,
        "maxDate": {
            "$max": {
                "$map": {
                    "input": "$revisions",
                    "as": "el",
                    "in": "$$el.created"
                }
            }
        }
    }},
    { "$sort": { "maxDate": 1 } }
])

但是，实际上两者都不是那么好，即使MongoDB 3.2方法的开销比以前的版本要少，但由于需要通过数据并计算出要排序的值.

But really both are not that great, even if the MongoDB 3.2 approach has way less overhead than what is available to prior versions, it's still not as good as you can get in terms of performance due to the need to pass through the data and work out the value to sort on.

因此，为了获得最佳性能，始终"保留需要在阵列外部"使用的此类数据.为此，有一个 $max"update" 运算符，仅在提供的值大于"已经存在的现有值的情况下，才替换文档中的值.即:

So for best performance, "always" keep such data you are going to need "outside" of the array. For this there is the $max "update" operator, which will only replace a value within the document "if" the provided value is "greater than" the existing value already there. i.e:

Collection.update(
    { "_id": "qTF8kEphNoB3eTNRA" },
    { 
        "$push": {
            "revisions": { "created": new Date("2016-02-01") }            
        },
        "$max": { "maxDate": new Date("2016-02-01") }
    }
)

这意味着您想要的值将始终"以预期值存在于文档中，因此，这只是对该字段进行排序的一个简单问题:

This means that the value you want will "always" be already present within the document with the expected value, so it is just now a simple matter of sorting on that field:

.sort({ "maxDate": 1 })

因此，以我的钱，我将使用现有的数据和任何可用的.aggregate()语句，并使用这些结果来更新每个文档以包含"maxDate"字段.然后更改数组数据的所有添加和修订的编码，以应用 $max每次更改都会更新" .

So for my money, I would go though the existing data with either of the .aggregate() statements available, and use those results to update each doccument to contain a "maxDate" field. Then change the coding of all additions and revisions of array data to apply that $max "update" on every change.

如果您经常使用实心字段而不是计算，则总是更有意义.而且维护非常简单.

Having a solid field rather than a calculation always makes much more sense if you are using it often enough. And the maintenance is quite simple.

无论如何，考虑到以上应用的示例日期，即小于"其他存在的最大日期，我将以各种形式返回给我:

In any case, considering the above applied example date, which is "less than" the other maximum dates present would return for me in all forms:

{
        "_id" : "5xF9iDTj3reLDKNHh",
        "name" : "Lorem ipsum",
        "revisions" : [
                {
                        "number" : 0,
                        "comment" : "Dolor sit amet",
                        "created" : ISODate("2016-02-11T01:22:45.588Z")
                }
        ],
        "number" : 1,
        "maxDate" : ISODate("2016-02-11T01:22:45.588Z")
}
{
        "_id" : "qTF8kEphNoB3eTNRA",
        "name" : "Consecitur quinam",
        "revisions" : [
                {
                        "comment" : "Hoste ad poderiquem",
                        "number" : 1,
                        "created" : ISODate("2016-02-11T23:25:46.033Z")
                },
                {
                        "number" : 0,
                        "comment" : "Fagor questibilus",
                        "created" : ISODate("2016-02-11T01:22:45.588Z")
                },
                {
                        "created" : ISODate("2016-02-01T00:00:00Z")
                }
        ],
        "number" : 2,
        "maxDate" : ISODate("2016-02-11T23:25:46.033Z")
}

根据"maxDate"正确地将第一个文档放在排序顺序的顶部.

Which correctly places the first document at the top of the sort order with consideration to the "maxDate".

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