I want to sort my index in a specific way (-1, 1) and want to make sure that null values are at the top of the index. How can I ensure this?
6
-
1null values will be at the top when you sort, if you want them to come last you need to put something other than null inSammaye– Sammaye2013-03-07 09:50:47 +00:00Commented Mar 7, 2013 at 9:50
-
Thank you. Does that also mean they will be on top, if I use .findOne() and they are indexed?user1680104– user16801042013-03-07 11:44:07 +00:00Commented Mar 7, 2013 at 11:44
-
findOne will only find one document. They should be on top on find() thoughSammaye– Sammaye2013-03-07 11:45:33 +00:00Commented Mar 7, 2013 at 11:45
-
But since the index is sorted this way, shouldn't it be on top?user1680104– user16801042013-03-07 15:35:47 +00:00Commented Mar 7, 2013 at 15:35
-
nahg findOne does not support a sort, only find does, you will still need to sort your index inside the querySammaye– Sammaye2013-03-07 15:36:51 +00:00Commented Mar 7, 2013 at 15:36
|
Show 1 more comment
2 Answers
If you are sorting descending and you are seeing null values at the end, that would be the default behaviour of the sort.
There's really not much that can be done to change that behaviour, but a workaround that will give you the results you're looking for is to do two queries instead of one:
db.Collection.find( { a: null } );
db.Collection.find( { a: { $ne: null } } ).sort( { a: -1, b: 1 } );
4 Comments
user1680104
Can I create a reverse sorted index somehow and use this with findOne? So it just gives me a null value, if there are any with null values?
Zaid Masud
@user1680104
findOne does not support sorting. However if you are interested in using findOne to find a null value you could do something like db.collection.findOne( { a: null } )user1680104
I know. I just want to basically have an "if one with null exists take it, else take one without" by one query.
Maxím G.
Another option is to create boolean field
has_a and include it in sort like .sort({has_a: 1, a: -1, b: 1}). It can be helpful if you're required to have one query only (e.g. apply pagination on results later on). You may want to include it in the index as well (to have compound index {has_a: 1, a: -1} instead of {a: -1}).You have said" I just want to basically have an 'if one with null exists take it, else take one without' by one query", how about db.collection.find().sort().limit(1) ?
