将没有现有字段的文档排序到结果末尾 [英] 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屋！