Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can mongo return documents with empty/missing fields at the end in asc order?

Tags:

mongodb

db.jason.find().sort({"rank":1})

{ "_id" : ObjectId("51ae517372779b7eeeb81342"), "name" : "jason" }
{ "_id" : ObjectId("51ae517372779b7eeeb81343"), "name" : "jason" }
{ "_id" : ObjectId("51ae513772779b7eeeb8133a"), "name" : "jason", "rank" : 0 }
{ "_id" : ObjectId("51ae513772779b7eeeb8133b"), "name" : "jason", "rank" : 1 }
{ "_id" : ObjectId("51ae513772779b7eeeb8133c"), "name" : "jason", "rank" : 2 }

I'd like the documents without rank to conclude the sort vs beginning it...but also keep the items with rank in asc order. Is this possible or should I default empty ones with either a string or high number like 99999?

like image 355
jasonsemko Avatar asked Jun 04 '13 22:06

jasonsemko


1 Answers

When returning the result for db.jason.find().sort({"rank":1}), MongoDB will order the documents by "rank" type, and then by "rank" value. For the purpose of sort order, MongoDB treats documents where a field is missing as having a NULL type for that field. The NULL type is ordered before numeric types, and this cannot be changed (see http://docs.mongodb.org/manual/reference/method/cursor.sort/ for the built-in type sort order). I would suggest constructing two queries instead (one for documents containing "rank", and one for documents without "rank") and merging the results in your application. However, if you need to keep this a single query, then you will need to set "rank" in all documents to generate the order you desire (for example, by using a sentinel value with a type which sorts after numeric types).

like image 67
J Rassi Avatar answered Nov 12 '22 02:11

J Rassi