CODEWITHSUNDEEP
mongodbsorting
missingfaktor
missingfaktor

Reputation: 92026

Customized sorting in MongoDB

I want to sort on a MongoDB collection on some key in ascending order, and need the records that don't have the key to come last. e.g.

db.coll.find({}, {key: 1}).sort({key: 1})

gives:

{_id: 1}
{_id: 2, key: 1}
{_id: 3, key: 2}

but should give following instead:

{_id: 2, key: 1}
{_id: 3, key: 2}
{_id: 1}

Is there a way to do this with MongoDB?

Upvotes: 3

Views: 224

Answers (4)

Eugene Kaurov
Eugene Kaurov

Reputation: 2981

I have a similar problem: I need to sort by text containing numbers in it, and numbers should be displayed AFTER strings. The only way we see for now is to create an indexed field where numbers are replaced with characters so they would be ranked after strings.

Upvotes: 0

Old Pro
Old Pro

Reputation: 25537

This can probably be done using the aggregation framework but it would be rather painful. You would have to replace the missing keys with a marker value that is higher than the valid keys (easy by using a key of a type that sorts after the type of key you have, unless the keys are regexes), then sort the collection, then strip out the marker values.

It would be more efficient to do it in two regular queries using $exist.

Upvotes: 1

attish
attish

Reputation: 3150

Unfortunately mongoDB "when comparing values of different BSON types, MongoDB uses the following comparison order, from lowest to highest:" DOCS

  1. MinKey (internal type)
  2. Null
  3. Numbers (ints, longs, doubles)
  4. Symbol,String
  5. Object
  6. Array
  7. BinData
  8. ObjectID
  9. Boolean
  10. Date, Timestamp
  11. Regular Expression
  12. MaxKey (internal type)

When mongo generates the index it will include a null for those documents which has not got the key. You can do it with two queries, with using spare index for the first.

If you define a spare index on key field the sort will return only those which has value. This one will exclude the documents that has no key for it. DOCS

Upvotes: 3

Remon van Vliet
Remon van Vliet

Reputation: 18595

This is not possible. You will have to set "key" to the theoretically maximum value when you create the documents. Alternatively you can do two queries (one to get all where "key" exists sorted, and an optional other without the "key" set at all to complement your result set if needed). Usually the latter is not possible due to various business rule constraints.

Upvotes: 3

Related Questions