Implementing Joins in MongoDB using the $lookup operator
Tags: webdev, mongodb, database, tutorial
Published: 2023-02-20 16:56:14

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. 

![left-outer-join](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ji3bplrrmcw89oftyztc.png)

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.

![database connection string](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d5txwacarsiib6ukuisi.png)

On the left menu pane, you should see the `business` database and the two collections we created. 

Select the `service` collection. 

![service-collection](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xo0zeoslmnm8lcid2wpp.png)

In the `service` collection view, select the `Aggregations` tab. 

In the first stage of the `Aggregations` view, select the `$lookup` operator.


![lookup-operator-aggregation](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s1j774yh5ipud9csulb3.png)

### 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.

![filter-match-opertaor](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kh9rrailfmse82z1715p.png)
```
{
  '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.