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 });
print(obj.salary);
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

db.employee.drop()
db.employeeSalary.drop()

db.test.insert({"name":"premaseem"})
obj1 = {"eid":1,"name":"premaseem"}
obj2 = {"eid":2,"name":"sony"}
obj3 = {"eid":3,"name":"meera"}
bulk_employee_insert = [obj1,obj2,obj3]

# insert salary
db.employee.insert(bulk_employee_insert)

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

db.employeeSalary.insert(bulk_salary_insert)

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

find_employee_with_joined_salary(2)

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 : https://docs.mongodb.org/manual/reference/operator/aggregation/lookup/#example

 

 

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s