CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Saif Bechan
Saif Bechan

Reputation: 17121

How to ORDER BY FIELD VALUE in MongoDB

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

Upvotes: 3

Views: 5714

Answers (2)

Envy
Envy

Reputation: 540

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
            ]
        ]
    ]
);

Upvotes: 4

Neil Lunn
Neil Lunn

Reputation: 151122

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.

Upvotes: 3

Related Questions