A join is a statement used in relational database systems to combine data from two or more tables based on a commonly shared column. MongoDB is however a non-relational database system which stores data as documents in collections, which are the equivalent of tables in SQL databases.
MongoDB is a document-oriented database which is great for storing unstructured data. Though non-relational in design, MongoDB has some operators that allow us to perform some join operations. Using the $lookup operator, we'll dive into how to implement a join operation in MongoDB.
Dependencies
You'll need to have MongoDB and MongoDB compass to follow this tutorial.
MongoDB's $lookup Operator
The $lookup operator provides us with a way to perform a left-outer join.
A left-outer join is an operation that returns all data from the left table and only matching data from the right table.
In the context of MongoDB, a left-outer join returns all data from the left collection and any matching data from the right collection. If there is no matching data in the right collection, only data from the left collection is returned.
The $lookup operator is used as a stage in the aggregation pipeline. In MongoDB, aggregation is the process of analysing data by passing it through different stages of operations. A strong use for aggregation is when we want to perform analytics and create reports based on data that spans across different collections.
Left Outer Joins using the $lookup operator
Head over to your terminal and start the mongo shell with the command
mongosh
Initialize a database named business with the command
use business
Create 2 collections which we will be using to demonstrate joins implementation via the $lookup operator.
db.createCollection('company')
db.createCollection('service')
Next, we'll seed our collections with data.
db.company.insertMany([
{
"_id": "886076c1-af54-46bf-9afd-d50c50543456",
"companyName": "Thoughtstorm",
"email": "ksoanes0@un.org",
"isRegistered": true
},
{
"_id": "4f6f50a8-51bf-4435-89ed-f421707bea9e",
"companyName": "Skilith",
"email": "bovesen1@miitbeian.gov.cn",
"isRegistered": true
},
{
"_id": "bd69948e-904c-4f56-a129-945ea829108e",
"companyName": "Yozio",
"email": "mshouler2@hc360.com",
"isRegistered": true
},
{
"_id": "818d73bc-46f8-497d-9ae4-ac71508f81f1",
"companyName": "Kayveo",
"email": "mbyfield3@dell.com",
"isRegistered": false
},
{
"_id": "f6fc009a-ac8e-433a-b47e-855abe246f1b",
"companyName": "Chatterbr_idge",
"email": "lhalfacree4@github.com",
"isRegistered": false
},
{
"_id": "e15015cc-0e9a-4fd9-9246-b51dc6f22f89",
"companyName": "Gabtype",
"email": "aurlin5@yandex.ru",
"isRegistered": false
},
{
"_id": "90900c9b-3b1e-4151-829c-0ca365220188",
"companyName": "Bubbletube",
"email": "pbaynham6@printfriendly.com",
"isRegistered": false
},
{
"_id": "7a1f34b7-6232-4ec3-a554-f5b3aef31231",
"companyName": "Trilith",
"email": "skinnaird7@ifeng.com",
"isRegistered": false
},
{
"_id": "f24f0151-1ae5-4724-aa97-d43fe8d3f854",
"companyName": "Katz",
"email": "lsimeoli8@ftc.gov",
"isRegistered": true
},
{
"_id": "cef57a11-acac-416d-ab3a-096a17722a84",
"companyName": "Livetube",
"email": "hhounsom9@usatoday.com",
"isRegistered": false
}
])
db.service.insertMany([
{
"_id": "11f314d4-69b0-4abe-8bac-3025ebfc38ee",
"companyId": "886076c1-af54-46bf-9afd-d50c50543456",
"serviceName": "Rebar & Wire Mesh Install",
"isActive": true
},
{
"_id": "08166e38-399b-4c8f-a5e8-c48e397538be",
"companyId": "4f6f50a8-51bf-4435-89ed-f421707bea9e",
"serviceName": "Wall Protection",
"isActive": true
},
{
"_id": "af3e867b-633a-44b3-8a8d-9ddda2320bba",
"companyId": "bd69948e-904c-4f56-a129-945ea829108e",
"serviceName": "Epoxy Flooring",
"isActive": true
},
{
"_id": "662883be-0e0a-4e80-92cc-7a45d8984006",
"companyId": "818d73bc-46f8-497d-9ae4-ac71508f81f1",
"serviceName": "Masonry",
"isActive": true
},
{
"_id": "5bec5ef0-6449-40c5-b097-27b330415757",
"companyId": "f6fc009a-ac8e-433a-b47e-855abe246f1b",
"serviceName": "Plumbing & Medical Gas",
"isActive": true
},
{
"_id": "fe04f4f1-27e1-421b-abe8-06f415136421",
"companyId": "e15015cc-0e9a-4fd9-9246-b51dc6f22f89",
"serviceName": "Casework",
"isActive": true
},
{
"_id": "0d2db760-9b5c-4fa2-be0f-f6acb251e7de",
"companyId": "90900c9b-3b1e-4151-829c-0ca365220188",
"serviceName": "Exterior Signage",
"isActive": true
}
])
Some points about the data we just added to our collections.
- There are 10 companies. 4 out of the 10 companies are registered.
- There are 7 services offered by these companies and all services are active.
Now the Analysis
Say we want to create a report that shows us only services that offered by registered companies.
How would we go about that?
Because the data we need spans across two collections, the company and service collection, we would need to do a join to generate this report.
The common factor between these two collections is the _id of the company collection and this is referenced as the companyId in the service collection.
For the visual clarity and ease of understanding, I recommend that you use MongoDB's Compass to implement the joins between the two collections.
In Compass, connect to the business database on localhost.
On the left menu pane, you should see the business database and the two collections we created.
Select the service collection.
In the service collection view, select the Aggregations tab.
In the first stage of the Aggregations view, select the $lookup operator.
Break down of the lookup stage syntax
The $lookup stage has the syntax:
{
from: collection,
localField: field,
foreignField: field,
as: result
}
The from key represents the collection we wish to join to. In this case, it is the company collection.
The localField key represents the field/column in our input collection. We are carrying out this join operation from the service collection which is our input collection. Therefore the localField in this case will be the companyId.
The foreignField key is the field from the collection we want to join. In our case, the foreignField is the _id field in the company collection.
The as key represents a variable which will hold the result of the join operation. The result of the join operation is an array. You are free to give the variable a name of your choice.
In Compass, fill out the $lookup syntax like this
{
from: "company",
localField: "companyId",
foreignField: "_id",
as: "registeredServices"
}
In the preview pane on Compass, you can see that we have 7 documents from this join operation. This operation fetches both companies that are registered and unregistered. But we want only the services that belong to registered companies.
Adding a second stage to the aggregation pipeline
To filter the data from the first aggregation stage so that we have only services offered by registered companies, we can use the $match operator.
The $match operator filters documents based on a specified condition.
In Compass, add a second stage in the aggregation pipeline and select the $match operator.
{
'registeredServices':{
$elemMatch: {
'isRegistered': true
}
}
}
What's going on?
We use the $match operator to target the array registeredServices. Then we use the $eleMAtch operator to specify a condition that we only need objects that have the isRegistered field set as true.
Run the pipeline in Compass and you'll see that we now have only 3 documents which represents services that are offered by registered companies.
Conclusion
In this article we learned how to implement a join in MongoDB using the $lookup operator. The $lookup operator performs a left-outer join and with it we can conduct analyses on data that span across different collections in a database.
Thank you for reading this far. Feel free to share your thoughts in the comments.
Top comments (0)