How to make a MongoDB query sort on strings with -number postfix?

Tags: ,



I have a query:

ownUnnamedPages = Entries.find( { author : this.userId, title : {$regex: /^unnamed-/ }}, {sort: { title: 1 }}).fetch()

That returns the following array sorted:

[ { 
    title: 'unnamed-1',
    text: '<p>sdaasdasdasd</p>',
    tags: [],
    _id: 'Wkxxpapm8bbiq59ig',
    author: 'AHSwfYgeGmur9oHzu',
    visibility: 'public' },
{ 
    title: 'unnamed-10',
    text: '',
    author: 'AHSwfYgeGmur9oHzu',
    visibility: 'public',
    _id: 'aDSN2XFjQPh9HPu4c' },
{ 
    title: 'unnamed-2',
    text: '<p>kkhjk</p>',
    tags: [],
    _id: 'iM9FMCsyzehQvYGKj',
    author: 'AHSwfYgeGmur9oHzu',
    visibility: 'public' },
{ 
    title: 'unnamed-3',
    text: '',
    tags: [],
    _id: 'zK2w9MEQGnwsm3Cqh',
    author: 'AHSwfYgeGmur9oHzu',
    visibility: 'public' }]

The problem is that it seems to sort on the first numeric character so it thinks the proper sequence is 1, 10, 2, 3, etc…. what I really want is for it to sort on both the whole numerical part so that 10 would be at the end.

I’d prefer not to do this by having additional numbers such as 01 or 001 for the numbers.

How would I do that?

Answer

MongoDB can’t sort by numbers stored as strings. You either have to store the number as an integer in its own field, pad with leading zeroes, or sort the results after they’ve been returned from the database.



Source: stackoverflow