Easiest Way to Implement JOINs in MongoDB 2.4

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 .

Written by 

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

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

  2. 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,
    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()) {

    for (DBObject o2 : out2.results()) {

    } catch (Exception e) {
    // TODO Auto-generated catch block

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

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

  5. 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/” , “errmsg” : “exception: SyntaxError: Unexpected identifier” , “code” : 16722 , “ok” : 0.0}] with root cause
    com.mongodb.CommandFailureException: { “serverUsed” : “localhost/” , “errmsg” : “exception: SyntaxError: Unexpected identifier” , “code” : 16722 , “ok” : 0.0}

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

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

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

Comments are closed.