CODEWITHSUNDEEP
javascriptmongodb
funkyeah
funkyeah

Reputation: 3194

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

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?

Upvotes: 23

Views: 26708

Answers (6)

Eugene Kaurov
Eugene Kaurov

Reputation: 2991

You can use

db.collectionName.find().sort({title: 1}).collation({locale: "en_US", numericOrdering: true})

numericOrdering flag is boolean and is Optional. Flag that determines whether to compare numeric strings as numbers or as strings. If true, compare as numbers; i.e. "10" is greater than "2". If false, compare as strings; i.e. "10" is less than "2". Default is false.

See mongo's collation documentation for an updated explanation of those fields.

Upvotes: 28

Masoud Darzi
Masoud Darzi

Reputation: 650

The mongo documentation said you can use Collation for this goal as @Eugene Kaurov said you can use

.collation({locale: "en_US", numericOrdering: true})

this is the official documentation: mongo ref

and be aware that the accepted answer is not correct now

Upvotes: 5

Raul Rueda
Raul Rueda

Reputation: 750

In my case we work with aggregations. The approach was to sort using the length of our string; only works when the text part is always the same (unnamed- in your case)

db.YourCollection.aggregate([
  {
    $addFields: {
      "TitleSize": { $strLenCP: "$Title" }
    }
  },
  {
    $sort: {
      "TitleIdSize": 1,
      "Title": 1
    }
  }
]);

Now we sort using length, the second sort will use the content.

Example:

  • "unnamed-2", Titlesize: 9
  • "unnamed-7", Titlesize: 9
  • "unnamed-30", Titlesize: 10
  • "unnamed-1", Titlesize: 9

The first sort will put the ids in this order: 2, 7, 1, 30. Then the second sort will put the ids in the correct order: 1, 2, 7, 30.

Upvotes: 0

Jasper den Hartog
Jasper den Hartog

Reputation: 11

In mongo is not possible (sort strings in ascii) but you can sort with the below function after you get all documents from the collection

const sortString = (a, b) => {
  const AA = a.title.split('-');
  const BB = b.title.split('-');

  if (parseInt(AA[1], 10) === parseInt(BB[1], 10)) {
    return 0;
  }
  return (parseInt(AA[1], 10) < parseInt(BB[1], 10)) ? -1 : 1;
};

document.sort(sortString);

Upvotes: 0

MrE
MrE

Reputation: 20798

If you 0 pad the numbers you will be able to search as a string in the right order, so instead of 0,1,2,3,4,5,6,7,8,9,10,11... use 01,02,03,04,05,06,07,08,09,10,11... and a string search will return them in order.

Upvotes: 6

Leopd
Leopd

Reputation: 42769

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.

Upvotes: 17

Related Questions