How to sort data in Mongo in a custom order

Question

How I can sort this data, using Mongo functionality:

Input

{ "_id" : 1, "domainName" : "test1.com", "hosting" : "hostgator.com" }
{ "_id" : 2, "domainName" : "test2.com", "hosting" : "aws.amazon.com"}
{ "_id" : 3, "domainName" : "test3.com", "hosting" : "aws.amazon.com" }
{ "_id" : 4, "domainName" : "test4.com", "hosting" : "hostgator.com" }
{ "_id" : 5, "domainName" : "test5.com", "hosting" : "aws.amazon.com" }
{ "_id" : 6, "domainName" : "test6.com", "hosting" : "cloud.google.com" }
{ "_id" : 7, "domainName" : "test7.com", "hosting" : "aws.amazon.com" }
{ "_id" : 8, "domainName" : "test8.com", "hosting" : "hostgator.com" }
{ "_id" : 9, "domainName" : "test9.com", "hosting" : "cloud.google.com" }
{ "_id" : 10, "domainName" : "test10.com", "hosting" : "godaddy.com" }

In order by hosting field, to get result, for example in this order:

1) First - all godaddy;

2) Second - all AWS;

3) and next, everything else.

Output

{
    "result" : [
            {

                    "_id" : 10, 
                    "domainName" : "test10.com",
                    "hosting" : "godaddy.com"
            },
            {
                    "_id" : 2, 
                    "domainName" : "test2.com",
                    "hosting" : "aws.amazon.com"
            },
            {
                    "_id" : 3, 
                    "domainName" : "test3.com",
                    "hosting" : "aws.amazon.com"
            },
            {
                    "_id" : 5, 
                    "domainName" : "test5.com",
                    "hosting" : "aws.amazon.com"
            },
            {
                    "_id" : 7, 
                    "domainName" : "test7.com",
                    "hosting" : "aws.amazon.com"
            },
            {
                    "_id" : 1, 
                    "domainName" : "test1.com",
                    "hosting" : "hostgator.com"
            },
            {
                    "_id" : 4, 
                    "domainName" : "test4.com",
                    "hosting" : "hostgator.com"
            },
            {
                    "_id" : 6, 
                    "domainName" : "test6.com",
                    "hosting" : "cloud.google.com"
            },
            {
                    "_id" : 8, 
                    "domainName" : "test8.com",
                    "hosting" : "hostgator.com"
            },
            {
                    "_id" : 9, 
                    "domainName" : "test9.com",
                    "hosting" : "cloud.google.com"
            },
    ]
    }

With this example i want to return results in more relevant way for user. And in origanl task I want to sort this using few another collections, which serve additional information.

But will be good enough if you helps me with previous task?

UPD: About second part of question.

One more task is how to return sortable data from one collection in dependence of another.

Example:

first collection the same which was given before:

 { "_id" : 1, "domainName" : "test1.com", "hosting" : "hostgator.com" }
 ...

second collection, provide some additional info about hosting:

{ '_id': 123, 'quality':'best', 'hostings': ["hostgator.com",  "aws.amazon.com"]},
{ '_id': 321, 'quality':'good', 'hostings': ["cloud.google.com"]},
{ '_id': 345, 'quality':'bad', 'hostings': ["godaddy.com"]},

And in result, I need to return from first collection data in this order:

1) First all good hostings 2) second all good 3) third all bad

Output:

{
"result" : [
        //Best:
        {
                "_id" : 1, 
                "domainName" : "test1.com",
                "hosting" : "hostgator.com"
        },
        {
                "_id" : 4, 
                "domainName" : "test4.com",
                "hosting" : "hostgator.com"
        },
        {
                "_id" : 8, 
                "domainName" : "test8.com",
                "hosting" : "hostgator.com"
        },
        {
                "_id" : 2, 
                "domainName" : "test2.com",
                "hosting" : "aws.amazon.com"
        },
        {
                "_id" : 3, 
                "domainName" : "test3.com",
                "hosting" : "aws.amazon.com"
        },
        {
                "_id" : 5, 
                "domainName" : "test5.com",
                "hosting" : "aws.amazon.com"
        },
        {
                "_id" : 7, 
                "domainName" : "test7.com",
                "hosting" : "aws.amazon.com"
        },

       // Good:
        {
                "_id" : 9, 
                "domainName" : "test9.com",
                "hosting" : "cloud.google.com"
        },
        {
                "_id" : 6, 
                "domainName" : "test6.com",
                "hosting" : "cloud.google.com"
        },

       //Bad
        {
                "_id" : 10, 
                "domainName" : "test10.com",
                "hosting" : "godaddy.com"
        }
]
}

UPDATE 2

I get good answer and example on previous example. Thank you so much! But I stack with another example.

I need to compare IDs of 3 collections to sort in order - first: friend, second: 'requests', and: other users.

Input

db.friends.find({userId: currentUser});
    // {"_id" : "PgC7LrtaZtQsShtzT", "userId" : "tHuxnWxFLHvcpRgHb", "friendId" : "jZagPF7bd4aW8agXb",}
db.requests.find({userId: currentUser});
   // looks like friend but with 'requesterId'

And now I need to aggregate 'users' collection, define the score which matches with previous two collections (friends, requests).

Using provided answer, I managed result but only with one collection. How can I make this with 3 or multiple?

Is it assumed that `godaddy` always means the string `godaddy.com` and same with aws and `aws.amazon.com`? — hyades, Nov 25 '16 at 11:26
Possible duplicate of [How to perform a value based Order By in MongoDB?](http://stackoverflow.com/questions/33037261/how-to-perform-a-value-based-order-by-in-mongodb) — styvane, Nov 25 '16 at 11:57
In first part of question you right, but Its has second, and I can't find any ideas about this. — Andrey Nadosha, Nov 25 '16 at 12:17

hyades · Accepted Answer · 2016-11-25T12:38:49.093

10

You can project each of the hosting into separate type denoted by integers, and finally sort on these integers. Illustrated in the aggregation pipeline below

[
{$lookup: {
    from: 'secondCollectionStoringQuality',
    localField: 'hosting',
    foreignField: 'hostings',
    as: 'nw'
    }},
{$unwind: '$nw'},
{$project: {
        domainName: 1,
        hosting: 1,
        type: {
            $cond: [
                {$eq: ['$nw.quality', 'best']},
                0,
                {$cond: [
                    {$eq: ['$nw.quality', 'good']},
                    1,
                    2
                    ]}
            ]
        }
    }},
    {$sort: {type: 1}}
]

edited Nov 25 '16 at 12:38

answered Nov 25 '16 at 11:29

hyades

3,110
1
17
36

Thank you for this answer. It's very helpful for my question! May be you know how to make sorting depend another collection? for example: I need to sort same data, which defined in question, by parameters which specified in another collection. like this: `{_id: 321, 'quality': 'good', 'hosting': ['godaddy.com', 'aws.amazon.com']};` `{_id: 123, 'quality': 'bad', 'hosting': ['hostgator.com', ... , ... ,]}` I need to queue from first collection, where first hosting will be good than bad? – Andrey Nadosha Nov 25 '16 at 11:51
Can you please update the question with these details? – hyades Nov 25 '16 at 11:53
Sorry for bother you once again. But I can't use given example in case of my problem. This example is good for compare two collections, but how to do the same with 3 or more? I updated the question - Update 2. – Andrey Nadosha Nov 28 '16 at 14:38
How to make $eq case insensitive? – tru.d Oct 19 '21 at 09:06
Worth noting if this is a large or highly queried collection: you won't be able to leverage indexes for this in the aggregation pipeline. If you have that problem, consider using calculated fields when you persist the document. – Ben Crouse Oct 25 '21 at 18:08

score 5 · Answer 2 · answered Mar 29 '19 at 09:12

Above answers works if the data exists in an array. And you can achieve this without $lookup and $project.

.aggregate([
   {$addFields: {
      sortId: {
         $cond: [{$eq: ['$hosting', 'godaddy.com']},0,
            {$cond: [{$eq: ['$hosting', 'aws.amazon.com']},1,2]
      }
   }},
  {$sort: {sortId: 1}}
])

MasterPiece · Answer 3 · 2022-03-02T11:14:28.913

I would use $addFields and $switch to make things a bit more clearer and simpler (this is mongoose format):

[
  {
    $addFields: {
      new_field_to_sort: {
        $switch: {
          branches: [
            { case: { $eq: ['$hosting', 'godaddy.com'] }, then: 0 },
            { case: { $eq: ['$hosting', 'aws.amazon.com'] }, then: 1 },
          ],
          default: 2,
        },
      }
    },
  },
  {
     $sort: {
       new_field_to_sort: 1,
     }
  }
]

score 1 · Answer 4 · answered Jan 18 '19 at 03:06

How about this solution ?

1) N number of conditions.

2) Required Sorting order can be passed by parameter.

[{
        "$lookup": {
            "from": "secondCollectionStoringQuality",
            "localField": "hosting",
            "foreignField": "hostings",
            "as": "nw"
        }
    },
    {
        "$unwind": "$nw"
    },
    {
        "$project": {
            "domainName": 1,
            "hosting": 1,
            "type": {
                "$indexOfArray": [
                    ["best", "good"], "$nw.quality"
                ]
            }
        }
    },
    {
      "$project": {
          "domainName": 1,
          "hosting": 1,
          "type": {
              "$cond": [ {"$eq":["$type", -1]},  10000, "$type"]
          }
      }
    },
    {
        "$sort": {
            "type": 1
        }
    }
]

How to sort data in Mongo in a custom order

4 Answers4

Linked