Provide a sort order from string values in mongdb aggregation [duplicate]

Question

Let's say I have a collection with documents that look like this:

{
    _id: <someMongoId>,
    status: 'start'
    otherImportantData: 'Important!'
}

...and status can be 'start', 'middle', or 'end'.

I want to sort these documents (specifically in the aggregation framework) by the status field - but I don't want it in alphabetical order; I want to sort in the order start -> middle -> end.

I've been looking for some way to project the status field to a statusValue field that is numeric (and where I get to dictate the numbers each string maps to), although I'd be happy to look at any alternatives.

Let's say I could do that. I'd take status and map it as such:

start: 1
middle: 2
end: 3
<anything else>: 0

then I could do something like this in the aggregation pipeline:

{
    $sort: { statusValue : 1 }
}

...but I'm not sure how to get those statuses mapped in the aggregation pipeline.

If there is no way to do it, at least I'll know to stop looking. What would be the next best way? Using the Map-Reduce features in MongoDB?

Something like [{ "$addFields" : { "statusValue " : { "$indexOfArray" : [ [start, middle, end], "$status" ] } } }, { "$sort" : { "statusValue" : 1 } }]. Anything else will be -1. — s7vr
– s7vr, Commented Dec 22, 2017 at 16:53
That did it - if you feel like writing that up as an answer I'd accept it! — WillyC
– WillyC, Commented Dec 22, 2017 at 17:52
How do you figure that's a duplicate? It doesn't seem to have any relation to this question to me and the answer you provided is completely different (but please correct me if I'm wrong). — WillyC
– WillyC, Commented Dec 22, 2017 at 17:58
It's similar in the sense that comparison is made between the field from document against the input array and sort then based on the result. Please look at this answer. Let me know if you think otherwise. — s7vr
– s7vr, Commented Dec 22, 2017 at 18:02
That solution uses the same technique to solve the problem, but the question being asked, to me, is completely different. I don't think it is a duplicate even if the same technique can be used to answer the question. As someone searching for an answer to this question I'd would never think that the question being proposed would contain the answer I was looking for. You provided a very direct answer to this specific question and I think it is valid and can stand on its own. — WillyC
– WillyC, Commented Dec 22, 2017 at 18:04

s7vr · Accepted Answer · 2017-12-22 18:05:36Z

6

You can try below aggregation in 3.4.

Use $indexOfArray to locate the position of search string in list of values and $addFields to keep the output index in the extra field in the document followed by $sort to sort the documents

[
 {"$addFields":{ "statusValue":{"$indexOfArray":[[start, middle, end], "$status"]}}}, 
 {"$sort":{"statusValue":1}}
]

answered Dec 22, 2017 at 18:05

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Provide a sort order from string values in mongdb aggregation [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related