MongoDB : How to simulate joins or sub query in no SQL MongoDB

No sql MongoDb by its very nature did not support joins and promotes to embed documents, however with new version 3.2 of mongoDb they have an alternative of joins which can be used in aggregation.

For a better performing design you can keep all related data in one big document (embedded) but for some security reasons if you have to keep it separate, then normalization is good idea.

Mongodb does not allow for sub queries or joins, however that can be simulated for example you employee table and instead of embedding salary details in employee document you kept in salary collection and you want to fetch,

SQL query : select salary from salary where employee_id = (select employee_id from employee where employee name like ‘premaseem’)

# Inserted 2 records in 2 different collections for join
> db.employee.insert({eid:1,name:”premaseem”})
WriteResult({ “nInserted” : 1 })
> db.salary.insert({ eid:1, salary:6000 })
WriteResult({ “nInserted” : 1 })

# Validated data in 2 tables
> db.salary.find({ eid:1})
{ “_id” : ObjectId(“56da1a5b2253b2199c53025b”), “eid” : 1, “salary” : 6000 }
> db.salary.find({ eid: db.employee.find({eid:1}) })
> db.employee.find({name : “prem” })
{ “_id” : ObjectId(“56da19d42253b2199c53025a”), “eid” : 1, “name” : “prem” }

#simulated join to get salary for employee premaseem
> db.employee.find({name : “premaseem” }).map(function(d){
var obj = db.salary.findOne({eid : d.eid });
return obj.salary;
} )
Output : 6000



Here is the python script to try out same

__author__ = 'premaseem'

from pymongo import Connection
c = Connection()
db = c.test


obj1 = {"eid":1,"name":"premaseem"}
obj2 = {"eid":2,"name":"sony"}
obj3 = {"eid":3,"name":"meera"}
bulk_employee_insert = [obj1,obj2,obj3]

# insert salary

objs1 = {"eid":1,"salary":1000}
objs2 = {"eid":2,"salary":8000}
objs3 = {"eid":3,"salary":25}
bulk_salary_insert = [objs1,objs2,objs3]


print str(db.employee.count()) + str("total employee")
print str(db.employeeSalary.count()) + str("total salary")

def find_employee() :
    emp_obj = db.employee.find_one({"eid":1})
    print emp_obj

def find_employee_with_joined_salary(eid) :
    emp_obj = db.employee.find_one({"eid":eid})
    emp_sal_obj = db.employeeSalary.find_one({"eid":eid})
    emp_obj["salary"] = emp_sal_obj["salary"]
    print emp_obj


MongoDB 3.2 has come up with $lookup which helps to join in aggregation. For reference follow links



Reference : MongoDB and the Shocking Case of the Missing JOIN ($lookup)

MongoDB doc :




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.