How to ORDER BY FIELD VALUE in MongoDB

Question

In Mysql I often use the FIELD() function in the ORDER BY clause:

ORDER BY FIElD(id, '1', '6', '3', ...);

How does one get the same results in MongoDB? I tried the following:

.find(...).sort({id: [1, 6, 3]})

This did not work

And I say that because there is a way to apply the $cond operator to these "values" in order to apply that "weighting" function. — Neil Lunn
– Neil Lunn, Commented Mar 24, 2014 at 11:17
You can do this with the aggregation framework as shown below however it will not use an index unlike in SQL as such this will not only be clipped in terms of RAM usage but also you will find it will get exponentially slower as you insert more. — Sammaye
– Sammaye, Commented Mar 24, 2014 at 11:37
Thanks for the answer, this sorting would be more of a 'nice feature' in my application rather than a necessity, maybe ill leave it out. — Saif Bechan
– Saif Bechan, Commented Mar 24, 2014 at 11:50
There is a JIRA to do this properly somewhere so it won't always be the case that you would have to use the aggregation framework, it is more of a hack than anything. — Sammaye
– Sammaye, Commented Mar 24, 2014 at 11:51

Envy · Accepted Answer · 2019-12-18 06:54:50Z

4

We can use $indexOfArray

Console

db.collectionName.aggregate([{
    $match: {
        _id: {
            $in: [249, 244]
        }
    }
}, {
    $addFields: {
        sort: {
            $indexOfArray: [
                [249, 244], "$_id"
            ]
        }
    }
},{
    $sort: {
        sort: 1
    }
}])

PHP code

$data = $this->mongo->{$collectionName}->aggregate(
    [
        [
            '$match' => ['_id' => ['$in' => $idList]]
        ],
        [
            '$addFields' => ['sort' => ['$indexOfArray' => [$idList, '$_id']]]
        ],
        [
            '$sort' => ['sort' => 1]
        ],
        [
            '$project' => [
                'name' => 1
            ]
        ]
    ]
);

edited Dec 18, 2019 at 6:54

answered Dec 17, 2019 at 8:11

Envy

5406 silver badges20 bronze badges

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

Comments

Neil Lunn · Accepted Answer · 2014-03-24 11:25:45Z

3

So for the record:

Given the array [1,6,3] what you want in your query is this:

db.collection.aggregate([
   { "$project": {
       "weight": { "$cond": [
           { "$eq": ["_id": 1] },
           3, 
           { "$cond": [
               { "$eq": ["_id": 6] },
               2,
               { "$cond": [
                   { "$eq": ["_id": 3] },
                   1,
                   0
               ]},
           ]}, 
       ]}
   }},
   { "$sort": { "weight": -1 } }
])

And that gives you specific "weights" by order of your "array" of inputs to "project" weights upon the results.

answered Mar 24, 2014 at 11:25

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

2 Comments

Saif Bechan Over a year ago

That looks a lot more complex than I would expect. Will give it a go, thanks for the answer. BTW, you voted to close the question, are you sure this is the only possible solution, and will be till the end of days, lol?

Neil Lunn Over a year ago

@SaifBechan I am pretty sure that there is only one way to supply an external "weight" here as your "platform specific" SQL demonstrates. So yes I'm pretty sure* I am speaking with some authority here after doing this for more than 26 years of my life.

Collectives™ on Stack Overflow

How to ORDER BY FIELD VALUE in MongoDB

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related