Mongoose: Sorting

Question

what's the best way to sort the following documents in a collection:

{"topic":"11.Topic","text":"a.Text"}
{"topic":"2.Topic","text":"a.Text"}
{"topic":"1.Topic","text":"a.Text"}

I am using the following

find.(topic:req.body.topic).(sort({topic:1}))

but is not working (because the fields are strings and not numbers so I get):

{"topic":"1.Topic","text":"a.Text"},
{"topic":"11.Topic","text":"a.Text"},
{"topic":"2.Topic","text":"a.Text"}

but i'd like to get:

{"topic":"1.Topic","text":"a.Text"},
{"topic":"2.Topic","text":"a.Text"},
{"topic":"11.Topic","text":"a.Text"}

I read another post here that this will require complex sorting which mongoose doesn't have. So perhaps there is no real solution with this architecture?

Your help is greatly appreciated

It's working great. Your topics are strings and are being sorted as such. — Yaron Schwimmer, Aug 14 '16 at 09:04
this doesn't work because as I stated in my question the topics are sorted as strings so I get 1,11,2... — qts, Aug 14 '16 at 12:15
They are all documents in a collection. I have edited the question to make it clearer. — qts, Aug 15 '16 at 02:00

Ravi Shankar Bharti · Answer 1 · 2016-08-15T03:58:17.170

0

i will suggest you make your topic filed as type : Number, and create another field topic_text.

Your Schema would look like:

var documentSchema = new mongoose.Schema({

    topic : Number,
    topic_text : String,
    text : String

});

Normal document would look something like this:

{document1:[{"topic":11,"topic_text" : "Topic" ,"text":"a.Text"},
        {"topic":2,"topic_text" : "Topic","text":"a.Text"},
        {"topic":1,"topic_text" : "Topic","text":"a.Text"}]}

Thus, you will be able to use .sort({topic : 1}) ,and get the result you want. while using topic value, append topic_text to it.

find(topic:req.body.topic).sort({topic:1}).exec(function(err,result)
{
    var topic = result[0].topic + result[0].topic_text;//use index i to extract the value from result array.
})

edited Aug 15 '16 at 03:58

answered Aug 14 '16 at 11:30

Ravi Shankar Bharti

8,922
5
28
52

you mean create another field as follows: {"topic_number":1,"topic":"Topic","text":"a.Text"}? I could do this but was wondering whether there was a less cumbersome way – qts Aug 14 '16 at 12:14
I mean keep only topic field as Numbers 1,2,.. and when you use the topic value create another variable which appends topic number and ".Topic". – Ravi Shankar Bharti Aug 14 '16 at 12:35
the problem is that the topic value can be any string not just 'topic'. {"topic":"1.apple"} or {"topic":"2.banana"} and I need to preserve the text – qts Aug 14 '16 at 13:35
ohk, in that case that wont work. In that case try storing the number and the text in different `fields` – Ravi Shankar Bharti Aug 14 '16 at 13:39
To implement your solution my schema would need to look like this: 'topic: {type: Number},{type:String}' which is not possible afaik. Or do you mean creating a list: {topic:[1,'topic']}? If so I prefer to just use separate fields. – qts Aug 15 '16 at 02:31
I suggested to create another field of `title_text : String` in the schema, along with `topic : Number` . I have edited again for you to understand better. – Ravi Shankar Bharti Aug 15 '16 at 03:56

score 0 · Answer 2 · answered Aug 15 '16 at 06:48

If you do not want (or maybe do not even can) change the shape of your documents to include a numeric field for the topic number then you can achieve your desired sorting with the aggregation framework.
The following pipeline essentially splits the topic strings like '11.Topic' by the dot '.' and then prefixes the first part of the resulting array with a fixed number of leading zeros so that sorting by those strings will result in 'emulated' numeric sorting.
Note however that this pipeline uses $split and $strLenBytes operators which are pretty new so you may have to update your mongoDB instance - I used version 3.3.10.

db.getCollection('yourCollection').aggregate([
    {
        $project: { 
            topic: 1,
            text: 1,
            tmp: { 
                $let: {
                    vars: {
                        numStr: { $arrayElemAt: [{ $split: ["$topic", "."] }, 0] }
                    },
                    in: {
                        topicNumStr: "$$numStr",
                        topicNumStrLen:  { $strLenBytes: "$$numStr" }
                    }
                }
            }
        }
    },
    {
        $project: {
            topic: 1,
            text: 1,
            topicNumber: { $substr: [{ $concat: ["_0000", "$tmp.topicNumStr"] }, "$tmp.topicNumStrLen", 5] },
        }
    },
    {
        $sort: { topicNumber: 1 }
    },
    {
        $project: {
            topic: 1,
            text: 1
        }
    }    
 ])

Mongoose: Sorting

2 Answers2