Can we do joins in MongoDB?

Reading Time: 3 minutes

MongoDB is a NoSQL document database designed for ease of development and scaling. The best part about using a relational DBMS is that we can perform a wide range of relational queries on it. Doing joins on different tables is very easy. But, when we talk about MongoDB, the way data is stored here is quite different from any relational DBMS.

How data is Stored in MongoDB?

In MongoDB, databases hold collections of documents.

A record in MongoDB is a document, which is a data structure composed of field and value pairs. MongoDB documents are similar to JSON objects. The values of fields may include other documents, arrays, and arrays of documents.

Here, in this blog, our major focus is on how we can perform joins on 2 different collections.

Consider two collections ‘cab’ and ‘employee’. The collections can be created in the following ways:

db.createCollection("cab")
db.createCollection("employee")


Once, our collections are ready, let’s try to insert records to them.

For cab:

db.cab.insert({cabId:1,cabName:'Innova',driver:'Ramesh',startDestination:'delhi',endDestination:'gurgaon'})

db.cab.insert({cabId:2,cabName:'Ertiga',driver:'Suresh',startDestination:'gurgaon',endDestination:'delhi'})


For employee:

db.employee.insert({empId:1,name:'jaya',cabId:1})
db.employee.insert({empId:2,name:'mahesh',cabId:1})
db.employee.insert({empId:3,name:'rahul',cabId:2})
db.employee.insert({empId:4,name:'radhika',cabId:2})


In mongo, we have a concept of $lookup(aggregation) that allow us to perform left outer join.

$lookup (aggregation):

This Performs a left outer join to an unsharded collection in the same database to filter in documents from the “joined” collection for processing. To each input document, the $lookup stage adds a new array field whose elements are the matching documents from the “joined” collection. The $lookup stage passes these reshaped documents to the next stage.

LeftOuterJoin

Syntax:

{
 $lookup:
 {
 from: <collection to join>,
 localField: <field from the input documents>,
 foreignField: <field from the documents of the "from" collection>,
 as: <output array field>
 }
}

Let us try to understand various fields in the above $lookup syntax.

localField: Specifies the field from the documents input to the $lookup stage. $lookup performs an equality match on the localField to the foreignField from the documents of the from collection. If an input document does not contain the localField, the $lookup treats the field as having a value of null for matching purposes.

foreignField: Specifies the field from the documents in the from collection. $lookup performs an equality match on the foreignField to the localField from the input documents. If a document in the from collection does not contain the foreignField, the $lookup treats the value as null for matching purposes.

as: Specifies the name of the new array field to add to the input documents. The new array field contains the matching documents from the from collection. If the specified name already exists in the input document, the existing field is overwritten.

The above lookup query will correspond to the following SQL query:

SELECT *, <output array field>
FROM collection
WHERE <output array field> IN (SELECT *
                        FROM <collection to join>
                        WHERE <foreignField>= <collection.localField>);


Now, let us create an aggregation on the table cab and employee

db.employee.aggregate([{$lookup:{from:"cab",localField:"cabId",foreignField:"cabId",
as:"cab_info"}}])

We will get the following response as a result of our aggregation:

{ "_id" : ObjectId("5ce3c9e44e3927e6702f003b"), "empId" : 1, "name" : "jaya", "cabId" : 1, 
"cab_info" : [ { "_id" : ObjectId("5ce3c97b4e3927e6702f0039"), "cabId" : 1,
"cabName" : "Innova", "driver" : "Ramesh", "startDestination" : "delhi", 
"endDestination" : "gurgaon" } ] }

{ "_id" : ObjectId("5ce3c9f04e3927e6702f003c"), "empId" : 2, "name" : "mahesh", "cabId" : 1,
 "cab_info" : [ { "_id" : ObjectId("5ce3c97b4e3927e6702f0039"), "cabId" : 1,
 "cabName" : "Innova", "driver" : "Ramesh", "startDestination" : "delhi",
 "endDestination" : "gurgaon" } ] }

{ "_id" : ObjectId("5ce3ca0a4e3927e6702f003d"), "empId" : 3, "name" : "rahul", "cabId" : 2,
 "cab_info" : [ { "_id" : ObjectId("5ce3c9aa4e3927e6702f003a"), "cabId" : 2,
 "cabName" : "Ertiga", "driver" : "Suresh", "startDestination" : "gurgaon",
 "endDestination" : "delhi" } ] }

{ "_id" : ObjectId("5ce3ca174e3927e6702f003e"), "empId" : 4, "name" : "radhika", "cabId" : 2,
 "cab_info" : [ { "_id" : ObjectId("5ce3c9aa4e3927e6702f003a"), "cabId" : 2,
 "cabName" : "Ertiga", "driver" : "Suresh", "startDestination" : "gurgaon",
 "endDestination" : "delhi" } ] }

There is more on aggregation, that can be explored on the official doc on MongoDB. The link is given below under references.
I hope, you have liked my blog. If you have any doubt or any suggestions to make please drop a comment. Thanks!

References:
MongoDB official doc on $lookup(aggregation)

knoldus-blog-footer-banner

Written by 

Vinisha Sharma is a software consultant having more than 6 months of experience. She thrives in a fast pace environment and loves exploring new technologies. She has experience with the languages such as C, C++, Java, Scala and is currently working on Java 8. Her hobbies include sketching and dancing. She believes Optimism and a learning attitude is the key to achieve success in your life

Leave a Reply

Knoldus Pune Careers - Hiring Freshers

Get a head start on your career at Knoldus. Join us!