将不存在字段的文档排序到结果末尾 [英] Sort Documents Without Existing Field to End of Results

问题描述

我有以下文档，很少有文档只有bids字段.

I have the following documents, few documents only have bids fields.

收藏夹:

   { 
        "_id" : "PqwSsLb2jsqTycMWR", 
        "name" : "aaa", 
        "bids" : [
            {
                "amount" : NumberInt(450)
            }
        ]
    }
    { 
        "_id" : "93EDoQfeYEFk8pyzX", 
        "name" : "bbb"
    }
    { 
        "_id" : "j5wkK5Eagnwuo8Jym", 
        "name" : "ccc", 
        "bids" : [
            {
                "amount" : NumberInt(520)
            }
        ]
    }
    { 
        "_id" : "eLaTyM5h5kqA97WQQ", 
        "name" : "ddd"
    }

如果我使用bids.amount : 1进行排序，则会得到低于结果的结果

If I sort with bids.amount : 1 am getting below results

结果:

   { 
       "_id" : "93EDoQfeYEFk8pyzX", 
       "name" : "bbb"
   }
   { 
       "_id" : "eLaTyM5h5kqA97WQQ", 
       "name" : "ddd"
   }
   { 
        "_id" : "PqwSsLb2jsqTycMWR", 
        "name" : "aaa", 
        "bids" : [
            {
                "amount" : NumberInt(450)
            }
        ]
    }
    { 
        "_id" : "j5wkK5Eagnwuo8Jym", 
        "name" : "ccc", 
        "bids" : [
            {
                "amount" : NumberInt(520)
            }
        ]
    }

但是我想重新安排bid.amount应该放在顶部的顺序.

But I want to re arrange the order where bid.amount should comes at top.

预期结果:

   { 
        "_id" : "PqwSsLb2jsqTycMWR", 
        "name" : "aaa", 
        "bids" : [
            {
                "amount" : NumberInt(450)
            }
        ]
    }
    { 
        "_id" : "j5wkK5Eagnwuo8Jym", 
        "name" : "ccc", 
        "bids" : [
            {
                "amount" : NumberInt(520)
            }
        ]
    }
    { 
        "_id" : "93EDoQfeYEFk8pyzX", 
        "name" : "bbb"
    }
    { 
        "_id" : "eLaTyM5h5kqA97WQQ", 
        "name" : "ddd"
    }

要获得预期结果的查询是什么?

Whats the query to get expected result?

推荐答案

由于您指定的字段在.sort()中的所有文档中都不存在，因此在不存在该字段的情况下，该值将被视为null，这当然是较低的顺序，因此在排序时具有较高的优先级结果胜过其他任何值.

Since you are specifiying a field that does not exist in all documents in your .sort() then where it is not present the value is considered null, which is of course a lower order and therefore a higher precedence in the sorted result than any other value.

唯一可以改变该响应的方法是，从本质上投影"一个比其他期望值范围更高的值，以使这些结果落在其他结果的末尾.这种具有预计值的加权"查询需要 .aggregate() 方法代替:

The only way to can alter that response is to essentially "project" a higher value from which to sort on than the other expected value range so that those results fall to the end of other results. Such "weighted" queries with a projected value require the .aggregate() method instead:

db.collection.aggregate([
    { "$project": {
        "name": 1,
        "bids": 1,
        "sortfield": { "$ifNull": [ "$bids", 999999 ] }
    }},
    { "$sort": { "sortfield": 1 } }
])

这使用 $project 和 $sort 聚集点阶段可以按所需顺序获得结果.首先使用 $ifNull 操作来确定在投影文档的"sortfield"属性取决于存在的数据，然后在$sort聚合管道阶段使用该值.

This uses the $project and $sort aggregation pipline stages to get the result in the desired order. First with the $ifNull operation to decide what to place in the "sortfield" property of the projected document depending on what data is present and then using that value within the $sort aggregation pipeline stage.

您还可以将常规查询操作与 $match 管道开始阶段的管道阶段，建议减少在$project阶段需要处理的文档.

You can also integrate normal query operations with a $match pipeline stage at the start of the pipeline, which would be recommended to reduce the documents needed to be processed in the $project stage.

如果文档不包含必填字段，则"sortfield"的值将高于预期值，并且这些文档将显示在末尾而不是开始.

With documents not containing the required field the value of "sortfield" will then be higher than expected values and those documents will appear at the end rather than at the start.

这篇关于将不存在字段的文档排序到结果末尾的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！