MongoDB: Sort by field existing and then alphabetically

Question

In my database I have a field of name. In some records it is an empty string, in others it has a name in it.

In my query, I'm currently doing:

db.users.find({}).sort({'name': 1})

However, this returns results with an empty name field first, then alphabetically returns results. As expected, doing .sort({'name': -1}) returns results with a name and then results with an empty string, but it's in reverse-alphabetical order.

Is there an elegant way to achieve this type of sorting?

Answer 1

How about:

db.users.find({ "name": { "$exists": true } }).sort({'name': 1})

Because after all when a field you want to sort on is not actually present then the returned value is null and therefor "lower" in the order than any positive result. So it makes sense to exclude those results if you really are only looking for something with a matching value.

If you really want all the results in there and regarless of a null content, then I suggest you "weight" them via .aggregate():

db.users.aggregate([
     { "$project": {
         "name": 1,
         "score": {
             "$cond": [
                 { "$ifNull": [ "$name", false ] },
                 1,
                 10
             ]
         }
     }},
     { "$sort": { "score": 1, "name": 1 } }
])

And that moves all null results to the "end of the chain" by assigning a value as such.

Answer 2

If you want to filter out documents with an empty "name" field, change your query: db.users.find({"name": {"$ne": ""}}).sort({"name": 1})