4

I have a collection with below data structure:

[{name: "123", category: "A"},
 {name: "456", category: "B"},
 {name: "789", category: "A"},
 {name: "101", category: "C"}]

I want to be able to sort them according to the value of category, by specifying which comes first. For example, sorting the query in the order of B->C->A, the result would yield:

[{name: "456", category: "B"},
 {name: "101", category: "C"},
 {name: "123", category: "A"},
 {name: "789", category: "A"}]

Is there any good way of doing so with the mongo query API? I am using mongoengine

Improve this question
1
  • This is the possible duplicate of this question I can't vote to close because the answer to this question is not upvoted or accepted so added an answer. Commented Apr 24, 2016 at 11:56

2 Answers 2

Reset to default
2

The best way to do this is using the .aggregate() method and the $cond conditional operator to add a weight to your documents in the $project stage then use the $sort aggregation operator to sort your documents by weight.

pipeline = [{'$project': {'category': 1,
               'name': 1,
               'w': {'$cond': [{'$eq': ['$category', 'B']},
                               1,
                               {'$cond': [{'$eq': ['$category', 'C']},
                                          2,
                                          3]
                               }]
                    }
            }},
            {'$sort': {'w': 1}}
]

Model.aggregate(*pipeline)

Demo using PyMongo:

>>> import pprint
>>> import pymongo
>>> client = pymongo.MongoClient()
>>> collection = client['test']['collection']
>>> pipeline = [{'$project': {'category': 1,
...                    'name': 1,
...                    'w': {'$cond': [{'$eq': ['$category', 'B']},
...                                    1,
...                                    {'$cond': [{'$eq': ['$category', 'C']},
...                                               2,
...                                               3]
...                                    }]
...                         }
...                 }},
...                 {'$sort': {'w': 1}}
...     ]
>>> pprint.pprint(list(collection.aggregate(pipeline=pipeline)))
[{'_id': ObjectId('571caa930e4f55302502a361'),
  'category': 'B',
  'name': '456',
  'w': 1},
 {'_id': ObjectId('571caa930e4f55302502a363'),
  'category': 'C',
  'name': '101',
  'w': 2},
 {'_id': ObjectId('571caa930e4f55302502a360'),
  'category': 'A',
  'name': '123',
  'w': 3},
 {'_id': ObjectId('571caa930e4f55302502a362'),
  'category': 'A',
  'name': '789',
  'w': 3}]
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

I think it is still not possible to provide a custom sorting function in MongoDB:

You can though, as a workaround, sort in Python, mapping categories to numerical values that would actually be used to sort the documents:

from pprint import pprint

weights = {
    "A": 2,
    "B": 0,
    "C": 1
}

docs = db.col.find()
pprint(sorted(docs, key=lambda item: weights.get(item["category"])))
Improve this answer

4 Comments

Actually this jira is quite old. I wonder why it's still open because since MongoDB version 2.6 you can use the $cond operator to do this server-side. Anyway this will solve the problem but is less efficient than the solution using the native API coded in C++
@user3100115 oh, wow, did not know that. Great answer! Thanks.
@user3100115 great example on using aggregate(), but do you mind elaborate how is mongo's API coded in C++ more efficient than python?
@tropicalfish well-written code in statically typed language like C++ are most of the time faster than dynamically typed language like Python why? The answer is out of the scope here. Also note that some operations are better handled server-side and that is precisely why MongoDB provides the sort method the $sort update operator and the $sort aggregation pipeline operator.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.