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:
| Fields | Description |
|---|---|
| from | It specifies the collection in the same database to perform the join with. |
| localField | This is used to an equality match on the localField to the foreignField from the documents of the collection. |
| foreignField | This is used to an equality match on the foreignField to the localField from the input documents. |
| as | In 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" : "United States of America"
},
{
"name": "Jack",
"blk_no": 25,
"street" : "gordon street",
"city" : "New Zealand"
}
]
);
{
"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" : "United States of America"
}
]
}
{
"_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" : "New Zealand"
}
]
}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.
Here, we create two collections orders and warehouses and stored some documents in them. Now, we apply aggregation operation to join both the collections.
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 members’ 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.
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.
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
- Create a new database and collection if you want
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.
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 make a left outer join with another collection and assist 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
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.
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
This way you can join the collections of the database by using python.
You may also like to read the following articles.
- MongoDB sort by field
- Create tables in MongoDB
- Pros and cons of MongoDB
- MongoDB update the documents
- Export MongoDB to CSV
- MongoDB is not recognized as an internal or external command
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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.