Mongodb(via MongoEngine) join query with aggregate

Jan 9, 2017 · 3 min read · mongodb ·

Since Mongodb 3.2 and MongoEngine 0.9, we can use $aggregate command to perform join queries on multiple collections in a database. This post would be a simple tutorial for join queries on Mongodb(via MongoEngine in Python) with examples.

Models Setup

Let's consider models defined as below:

 1import random
 2import mongoengine
 3
 4
 5class User(mongoengine.Document):
 6    meta = {"indexes": ['rnd']}
 7    name = mongoengine.StringField()
 8    rnd = mongoengine.FloatField(default=random.random)
 9
10
11class Group(mongoengine.Document):
12    meta = {"indexes": ['rnd']}
13    name = mongoengine.StringField()
14    rnd = mongoengine.FloatField(default=random.random)
15
16
17class Relation(mongoengine.Document):
18    meta = {"indexes": ['user_id', 'group_id']}
19    user_id = mongoengine.ObjectIdField()
20    group_id = mongoengine.ObjectIdField()

Note:

User and Group documents will be generated randomly.
rnd fields in User and Group, are used for efficiently pick a random document.
Relation documents are matched by randomly picking (User, Group) pairs.

Join Queries

Based on the Mongodb official document, aggregation enables pipelining several operators on the original data from a collection. From MongoEngine you may use aggregation via

1cursor = SomeDocumentClass.objects.aggregate(*pipelines)

where pipelines is a list of stages defined by a dict(the keys of the dict can be found via the document). And let's run a few examples.

Join User and Relation

The join is powered by the $lookup operator.

1curosr = User.objects.aggregate(*[
2         {
3          '$lookup': {
4              'from': Relation._get_collection_name(),
5              'localField': '_id',
6              'foreignField': 'user_id',
7              'as': 'relation'}
8         }])

A object from the result set would be like

 1{
 2 '_id': ObjectId('58725790958bee0e467d12fe'),
 3 'rnd': 0.4821115717624539,
 4 'relation': [
 5     {
 6      'user_id': ObjectId('58725790958bee0e467d12fe'),
 7      '_id': ObjectId('58725790958bee0e467d12ff'),
 8      'group_id': ObjectId('58725790958bee0e467d12e4')
 9     }
10 ],
11 'name': 'user_sqkXPqverW'}

Use $unwind to deal with list

You may have noticed from above results, the relation in a result object was a list since there may be one-to-many mapping. The $unwind will let you expand the list. And remember that aggregate is an pipeline, so we just append $unwind operators behind.

 1curosr = User.objects.aggregate(*[
 2         {
 3          '$lookup': {
 4              'from': Relation._get_collection_name(),
 5              'localField': '_id',
 6              'foreignField': 'user_id',
 7              'as': 'relation'},
 8          '$unwind': {
 9              '$unwind': "$relation"}
10         }])

And from the results, you will notice that relation became a dict:

1{
2 '_id': ObjectId('58725790958bee0e467d12fe'),
3 'rnd': 0.4821115717624539,
4 'relation': {
5      'user_id': ObjectId('58725790958bee0e467d12fe'),
6      '_id': ObjectId('58725790958bee0e467d12ff'),
7      'group_id': ObjectId('58725790958bee0e467d12e4')
8     },
9 'name': 'user_sqkXPqverW'}

Join more collections: User, Relation and Group

Join more collections are pretty simple, you can just append more $lookup operators in the pipeline.

 1cursor = User.objects.aggregate(*[
 2             {'$lookup': {'from': Relation._get_collection_name(),
 3                          'localField': '_id',
 4                          'foreignField': 'user_id',
 5                          'as': 'relation'}},
 6             {'$unwind': "$relation"},
 7             {'$lookup': {'from': Group._get_collection_name(),
 8                          'localField': 'relation.group_id',
 9                          'foreignField': '_id',
10                          'as': 'group'}},
11             {'$unwind': "$group"},
12         ])

And in result objects, there would be group object:

 1{
 2 'group': {
 3     '_id': ObjectId('58725790958bee0e467d12e4'),
 4     'rnd': 0.9648027926390293,
 5     'name': 'group_xgsYceERw'
 6 },
 7 '_id': ObjectId('58725790958bee0e467d12fe'),
 8 'rnd': 0.4821115717624539,
 9 'relation': {
10     'user_id': ObjectId('58725790958bee0e467d12fe'),
11     '_id': ObjectId('58725790958bee0e467d12ff'),
12     'group_id': ObjectId('58725790958bee0e467d12e4')},
13     'name': 'user_sqkXPqverW'
14 }

Several Notes

ObjectId Field

You should use ObjectId to perform join with other collection's _id field(which is also an ObjectId), Mongodb can not do type conversion from ObjectId to string(or vice vesa) in a aggregation query.

There are several issues on Mongodb official jira addressing the problem(SERVER-11400, SERVER-22781, SERVER-24947).