MongoDB join two collections

In this MongoDB tutorial, We are going to learn “How to join two collections into MongoDB”. We will also cover this with different operations with examples. These are the following topics that we are going to cover in this tutorial:

  • What is used for joining the collection in MongoDB?
  • MongoDB join two collections query
  • MongoDB join two collections
  • How MongoDB join two collections with where clause?
  • MongoDB join two collections with an array
  • MongoDB join two collections by id
  • How MongoDB join two collections by using compass
  • MongoDB join two collections from different database
  • MongoDB join two collections using python

What is used for joining the collection in MongoDB?

In MongoDB, the $lookup (aggregation) function allows us to join the documents on collections that reside in the same database.

The $lookup(aggregation) is a stage that makes a left outer join with another collection and assists to filters the documents from joined documents.

If documents come from the “joined” collection, the $lookup (aggregation) function will return the documents in the form of a sub-array of the target original collection.

Read: MongoDB group by multiple fields

MongoDB join two collections query

In MongoDB, we can combine data of multiple collections into one through the $lookup aggregation stage.

In this, you have to specify which collection you want to join with the current collection and select the field that matches in both the collection.

Syntax:

This is the simple syntax or query that is used to join two collections.

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

The $lookup function takes a document with these fields:

FieldsDescription
fromIt specifies the collection in the same database to perform the join with.
localFieldThis is used to an equality match on the localField to the foreignField from the documents of the collection.
foreignFieldThis is used to an equality match on the foreignField to the localField from the input documents.
asIn this, we specify the name of the new array field to add to the input documents and the new array field contains the matching documents from the collection.

These are some of the fields that we have to use when we use the $lookup function.

Read: Import CSV into MongoDB

MongoDB join two collections

In MongoDB, We use the $lookup(aggregation) function for joining the two collections. This function allows us to join the documents of collections that reside in the same database. You will more understand with the help of the examples.

Example:

In this example, we create a sample dataset and insert some documents

db.address.insertMany(
    [
        {
            "name": "Bob",
            "blk_no": 22,
            "street" : "dewey street",
            "city" : "olongapo"
        },
        {
            "name": "Jack",
            "blk_no": 25,
            "street" : "gordon street",
            "city" : "olongapo"
        }
    ]
);
{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("613594cdb59313217373673c"),
                ObjectId("613594cdb59313217373673d")
        ]
}

We inserted some documents into the address collection.

Now, we’ll insert some documents into another collection:

db.userInfo.insertMany(
    [
        {
            "contact_name": "Bob",
            "age": 27,
            "sex" : "male",
            "citizenship" : "Filipino"
        },
        {
            "contact_name": "Jack",
            "age": 22,
            "sex" : "male",
            "citizenship" : "Filipino"
        }
    ]
);
{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("613594dbb59313217373673e"),
                ObjectId("613594dbb59313217373673f")
        ]
}

Here, we inserted some documents into the userInfo collection.

Note that, name field in the address collection has the same values as the contact_name in the userInfo collection.

Now, we apply the $lookup function and find the result as an equality match :

db.userInfo.aggregate([
    { $lookup:
        {
           from: "address",
           localField: "contact_name",
           foreignField: "name",
           as: "address"
        }
    }
]).pretty();

Output:

{
        "_id" : ObjectId("613594dbb59313217373673e"),
        "contact_name" : "Bob",
        "age" : 27,
        "sex" : "male",
        "citizenship" : "Filipino",
        "address" : [
                {
                        "_id" : ObjectId("613594cdb59313217373673c"),
                        "name" : "Bob",
                        "blk_no" : 22,
                        "street" : "dewey street",
                        "city" : "olongapo"
                }
        ]
}
{
        "_id" : ObjectId("613594dbb59313217373673f"),
        "contact_name" : "Jack",
        "age" : 22,
        "sex" : "male",
        "citizenship" : "Filipino",
        "address" : [
                {
                        "_id" : ObjectId("613594cdb59313217373673d"),
                        "name" : "Jack",
                        "blk_no" : 25,
                        "street" : "gordon street",
                        "city" : "olongapo"
                }
        ]
}

Here, you can see in the output we joined two collections where we match the equality in the name and contact_name field. This is the simplest way of joining the two collections of the same database.

Read: MongoDB drop collection

How MongoDB join two collections with where clause?

In MongoDB, for joining the two collections with where clause then we use the aggregate() method. This will allow us to correlate the subquery between the two collections.

We also use the pipeline operator to apply the condition. Let’s understand with the help of an example:

Example:

In this example, we create two different collections and after that, we join using $lookup aggregation by applying the where clause.

How MongoDB join two collections with where clause
MongoDB insert documents into the collections

Here, we create two collections orders and warehouses and stored some documents in them. Now, we apply aggregation operation to join both the collections.

MongoDB join two collections with where clause
MongoDB join two collections with where clause

You can see for the where clause condition, we use pipeline function and apply the condition to check stock_item equal to order_item and then if instock is greater than equal to order_qty then add with that item as a stockdata.

This is the simplest way of applying where clause with join the collections. There are some other ways of doing this as well that you can learn from yourself by doing more practice.

Read: How to store images in MongoDB

MongoDB join two collections of an array

In MongoDB, The $lookup function is used to join two collections of an array when the localField is an array, you match the array elements against foreignFields without the $unwind stage.

Example:

In this example, we create two different collections and stored some documents

> db.classes.find()
{ "_id" : 1, "title" : "Reading is ...", "enrollmentlist" : [ "tom", "peter", "arky" ], "days" : [ "M", "W", "F" ] }
{ "_id" : 2, "title" : "But Writing ...", "enrollmentlist" : [ "sam", "arky" ], "days" : [ "T", "F" ] }

Here, we inserted some documents into the classes collection.

>  db.members.find()
{ "_id" : 1, "name" : "arky", "joined" : ISODate("2019-05-01T00:00:00Z"), "status" : "A" }
{ "_id" : 2, "name" : "kim", "joined" : ISODate("2020-05-01T00:00:00Z"), "status" : "D" }
{ "_id" : 3, "name" : "sam", "joined" : ISODate("2020-10-01T00:00:00Z"), "status" : "A" }
{ "_id" : 4, "name" : "panda", "joined" : ISODate("2021-10-11T00:00:00Z"), "status" : "A" }
{ "_id" : 5, "name" : "peter", "joined" : ISODate("2021-12-01T00:00:00Z"), "status" : "A" }
{ "_id" : 6, "name" : "tom", "joined" : ISODate("2021-12-01T00:00:00Z"), "status" : "D" }

Here, these are some documents we inserted into the member collection. Now, we apply aggregation operation and join documents of the members collection with classes collection.

db.classes.aggregate([
   {
      $lookup:
         {
            from: "members",
            localField: "enrollmentlist",
            foreignField: "name",
            as: "enrollee_info"
        }
   }
])

Here we apply $lookup aggregation on member collection using the field enrollmentlist of an array and merge the two collections with foreignField name and that displays the below output.

Output:

{
        "_id" : 1,
        "title" : "Reading is ...",
        "enrollmentlist" : ["tom","peter","arky"],
        "days" : ["M","W","F"],
        "enrollee_info" : [
                {
                        "_id" : 1,
                        "name" : "arky",
                        "joined" : ISODate("2019-05-01T00:00:00Z"),
                        "status" : "A"
                },
                {
                        "_id" : 5,
                        "name" : "peter",
                        "joined" : ISODate("2021-12-01T00:00:00Z"),
                        "status" : "A"
                },
                {
                        "_id" : 6,
                        "name" : "tom",
                        "joined" : ISODate("2021-12-01T00:00:00Z"),
                        "status" : "D"
                }
        ]
}
{
        "_id" : 2,
        "title" : "But Writing ...",
        "enrollmentlist" : ["sam","arky"],
        "days" : ["T","F"],
        "enrollee_info" : [
                {
                        "_id" : 1,
                        "name" : "arky",
                        "joined" : ISODate("2019-05-01T00:00:00Z"),
                        "status" : "A"
                },
                {
                        "_id" : 3,
                        "name" : "sam",
                        "joined" : ISODate("2020-10-01T00:00:00Z"),
                        "status" : "A"
                }
        ]
}

So when you have an array of fields in documents and you want to merge the collections then this way you can easily join the collections.

Read: MongoDB group by count

MongoDB join two collections by id

In this topic, you will learn how you can join two collections by id. As you already know that for joining the collection we use the $lookup aggregation.

Here, I’ll solve an example that will help you to understand how to join collection by their id in MongoDB.

Example:

In this example, we create two different collections and after that, we join the collection by id.

MongoDB join two collections by id
MongoDB insert documents into the collection

Here, we created two collections orders and items and inserted some documents into them.

Now, we apply the $lookup aggregation operation to join both the collection by their id.

MongoDB join two collections by  their id
MongoDB join two collections by their id

As you know there will be 1 to 1 relationship you can unwind $loookup results to have one embedded item for each other. Then this will display the output by merging the collection by their id.

Read: Import JSON and insert JSON into MongoDB

How MongoDB join two collections by using compass

In MongoDB compass, For joining the two collections of a database we use the $lookup aggregation function. If you want to see how we can perform then you will more understand with help of an example.

Example:

In this example, we’ll learn how to use $lookup aggregation in MongoDB compass to merge two collections of a database.

Now, follow the below steps to apply the aggregation operation in compass:

  • Open MongoDB compass
MongoDB join collections by using compass
Open MongoDB compass
  • Create a new database and collection if you want
How MongoDB join two collections by using compass
How MongoDB join two collections by using compass

Here, I created two collections geners and movies into the test database and also inserted some documents into both the collections.

  • Now we apply the aggregation function to merge both the collection.
  • Click on the Aggregation right side of the Documents.
MongoDB join two collections by using compass
Apply $lookup aggregation using MongoDB compass

After selecting aggregation at the bottom, there is a “pipeline stage” where a dropdown list and where you have to select the operation which you want to perform and here we select the $lookup function.

In this function, you have to provide some fields of the collection. In from field write the collection name which you want to join(movies)and in localField and foreignField write the field of local collection(genrelist) and field of “form collection”(type) respectively and in as write output array field(movie).

  • After applying aggregation, you can see the output right side of the input.

This is the simplest way of applying the $lookup aggregation using MongoDB compass. Now, if you do more practice with MongoDB compass then you will more understand the aggregation operations and be more familiar with the compass environment.

Read: How to check if MongoDB is installed + MongoDB Version

MongoDB join two collections from different database

In MongoDB, when we join two collections in the same database then we do with the help of $lookup aggregation. This will makes a left outer join with another collection and assists to filters the documents from joined documents.

But there is no concept of joining the two collections from different databases in MongoDB so we can not join them.

Read: MongoDB sort by date

MongoDB join two collections using python

In python, we can also perform MongoDB aggregation operations. The use of aggregation operation is to combinations the values of multiple documents concurrently and implement a variety of operations on group data.

Here, we learn how to join the two collections of the same database using python.

Example:

In this example, we join the two collections using python

MongoDB join two collections using python
MongoDB insert documents into the collection

Here, we created groceries and stock two collections earlier and inserted some documents into them.

Now, apply $lookup aggregation and join both the collections using python.

MongoDB join two collections by using python
MongoDB join two collections by using python

Here first, we make the connection between Python and MongoDB by using the pymongo library. After that access the database by using the myclient class and then use $lookup aggregation to join the collection as we have done in the MongoDB database. This will display the following output:

output:

using python join two collections of MongoDB
Output after joining two collections
using python

This way you can join the collections of the database by using python.

You may also like to read the following articles.

In this tutorial, you have learned How to join two collections into MongoDB in different operations with examples. These are the following topics that we covered in this tutorial:

  • What is used for joining the collection in MongoDB?
  • MongoDB join two collections query
  • MongoDB join two collections
  • How MongoDB join two collections with where clause
  • MongoDB join two collections with an array
  • MongoDB join two collections by id
  • How MongoDB join two collections by using compass
  • MongoDB join two collections from different database
  • MongoDB join two collections using python

Leave a Comment