Join operation in MongoDB 2.6

[Gagan Rana]

How to perform join operation in mongodb 2.6.

[William Byrne III]

Gagan,

Most MongoDB databases are very denormalised compared to their RDBMS equivalents. Related “records” are stored in the same document, so the data is already “joined”. See this discussion of embedded versus normalised data models for more on that.

However, there are still cases where documents from different collections need to be joined. As MongoDB has no server side join capability (in version 2.6), the join is performed on the client. You open a cursor with all of the relevant documents from the first collection, and for each document fetched from that cursor you open a second cursor to find all the matching documents in the second collection.

Here is a working example in javascript you can run in the mongo shell:

db.customers.insert([ {"name":"Bri", "address":"Brisbane"},
                      {"name":"Sid", "address":"Sydney"}, 
                      {"name":"Mel", "address":"Melbourne"} ]);

db.orders.insert([
 {"custname":"Bri", "date":"2016.01.01", "details":"towel, hat"}
 {"custname":"Sid", "date":"2016.01.02", "details":"cheese, crackers"},
 {"custname":"Mel", "date":"2016.01.03", "details":"umbrella"},
 {"custname":"Mel", "date":"2016.01.04", "details":"gumboots"},
 {"custname":"Bri", "date":"2016.01.07", "details":"sunscreen"},
 {"custname":"Sid", "date":"2016.01.08", "details":"coffee, biscuits"} ]);

cust_cur = db.customers.find({"name":"Sid"});
while (cust_cur.hasNext())
  { cust = cust_cur.next();
    print("\n", cust.name, "of", cust.address, "orders:");

    ord_cur = db.orders.find({"custname":cust.name});
    while (ord_cur.hasNext())
      { ord = ord_cur.next();
        print("   -", ord.date, ":", ord.details);
      };
  };

to produce this output:

 Sid of Sydney orders:
   - 2016.01.02 : cheese, crackers
   - 2016.01.08 : coffee, biscuits

Finally, one of the Aggregation Pipeline enhancements added in MongoDB 3.2 is the $lookup operator. That makes doing a left outer equality join (similar to my code above) much simpler to code.

III

---

William Byrne III

​