![\"\"](\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2018\/07\/Screen-Shot-2018-07-13-at-1.24.12-PM-300x157.png\")
<\/p>\n<\/p>\n
As NoSQL databases evolved, each added higher level APIs or languages to help programmers to complex things easily. SQL, having done that for relational data, showed the way. In SQL, developers say WHAT needs to be done and the database engine figures out the HOW. \u00a0HOW is the efficient procedure\/algorithm to execute the statement. <\/span>Select, join and project are the basic operations SQL processing. Even in NoSQL systems when you model data without much normalization, you still need to join a collection of objects. \u00a0Customers with orders, orders with inventory, inventory with suppliers, suppliers with credits and so forth. Hence, Couchbase N1QL has supported join operations since its first release. \u00a0Following that, MongoDB, in version 3.2, added $lookup operator to the aggregation framework to perform the join operations. <\/span><\/p>\n Without an expressive and high-performance query feature, application developers have to do it within the application or export the data to a system that does it. \u00a0Both expensive propositions. <\/span><\/p>\n In this article, we will be comparing Couchbase vs. MongoDB and their differing approaches to join JSON documents. Specifically, we do a comparative study of using joins for MongoDB collections versus how we can execute them in Couchbase. Joins are unsupported in Cassandra CQL and DynamoDB natively.\u00a0 Developers must do the work themselves or use other layers like Spark or Amazon EMR to achieve the same result. So, we won\u2019t cover them in this article<\/span>.<\/span><\/p>\n Joins in Couchbase<\/span><\/p>\n Couchbase introduced INNER and LEFT OUTER joins starting with Couchbase 4.0 (2015). This supported joins of in a child-to-parent relationship. \u00a0Children documents (e.g. Orders) can be joined with parent documents (e.g. customer). In 4.5 (2016), Couchbase introduced <\/span>index joins<\/span><\/a> to query from parent to child joins. \u00a0In both cases, there was an implied attribute-value to document-key equality predicate, specified by the ON KEY clause. \u00a0<\/span><\/p>\n Couchbase 5.5 has ANSI standard SQL extended for JSON. \u00a0It supports INNER JOIN, LEFT OUTER JOIN and limited RIGHT OUTER join.\u00a0 We’ll be using examples based on Couchbase 5.5.<\/span><\/p>\n Couchbase joins documentation: <\/span>https:\/\/developer.couchbase.com\/documentation\/server\/5.5\/n1ql\/n1ql-language-reference\/from.html<\/span><\/a><\/p>\n MongoDB Joins and Collections:<\/span><\/p>\n Joins are supported via the $lookup operator within the aggregation framework. <\/span><\/p>\n Following are excerpts from the MongoDB documentation.<\/span><\/p>\n New in version 3.2.<\/span><\/i><\/p>\n Performs a left outer join to an unsharded collection in the <\/span>same<\/span><\/i> database to filter in documents from the \u201cjoined\u201d collection for processing. To each input document, the <\/span>$lookup<\/span><\/a> stage adds a new array field whose elements are the matching documents from the \u201cjoined\u201d collection. The <\/span>$lookup<\/span><\/a> stage passes these reshaped documents to the next stage.<\/span><\/p>\n Eliot Horowitz, MongoDB CTO, <\/span>said<\/span><\/a>: “MongoDB aggregation is similar to Unix pipeline. The output of one stage goes into another\u2026.[it\u2019s] very procedural. Lets you think about in a very procedural way.”<\/span><\/p>\n MongoDB $lookup : <\/span>https:\/\/docs.mongodb.com\/manual\/reference\/operator\/aggregation\/lookup\/<\/span><\/a><\/p>\n Interested in learning more on JOINs? \u00a0Read the article by Lukas Eder. https:\/\/dzone.com\/articles\/a-probably-incomplete-comprehensive-guide-to-the-many-different-ways-to-join-tables-in-sql<\/a><\/strong><\/p>\n High Level Comparison between Couchbase N1Ql and MongoDB.<\/span><\/p>\n Couchbase N1QL: Supports INNER JOIN, LEFT OUTER JOIN and limited RIGHT OUTER JOIN.\u00a0 The query language, like SQL, is declarative. Developers write, tools generate the query to the N1QL syntax.\u00a0 The engine figures out the plan and executes the query.<\/span><\/p>\n MongoDB: Supports LEFT OUTER JOIN for scalar values only.\u00a0 The design of the joins into the MongoDB query language is done help write the query and process data in a procedural way.\u00a0<\/span><\/p>\n Implication<\/span>:<\/span> <\/span><\/p>\n Examples:<\/strong> <\/span><\/p>\n We use the simple travel-sample model and data. \u00a0Here are the details of the model data. <\/span>https:\/\/developer.couchbase.com\/documentation\/server\/4.5\/travel-app\/travel-app-data-model.html<\/span><\/a><\/p>\n We simply exported the data from Couchbase and imported it to a mongo database called travel-sample. \u00a0In MongoDB, the 5 different types of the document (landmark, route, airline, airport, hotel) are stored in 5 collections with respective names.<\/span><\/p>\n Example 1<\/span>: LEFT OUTER JOIN with ON clause on scalar values.<\/span><\/span><\/p>\n Couchbase N1QL<\/span><\/p>\n MongoDB Query<\/span><\/p>\n Observations:<\/span><\/p>\n This is a fairly simple left outer join query joining two collections and then simply counting the total number of documents produced.\u00a0 Notice, unlike N1QL (and SQL), in MongoDB, you’d still have to group the resultset to get the count, even if you have a single group.<\/p>\n Example 2: List the airports and landmarks in the same city, ordered by the airports.<\/span><\/p>\n Couchbase N1QL:<\/span><\/p>\n MongoDB:<\/span><\/p>\n Observations:<\/span><\/p>\n As expected, the MongoDB join query is procedural and you’ve to understand the execution plan and write code for each stage.<\/p>\n Example 3: Starting from San Francisco, find all the destination airports (those have routes from SFO).<\/span><\/p>\n Couchbase N1QL<\/span><\/p>\n MongoDB:<\/span><\/p>\n Observations:<\/span><\/p>\n Example 4: Find all the hotels and landmarks in Yosemite.\u00a0 Hotels should have <\/span>altleast 5 likes.<\/span><\/p>\n Couchbase N1QL<\/span><\/p>\n MongoDB:<\/span><\/p>\n Observation:<\/span><\/p>\n Example 5: Find all the hotels and landmarks in Yosemite.\u00a0 Hotels should have at least 5 likes.<\/span><\/p>\n This is just like Example 4, but do it faster!<\/p>\n Couchbase N1QL<\/span><\/p>\n Observation:<\/span><\/p>\n The default join method in Couchbase N1QL is nested loop join.\u00a0 This works fine when you have a smaller number of documents involved on each side of the join.\u00a0 When you have a\u00a0larger data set, typically in reporting queries, nested loop join slows down.\u00a0 Couchbase N1QL has hash joins and this speeds up joins significantly.\u00a0 When each side of join has thousands of documents to millions of documents, the speed increase can be 2x to 20x or more.\u00a0See the detailed Couchbase blog<\/a> on ANSI Joins for more information.<\/p>\n From the documentation and explain plan, it’s unclear what join method MongoDB uses. Some of the blogs indicate that they’ve used a nested loop join to implement the $lookup operator.<\/p>\n Summary:<\/strong><\/p>\n <\/p>\n
\n
\n\n
SELECT count(*)\r\nFROM `travel-sample` route\r\n LEFT OUTER JOIN `travel-sample` airline\r\n ON (route.airlineid = META(airline).id)\r\nWHERE route.type = 'route';\r\n<\/pre>\n
db.route.aggregate([\r\n {\r\n $lookup:\r\n {\r\n from:\"airline\",\r\n localField: \"airlineid\",\r\n foreignField: \"_id\",\r\n as: \"airline_docs\"\r\n }\r\n },\r\n { $group: { _id: null, myCount: { $sum: 1 } } },\r\n { $project: { _id: 0 } }\r\n ]);\r\n<\/pre>\nSELECT landmark.name AS Landmark_Name,\r\n MIN(airport.airportname) AS Airport_Name,\r\n MIN(airport.tz) AS Landmark_Time\r\nFROM `travel-sample` airport INNER JOIN `travel-sample` landmark\r\n ON airport.city = landmark.city\r\nWHERE landmark.country = \"United States\"\r\n AND airport.type = \"airport\"\r\n AND landmark.type = \"landmark\"\r\nGROUP BY landmark.name\r\nORDER BY Airport_Name\r\n<\/pre>\n
db.airport.aggregate([\r\n {\r\n $lookup:\r\n {\r\n from:\"landmark\",\r\n localField: \"city\",\r\n foreignField: \"city\",\r\n as: \"aplm_docs\"\r\n }\r\n },\r\n {\r\n $match: {\"airline_docs\": {$ne: []}}\r\n },\r\n { $unwind: { path: \"$aplm_docs\", preserveNullAndEmptyArrays: true }},\r\n { $group: {\r\n _id: \"$aplm_docs.name\",\r\n Airport_Name: { $min: \"$airportname\" } ,\r\n Landmark_Time: { $min: \"$tz\"}\r\n }\r\n },\r\n { $sort : { Airport_Name: 1 } },\r\n { $project: { _id: 1, Airport_Name:1, Landmark_Time:1 } }\r\n ]);\r\n<\/pre>\n\n
SELECT DISTINCT route.destinationairport\r\nFROM `travel-sample` airport JOIN `travel-sample` route\r\n ON (airport.faa = route.sourceairport AND route.type = \"route\")\r\nWHERE airport.type = \"airport\"\r\n AND airport.city = \"San Francisco\"\r\n AND airport.country = \"United States\"\r\nORDER BY route.destinationairport\r\n<\/pre>\n
db.airport.aggregate([\r\n {\r\n $match: {\r\n $and: [\r\n {\"type\": \"airport\"},\r\n { city: \"San Francisco\"},\r\n { \"country\": \"United States\"}\r\n ]\r\n }\r\n },\r\n {\r\n $lookup:\r\n {\r\n from:\"route\",\r\n let: { rfaa : \"$faa\"},\r\n pipeline: [\r\n { $match:\r\n { $expr:\r\n { $and:\r\n [\r\n { $eq: [\"$sourceairport\", \"$$rfaa\"]} ,\r\n { $eq: [\"$type\", \"route\"] }\r\n ]\r\n\r\n }\r\n }\r\n }\r\n ],\r\n as: \"airline_docs\"\r\n }\r\n },\r\n { $match: {\"airline_docs\": {$ne: []}} },\r\n { $unwind: { path: \"$airline_docs\", preserveNullAndEmptyArrays: true }},\r\n { $project: { _id:0, \"airline_docs.destinationairport\" : 1 }},\r\n { $group: {\r\n _id : \"$airline_docs.destinationairport\"\r\n }\r\n },\r\n { $sort: { _id : 1 }},\r\n ]);\r\n<\/pre>\n\n
SELECT hotel.name hotel_name, landmark.name landmark_name, landmark.activity\r\nFROM `travel-sample` hotel INNER JOIN `travel-sample` landmark\r\n ON (hotel.city = landmark.city \r\n AND hotel.country = landmark.country \r\n AND landmark.type = \"landmark\")\r\nWHERE hotel.type = \"hotel\" \r\n AND hotel.title like \"Yosemite%\" \r\n AND array_length(hotel.public_likes) > 5;\r\n<\/pre>\n
db.hotel.aggregate([\r\n { $match: { title: { $regex: \/^Yosemite\/ } }, },\r\n {\r\n $lookup:\r\n {\r\n from:\"landmark\",\r\n let: { hcity : \"$city\", hcountry : \"$country\"},\r\n pipeline: [\r\n { $match:\r\n { $expr:\r\n { $and:\r\n [\r\n { $eq: [\"$city\", \"$$hcity\"]} ,\r\n { $eq: [\"$country\", \"$$hcountry\"] }\r\n ]\r\n }\r\n }\r\n }\r\n ],\r\n as: \"hotel_lm_docs\"\r\n }\r\n },\r\n { $match : {\"hotel_lm_docs\": { $ne: [] }}},\r\n { $project: {_id:0, hname: \"$name\", public_likes: 1, hotel_lm_docs:1}},\r\n { $unwind: { path: \"$hotel_lm_docs\", preserveNullAndEmptyArrays: true }},\r\n { $project: { _id: 1, hname : 1 , \"hotel_lm_docs.name\" : 1, \"hotel_lm_docs.name\" : 1, \"hotel_lm_docs.activity\" : 1, mt5 : {$gt: [ {$size: \"$public_likes\"}, 5]}}},\r\n { $match: { mt5 : true } },\r\n { $project: {_$id:0}}\r\n ]);\r\n<\/pre>\n\n
SELECT hotel.name hotel_name, landmark.name landmark_name, landmark.activity\r\nFROM `travel-sample` hotel INNER JOIN `travel-sample` landmark USE HASH(build)\r\n ON (hotel.city = landmark.city \r\n AND hotel.country = landmark.country \r\n AND landmark.type = \"landmark\")\r\nWHERE hotel.type = \"hotel\"\r\n AND hotel.title like \"Yosemite%\" \r\n AND array_length(hotel.public_likes) > 5;\r\n<\/pre>\n