Easiest Way to Implement JOINs in MongoDB 2.4


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" } }


About Ayush Mishra

Ayush is the Sr. Software Consultant @ Knoldus Software LLP. In his 5 years of experience he has become 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. .
This entry was posted in JavaScript, MongoDB and tagged . Bookmark the permalink.

12 Responses to Easiest Way to Implement JOINs in MongoDB 2.4

  1. Pingback: An alternate way to implement JOINs in MongoDB [Update] | Knoldus

  2. 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.

  3. Pingback: Jointure de table avec MongoDB | Giwi

  4. Mohsin says:

    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();
    }
    }
    }

  5. Sacha says:

    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.

  6. Tim Arheit says:

    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.

  7. Confused says:

    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}

  8. Asya says:

    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.

  9. Alkan Arslan says:

    Asya is right! It doesn’t work for “join”

  10. 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.

  11. Raja says:

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

  12. Saleem Ansari says:

    Hi,

    Is there any other way for same like in sql join

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