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
– hyades, Commented Nov 25, 2016 at 11:26
Possible duplicate of How to perform a value based Order By in MongoDB? — Sede
– Sede, Commented Nov 25, 2016 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
– Andrey Nadosha, Commented Nov 25, 2016 at 12:17

hyades · Accepted Answer · 2016-11-25 12:38:49Z

11

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, 2016 at 12:38

answered Nov 25, 2016 at 11:29

hyades

3,2101 gold badge22 silver badges36 bronze badges

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

5 Comments

Andrey Nadosha Over a year ago

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?

hyades Over a year ago

Can you please update the question with these details?

Andrey Nadosha Over a year ago

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.

tru.d Over a year ago

How to make $eq case insensitive?

Ben Crouse Over a year ago

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.

Thyagarajan C · Accepted Answer · 2019-03-29 09:12:05Z

5

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

answered Mar 29, 2019 at 9:12

Thyagarajan C

8,2431 gold badge24 silver badges25 bronze badges

Comments

MasterPiece · Accepted Answer · 2022-03-02 11:14:28Z

2

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,
     }
  }
]

edited Mar 2, 2022 at 11:14

answered Mar 2, 2022 at 10:49

MasterPiece

5741 gold badge8 silver badges16 bronze badges

Comments

Gajraj Kalburgi · Accepted Answer · 2019-01-18 03:06:28Z

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
        }
    }
]

Collectives™ on Stack Overflow

How to sort data in Mongo in a custom order

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related