Sort Documents Without Existing Field to End of Results

Question

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

Collection:

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

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

Result:

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

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

Expected result:

   { 
        "_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?

Answer 1

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.

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

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.

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.

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.