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)