Easiest Way to Implement JOINs in MongoDB 2.4

March 12, 2014March 12, 2014 Ayush MishraJavaScript, MongoDBMongoDB12 Comments

Table of contents

Reading Time: 2 minutes

Last year, I wrote a blog to implement JOINs in MongoDB. But this solution was applicable only for versions below Mongo 2.4.

According to the release log of 2.4, this feature is removed. We could not use another collection on map reduce, which is invoked against a particular collection. Earlier it was permitted.

MongoDB is a no-SQL database and doesn’t support joins. But if you have such a scenario, where you have a bad structured DB schema or related collections in MongoDB, then this solution would work.

After getting blog reader’s feedback, I have written this blog with the solution for MongoDB 2.4.x.
Let’s go with a small example :
We have two collections Employee and Department . We have to fetch details of all employees with their department .

db.createCollection("employee");
db.employee.insert(
   {
      _id: 1,
      name: { first: 'John', last: 'Backus' },
      city: 'New York',
      department:1
   }
);

db.employee.insert(
    {
      _id: 2,
      name: { first: 'Merry', last: 'Desuja' },
      city: 'London',
      department:2
   }
);
db.createCollection("department");
db.department.insert(
   {
      _id: 1,
      department: 'Manager'
   }
);

db.department.insert(
    {
      _id: 2,
      department: 'Accountant'
   }
);

var mapEmployee = function () {
                var output= {departmentid : this.department,firstname:this.name.first, lastname:this.name.last , department:null}
                    emit(this.department, output);
                };

var mapDepartment = function () {

                var output= {departmentid : this._id,firstname:null, lastname:null , department:this.department}
                    emit(this._id, output);
                };

var reduceF = function(key, values) {
    var outs = {firstname:null, lastname:null , department:null};

    values.forEach(function(v){

                   if(outs.firstname ==null){
                        outs.firstname = v.firstname
                    }
                    if(outs.lastname ==null){
                        outs.lastname = v.lastname
                    }
                    if(outs.department ==null){
                        outs.department = v.department
                    }
                    
     });
    return outs;
};

result = db.employee.mapReduce(mapEmployee, reduceF, {out: {reduce: 'emp_dept'}})

result = db.department.mapReduce(mapDepartment,reduceF, {out: {reduce: 'emp_dept'}})


db.emp_dept.find()
{ "_id" : 1, "value" : { "firstname" : "John", "lastname" : "Backus", "department" : "Manager" } }
{ "_id" : 2, "value" : { "firstname" : "Merry", "lastname" : "Desuja", "department" : "Accountant" } }

Written by Ayush Mishra

Ayush is the Sr. Lead Consultant @ Knoldus Software LLP. In his 10 years of experience he has become a developer with proven experience in architecting and developing web applications. Ayush has a Masters in Computer Application from U.P. Technical University, Ayush is a strong-willed and self-motivated professional who takes deep care in adhering to quality norms within projects. He is capable of managing challenging projects with remarkable deadline sensitivity without compromising code quality.

12 thoughts on “Easiest Way to Implement JOINs in MongoDB 2.42 min read”

Pingback: An alternate way to implement JOINs in MongoDB [Update] | Knoldus
charlesmartin122 says:

March 13, 2014 at 5:31 PM

What’re we looking at for performance as the db scales? The lesson I’ve been learning in mongo is that you can do fairly insane things with mapreduce or the aggregation pipeline…but I wonder at the query having to populate potentially millions of rows of data, then iterating against them with inserts per-query.
Pingback: Jointure de table avec MongoDB | Giwi
Mohsin says:

September 29, 2014 at 10:17 AM

Hi Ayush,

The above mapreduce program really works in mongodb 2.4.11 via shell commands. Awesome. Thanks for sharing!
I tried the same mapreduce program in java but getting an error as below –

Could you please help me in this? thanks….

~~~~~~~~~~~
com.mongodb.CommandResult$CommandFailure: command failed [command failed [mapreduce] { “errmsg” : “exception: SyntaxError: Unexpected identifier” , “code” : 16722 , “ok” : 0.0}
at com.mongodb.CommandResult.getException(CommandResult.java:69)
at com.mongodb.CommandResult.throwOnError(CommandResult.java:79)
at com.mongodb.DBCollection.mapReduce(DBCollection.java:963)
at MapReduce.main(MapReduce.java:52)
~~~~~~~~~~~

Below is my java program for above mapreduce functionality –

import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.DBObject;
import com.mongodb.MapReduceCommand;
import com.mongodb.MapReduceOutput;
import com.mongodb.Mongo;

public class MapReduce {

public static void main(String[] args) {

Mongo mongo;

try {
mongo = new Mongo(“localhost”, 27017);
DB db = mongo.getDB(“test”);

DBCollection employee = db.getCollection(“employee”);
DBCollection department = db.getCollection(“department”);

String mapEmployee = “function () {”
+ ” var output= {departmentid : this.department,firstname:this.name.first, lastname:this.name.last , department:null}”
+ ” emit(this.department, output);”
+ ” };”;

String mapDepartment = “function () {”
+ ” var output= {departmentid : this.departmentNewId,firstname:null, lastname:null , department:this.department}”
+ ” emit(this.departmentNewId, output);”
+ ” };”;

String reduceF = “function(key, values) {”
+ ” var outs = {firstname:null, lastname:null , department:null};”
+ ” values.forEach(function(v){”
+ ” if(outs.firstname ==null){”
+ ” outs.firstname = v.firstname”
+ ” }”
+ ” if(outs.lastname ==null){”
+ ” outs.lastname = v.lastname”
+ ” }”
+ ” if(outs.department ==null){”
+ ” outs.department = v.department”
+ ” } ” + ” });”
+ ” return outs;” + “};”;

MapReduceCommand cmd1 = new MapReduceCommand(employee, mapEmployee,
reduceF, “reducedCol”, MapReduceCommand.OutputType.REPLACE,
null);
MapReduceCommand cmd2 = new MapReduceCommand(department,
mapDepartment, reduceF, “reducedCol”,
MapReduceCommand.OutputType.REPLACE, null);

MapReduceOutput out1 = employee.mapReduce(cmd1);
MapReduceOutput out2 = department.mapReduce(cmd2);

for (DBObject o1 : out1.results()) {
System.out.println(o1.toString());
}

for (DBObject o2 : out2.results()) {
System.out.println(o2.toString());
}

} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Sacha says:

November 20, 2014 at 3:16 AM

This does to work if there is more than one employee in the same department, as all of these employees are reduced to one single row with the (now unique) department’s id.
Tim Arheit says:

January 25, 2015 at 7:40 AM

Looking around for a simpler way to implement joins I ran into your site. Good information. I also ran into what appears to be a new utility that claims to allow SQL like querys (including joins) to be run against Mongo. Definately going to give it a try when I get a chance at work. See Slamdata.com.. It will be interesting what it’s ‘compile’ option generates for a Join.
Confused says:

February 25, 2015 at 3:19 PM

Not working for Springs. Says
SEVERE: Servlet.service() for servlet [dispatcher] in context with path [/SpringMongo] threw exception [Request processing failed; nested exception is com.mongodb.CommandFailureException: { “serverUsed” : “localhost/127.0.0.1:27017” , “errmsg” : “exception: SyntaxError: Unexpected identifier” , “code” : 16722 , “ok” : 0.0}] with root cause
com.mongodb.CommandFailureException: { “serverUsed” : “localhost/127.0.0.1:27017” , “errmsg” : “exception: SyntaxError: Unexpected identifier” , “code” : 16722 , “ok” : 0.0}
Asya says:

April 26, 2015 at 7:17 PM

Sachs is correct, this code is wrong and it doesn’t do proper join – it won’t work when you have more than one employee per department.
Alkan Arslan says:

May 11, 2015 at 1:14 PM

Asya is right! It doesn’t work for “join”
Parag Nair (@paragnair) says:

May 21, 2015 at 9:53 PM

This is only going to work if only 1 employee belongs to a department (highly unlikely). As soon as you have more than 1 employees for a department, the result only considers the first employee.
Raja says:

August 21, 2015 at 10:13 PM

people these days want perfect suited code snippet to their requirement so that directly they can copy past the code ..
Saleem Ansari says:

April 14, 2017 at 6:48 PM

Hi,

Is there any other way for same like in sql join