{"id":575,"date":"2017-08-30T10:38:04","date_gmt":"2017-08-30T17:38:04","guid":{"rendered":"https:\/\/www.kenwalger.com\/blog\/?p=575"},"modified":"2017-10-16T04:40:48","modified_gmt":"2017-10-16T11:40:48","slug":"performing-joins-mongodb-lookup","status":"publish","type":"post","link":"https:\/\/www.kenwalger.com\/blog\/nosql\/mongodb\/performing-joins-mongodb-lookup\/","title":{"rendered":"Performing joins in MongoDB with $lookup"},"content":{"rendered":"

Eventually, it seems, looking up data in multiple MongoDB<\/a> collections at the same time becomes necessary. Okay,\u00a0necessary<\/em>, might be strongly phrased. “Very helpful” may be better. Until version 3.2 of MongoDB doing joins wasn’t possible, but with that version came the $lookup<\/code> aggregation stage operator<\/a>.\u00a0The introduction of $lookup<\/code> allows for left outer joins to be performed on collections in the same database which\u00a0are\u00a0not\u00a0sharded<\/a>.<\/p>\n

For our data for this post let’s utilize the concept of recipes. I’ll lay out some basic traditional SQL database tables and walk through the SQL syntax for a join.<\/p>\n

\"Recipe<\/p>\n

SQL Syntax<\/h3>\n

Let’s have a quick look at what a left outer join looks like in SQL with a couple of different syntaxes in SQL.<\/p>\n

Standard SQL<\/h6>\n
SELECT recipe_type.decription, recipes.title\nFROM recipe_type\nLEFT OUTER JOIN recipes\nON recipe_type.id = recipes.recipe_type_id\n<\/pre>\n
Oracle<\/h6>\n
SELECT recipe_type.description, recipes.title\nFROM recipe_type, recipes\nWHERE recipe_type.id = recipes.recipe_type_id(+)\n<\/pre>\n
The results of a left outer join for these two tables will contain all<\/em> of the rows from our “left” table (recipe_type).<\/p>\n
$lookup for Joins<\/h3>\n
\"Concept
Concept of a Left Outer Join<\/figcaption><\/figure>\n
Our join from $lookup<\/code> then should be the same from a conceptual standpoint as our SQL cousin. Assuming we have a collection of recipe types and one of recipes\u00a0our $lookup<\/code> stage would look like the following:<\/p>\n
{\n  $lookup:\n    {\n      from: \"recipes\",\n      localField: \"id\",\n      foreignField: \"recipe_type_id\",\n      as: \"recipe_categories\"\n    }\n}\n<\/pre>\n
Our $lookup<\/code> stage then is doing an equality match between the two documents based on the id<\/code> and recipe_type_id<\/code> fields. It will add a new array field to the document in our aggregation pipeline document and passes it to the next stage of the pipeline.<\/p>\n
Now, given our knowledge of schema design<\/a> and document models<\/a> in MongoDB we may not have a need for this exact join as these two collections of data\u00a0might<\/em> be embedded in one or the other collection. However, depending on your data access patterns and application needs, the collections may be utilizing references.<\/p>\n
Wrap Up<\/h3>\n
I hope this provided a useful, and quick, introduction to the $lookup<\/code> aggregation stage and how it is possible to do joins in MongoDB. While you may not find yourself using it all of the time, it is a great tool to have at the ready when it is needed.<\/p>\n
There are several MongoDB specific terms in this post. I created a MongoDB Dictionary<\/a> skill for the Amazon Echo<\/a>\u00a0line of products. Check it out and you can say “Alexa, ask MongoDB what is a document?” and get a helpful response.<\/p>\n
\n
Follow me on Twitter @kenwalger<\/a> to get the latest updates on my postings.<\/em><\/p>\n\"Facebook\"<\/a>\"twitter\"<\/a>\"reddit\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>","protected":false},"excerpt":{"rendered":"
Eventually, it seems, looking up data in multiple MongoDB collections at the same time becomes necessary. Okay,\u00a0necessary, might be strongly phrased. “Very helpful” may be better. Until version 3.2 of MongoDB doing joins wasn’t possible, but with that version came the $lookup aggregation stage operator.\u00a0The introduction of $lookup allows for left outer joins to be … Continue reading “Performing joins in MongoDB with $lookup”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":578,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"pmpro_default_level":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[1023,1022,1024],"yst_prominent_words":[891,873,898,897,893,894,298,882,878,879,884,87,880,885,889,890,883,875,876,892],"class_list":["post-575","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mongodb","tag-lookup","tag-joins","tag-v3-2","pmpro-has-access"],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/uploads\/2017\/08\/feature-2-e1504105105445.png?fit=125%2C125&ssl=1","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8lx70-9h","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/posts\/575","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/comments?post=575"}],"version-history":[{"count":3,"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/posts\/575\/revisions"}],"predecessor-version":[{"id":586,"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/posts\/575\/revisions\/586"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/media\/578"}],"wp:attachment":[{"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/media?parent=575"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/categories?post=575"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/tags?post=575"},{"taxonomy":"yst_prominent_words","embeddable":true,"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/yst_prominent_words?post=575"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}