How to do "group by" in mongoengine

Question

Suppose my Schema looks like so:

class User(Document):
  username = StringField()
  password = StringField()
  category = StringField()

Imagine we have these existing categories: "avengers", "justice-leaguers", "villains", and I want to perform a "group by" query for User.objects.all() so that I can get something like this:

[
 [<User: IronMan object>, <User: Thor object>, <User: Hulk object>], 
 [<User: Superman object>,<User: Batman object>], 
 [<User: Ultron object>, <User: Joker object>, <User: LexLuthor object>]
]

Or better yet:

{
 "avengers": [<User: IronMan object>, <User: Thor object>, <User: Hulk object>], 
 "justice-leaguers": [<User: Superman object>,<User: Batman object>], 
 "villains": [<User: Ultron object>, <User: Joker object>, <User: LexLuthor object>]
}

I looked into MongoEngine's docs and have yet to find anything helpful. Thanks guys!

Realistic · Answer 1 · 2016-11-21T23:10:58.793

20

Since the OP asked for mongoengine (and thats what I needed) here is an example of this using mongoengine:

categories = User.objects.aggregate([{
    '$group': { '_id': '$category', 'username': { '$push': '$username' }}
}])

This will return a pymongo command cursor iterator.

print list(categories)

Will return:

[{ "_id": "villains", "username": ["Ultron", "Joker", "LexLuthor"]},
 { "_id": "justice-leagers", "username": ["Superman", "Batman"]},
 { "_id": "avengers", "username": ["IronMan", "Thor", "Hulk"]}]

edited Nov 21 '16 at 23:10

answered Nov 01 '15 at 20:41

Realistic

1,038
1
10
20

3

@SolessChong it is proper syntax all right. It is a python list of dictionaries. The reason it does not work anymore is possibly a change in mongoengine's aggregate. To make it work simply dismiss the list: `User.objects.aggregate({...})` instead of `([{...}])`. – Wtower Dec 16 '16 at 10:11
1

@Wtower See the edit on Nov 21. The answer has corrected syntax error. – SolessChong Dec 19 '16 at 08:39
@SolessChong alright, thanks for clearing this up. Nevertheless, the comment regarding the list is applicable currently. – Wtower Dec 19 '16 at 09:30
@Realistic, thanks for your answer! Would you mind updating it with the syntax of an example when you have more than one category to group by. This is what would work in the CLI `$group : {_id : {one:"$one", two:"$two"}` but code says "can not encode the object". – hebeha Dec 24 '17 at 12:08

Sylvain Leroux · Accepted Answer · 2015-05-12T17:38:50.987

4

Using the aggregation framework, you only need to $group documents by category:

db.User.aggregate([
  {
    $group: { _id: "$category", username: { $push: "$username" }}
  }
])

Using the $push aggregation function, you will build an array containing all the username sharing the same category.

Given you sample data:

> db.User.find({},{category:1,username:1,_id:0})
{ "category" : "avengers", "username" : "IronMan" }
{ "category" : "avengers", "username" : "Thor" }
{ "category" : "avengers", "username" : "Hulk" }
{ "category" : "justice-leagers", "username" : "Superman" }
{ "category" : "justice-leagers", "username" : "Batman" }
{ "category" : "villains", "username" : "Ultron" }
{ "category" : "villains", "username" : "Joker" }
{ "category" : "villains", "username" : "LexLuthor" }

This will produce:

{ "_id" : "villains", "username" : [ "Ultron", "Joker", "LexLuthor" ] }
{ "_id" : "justice-leagers", "username" : [ "Superman", "Batman" ] }
{ "_id" : "avengers", "username" : [ "IronMan", "Thor", "Hulk" ] }

edited May 12 '15 at 17:38

answered May 12 '15 at 17:32

Sylvain Leroux

50,096
7
103
125

3

Any chance this could be done using Mongoengine? I'm very likely to do this in my python code instead of the mongo shell. Thanks again! – benjaminz May 12 '15 at 19:46
@benjaminz I don't have access to Mongoengine (and have never used it), but it appears to have the required API: [mongoengine.queryset.QuerySet.aggregate(*pipeline, **kwargs)](http://docs.mongoengine.org/en/latest/apireference.html#mongoengine.queryset.QuerySet.aggregate) – Sylvain Leroux May 12 '15 at 19:59
Do check out this answer : [link](http://stackoverflow.com/a/24420848/1882331) for using 'group by' in mongoengine. – Yahya May 31 '16 at 06:41
Is it possible to add where conditions in these queries? – technazi Jun 27 '19 at 15:38

How to do "group by" in mongoengine

2 Answers2