Re: Join operation in MongoDB 2.6

From: William Byrne III <william.byrne@xxxxxxxxxxx>
To: mongodb-user <mongodb-user@xxxxxxxxxxxxxxxx>
Date: Sun, 1 May 2016 16:38:47 -0700 (PDT)
Why ads?


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 <https://docs.mongodb.org/manual/core/data-model-design/> 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 
<https://www.mongodb.com/blog/post/joins-and-other-aggregation-enhancements-coming-in-mongodb-3-2-part-1-of-3-introduction
added in MongoDB 3.2 is the $lookup operator 
<https://docs.mongodb.org/manual/reference/operator/aggregation/lookup/>. 
That makes doing a left outer equality join (similar to my code above) much 
simpler to code.

III
------------------------------

William Byrne III


-- 
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.org/manual/support/
--- 
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@xxxxxxxxxxxxxxxx.
To post to this group, send email to mongodb-user@xxxxxxxxxxxxxxxx.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/cee98a09-78e0-4289-9254-1fdf509b03b1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Why ads?