Sorting and null values

[Nick Hoffman]

Hey guys. When sorting on a field in ascending order, why are documents that have a null value for that field returned first? Eg:

> db.foos.drop()

true

> 

> db.foos.insert({position: 5})

> db.foos.insert({position: null})

> db.foos.insert({position: 2})

> 

> 

> db.foos.find().sort({position: 1})

{ "_id" : ObjectId("4ea8c4149a551b253f350241"), "position" : null }

{ "_id" : ObjectId("4ea8c4219a551b253f350243"), "position" : 2 }

{ "_id" : ObjectId("4ea8c41f9a551b253f350242"), "position" : 5 }

Is there a way to sort in ascending order, and have the documents whose "position" is null be at the end?

Thanks,

Nick

[Nat]

Not really. The data is sorted by canonical type where null has much lower canonical value than that of number.

[Nick Hoffman]

Hm, that's disappointing. It really doesn't make sense to order null values before sortable values.

[Sam Millman]

I suppose you could make the default value of your position field be a number you know is so high it will never be touched by the others.

On 27 October 2011 14:30, Nick Hoffman <ni...@deadorange.com> wrote:

Hm, that's disappointing. It really doesn't make sense to order null values before sortable values.

--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.

To view this discussion on the web visit https://groups.google.com/d/msg/mongodb-user/-/zoiZEDod_jYJ.

To post to this group, send email to mongod...@googlegroups.com.
To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.

[nader]

Nick, I'm running into the same issue.  Did you figure anything worthwhile out?  The best I could come up with was to have a duplicate field that was used purely for sorting.  The real fields default to null, whereas the sort field defaults to MAX_VALUE so that it always sorts last unless explicitly set.

Pretty ugly, mongo should support this.

[Nick Hoffman]

I still haven't found a solution, unfortunately.

[Wes Freeman]

This doesn't answer your question, but if you guys don't want to see the null values in the results, you could not define them--leave position "unset"--and use a sparse index. Obviously, this is a different result set (as it leaves out the records that don't have position), but it might suit your needs.

> db.foos.find();

{ "_id" : ObjectId("4f2afc0d1633c741d52f0c95"), "position" : 5 }

{ "_id" : ObjectId("4f2afc0d1633c741d52f0c96") }

{ "_id" : ObjectId("4f2afc121633c741d52f0c97"), "position" : 2 }

> db.foos.ensureIndex({position:1},{sparse:true});

> db.foos.find().sort({position:1});

{ "_id" : ObjectId("4f2afc121633c741d52f0c97"), "position" : 2 }

{ "_id" : ObjectId("4f2afc0d1633c741d52f0c95"), "position" : 5 }

[Nick Hoffman]

On Thursday, 2 February 2012 16:17:05 UTC-5, Wes Freeman wrote:

This doesn't answer your question, but if you guys don't want to see the null values in the results, you could not define them--leave position "unset"--and use a sparse index. Obviously, this is a different result set (as it leaves out the records that don't have position), but it might suit your needs.

Yeah, I think a lame workaround is to query and sort documents with a non-null value, then do the same thing for documents with a null value.

Paginating that is less than ideal, though, because you need to do count() on both queries.

[Sam Millman]

I just solve it with higher than possible numbers or strings that forces the null results to the back. Works a treat in my app coding and works very well on the db side, fair enough it is a little dirty when you look at it in mongo console or something but meh solves having to degrade performance through two queries and trying to paginate them as one query.

--

You received this message because you are subscribed to the Google Groups "mongodb-user" group.

To view this discussion on the web visit https://groups.google.com/d/msg/mongodb-user/-/uk50r7UJUkoJ.

[Nick Hoffman]

On Friday, 3 February 2012 11:11:07 UTC-5, Sammaye wrote:

I just solve it with higher than possible numbers or strings that forces the null results to the back. Works a treat in my app coding and works very well on the db side, fair enough it is a little dirty when you look at it in mongo console or something but meh solves having to degrade performance through two queries and trying to paginate them as one query.

What string value do you use to ensure that they're ordered at the end of the results? 

[Sam Millman]

Depends on the scenario.

With the Alphabet I always use "zz" that ensures it is actually behind "z"

--

You received this message because you are subscribed to the Google Groups "mongodb-user" group.

To view this discussion on the web visit https://groups.google.com/d/msg/mongodb-user/-/HigkbCO6DIcJ.

[Nick Hoffman]

On Friday, 3 February 2012 11:23:55 UTC-5, Sammaye wrote:

Depends on the scenario.

With the Alphabet I always use "zz" that ensures it is actually behind "z"

That makes sense. And it looks like MongoDB orders "z" after non-alphanumeric characters. 

[Tiny Wisp]

Any news on this? 5 years past...

[Kevin Adistambha]

Hi,

I believe the issue discussed in this thread is captured in SERVER-24310. If this is the feature you seek, please vote/comment on the issue describing your use case.

Best regards,
Kevin

​