2

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?

WillyC
  • 3,917
  • 6
  • 35
  • 50
  • 1
    Something like `[{ "$addFields" : { "statusValue " : { "$indexOfArray" : [ [start, middle, end], "$status" ] } } }, { "$sort" : { "statusValue" : 1 } }]`. Anything else will be -1. – s7vr Dec 22 '17 at 16:53
  • That did it - if you feel like writing that up as an answer I'd accept it! – WillyC Dec 22 '17 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 Dec 22 '17 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](https://stackoverflow.com/a/42293303/2683814) answer. Let me know if you think otherwise. – s7vr Dec 22 '17 at 18:02
  • 1
    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 Dec 22 '17 at 18:04
  • You asked how to provide a sort order from something that's not expressed in the documents themselves (order provided by your own custom array). That's what the other question asks - how do I sort by some custom order. – Asya Kamsky Jan 04 '18 at 14:39
  • The proposed duplicate now is not the proposed duplicate that spawned this thread of comments. The current proposed duplicate does seem more appropriate, however it didn’t come up with any searches I tried so having this here may still have value. – WillyC Jan 04 '18 at 14:42

1 Answers1

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}}
]
s7vr
  • 73,656
  • 11
  • 106
  • 127