How to Combine Two Collections Into One Collection Using MongoDB

1. Combine Two Collections Into One Collection Using MongoDB
2. Use the $lookup Aggregate Stage to Join Two Collections Into One
3. Use the pipeline Operator to Join Two Collections Into One Based on the Specified Condition
4. Use the $unwind Operator to Flat Array Before Attaching to the Resulted Documents
5. Use the $project Filter Stage in Aggregation Queries to Join Two Collections Into One
6. Join Two Collections Using Compass (Graphical Interface of MongoDB)

Today, we will use the $lookup aggregate stage, pipeline and $unwind operators, $project filter stage, and MongoDB Compass to combine two collections into one collection.

Combine Two Collections Into One Collection Using MongoDB

We have different approaches for combining two collections into one collection using MongoDB. Some of them are given below, which we will cover in this tutorial.

1. Use the $lookup aggregate stage to join two collections
2. Use the pipeline operator to join two collections based on the specified condition
3. Use the $unwind operator to flat an array before attaching it to the resulted documents
4. Use the $project filter stage in aggregation queries to join two collections
5. Join two collections using a compass (a graphical interface of MongoDB)

For all of the above scenarios, we must have a database containing two collections (same as tables in MySQL) populated with documents (same as records in MySQL). We have done that using the following queries; you can do so also.

Create two collections named usersInformation and userAddress that reside in the users database. Further, populate them with documents as follows.

Create database and collections:

> use users
> db.createCollection('userInformation')
> db.createCollection('userAddress')

Populate the userInformation collection with two documents:

> db.userInformation.insertMany(
    [
        {
            fullname: 'Mehvish Ashiq',
            age: 30,
            gender: 'Female',
            nationality: 'Pakistani'
        },
        {
            fullname: 'James Daniel',
            age: 45,
            sex: 'male',
            nationality: 'Canadian'
        }
    ]
)

Populate the userAddress collection with two documents:

> db.userAddress.insertMany(
    [
        {
            fullname: 'Mehvish Ashiq',
            block_number: 22,
            street: 'Johar Town Street',
            city: 'Lahore'
        },
        {
            fullname: 'James Daniel',
            block_number: 30,
            street: 'Saint-Denis Street',
            city: 'Montreal'
        }
    ]
)

We use the insertMany() function to insert multiple documents. Now, we can use the commands below to see the data for both collections.

In the following code snippet, the pretty() method shows the clean and formatted output, which is easy to understand on the shell.

Display documents from the userInformation:

> db.userInformation.find().pretty()

OUTPUT:

{
        "_id" : ObjectId("628bc4a45c544feccff5a566"),
        "fullname" : "Mehvish Ashiq",
        "age" : 30,
        "gender" : "Female",
        "nationality" : "Pakistani"
}
{
        "_id" : ObjectId("628bc4a45c544feccff5a567"),
        "fullname" : "James Daniel",
        "age" : 45,
        "sex" : "male",
        "nationality" : "Canadian"
}

Display documents from the userAddress:

> db.userAddress.find().pretty()

OUTPUT:

{
        "_id" : ObjectId("628bc4ae5c544feccff5a568"),
        "fullname" : "Mehvish Ashiq",
        "block_number" : 22,
        "street" : "Johar Town Street",
        "city" : "Lahore"
}
{
        "_id" : ObjectId("628bc4ae5c544feccff5a569"),
        "fullname" : "James Daniel",
        "block_number" : 30,
        "street" : "Saint-Denis Street",
        "city" : "Montreal"
}

Both collections must be in the same database to use the $lookup aggregate stage. Once both collections are ready, we can use the various queries to join both collections’ data depending on what scenario we have.

Use the $lookup Aggregate Stage to Join Two Collections Into One

Example Code:

> db.userInformation.aggregate([
    { $lookup:
        {
           from: 'userAddress',
           localField: 'fullname',
           foreignField: 'fullname',
           as: 'address'
        }
    }
]).pretty();

OUTPUT:

{
        "_id" : ObjectId("628bc4a45c544feccff5a566"),
        "fullname" : "Mehvish Ashiq",
        "age" : 30,
        "gender" : "Female",
        "nationality" : "Pakistani",
        "address" : [
                {
                        "_id" : ObjectId("628bc4ae5c544feccff5a568"),
                        "fullname" : "Mehvish Ashiq",
                        "block_number" : 22,
                        "street" : "Johar Town Street",
                        "city" : "Lahore"
                }
        ]
}
{
        "_id" : ObjectId("628bc4a45c544feccff5a567"),
        "fullname" : "James Daniel",
        "age" : 45,
        "sex" : "male",
        "nationality" : "Canadian",
        "address" : [
                {
                        "_id" : ObjectId("628bc4ae5c544feccff5a569"),
                        "fullname" : "James Daniel",
                        "block_number" : 30,
                        "street" : "Saint-Denis Street",
                        "city" : "Montreal"
                }
        ]
}

In the MongoDB database, the $lookup aggregate stage performs the left outer join with the other collection and also filters information (data) from the joined documents. For instance, we use the query to get all users’ information with their addresses.

The $lookup function accepts four fields. First is the from field, where we specify the collection that is supposed to be joined with the other collection.

The second is the localField field. It is one of the attributes (field) from the input documents of the collection specified in the from field.

It is used to perform a match on the localField to foreignField from the collections’ documents.

Similarly, the third field named foreignField also performs the equality match on the foreignField to localField from the collections’ documents.

We write the new array’s name for the fourth field, as. See the following explanation for the $lookup aggregate stage.

Use the pipeline Operator to Join Two Collections Into One Based on the Specified Condition

Example Code:

> db.userInformation.aggregate([{
    $lookup:{
        from: 'userAddress',
        let: {full_name: '$fullname'},
        pipeline: [{
            $match: {
                $expr: {
                    $eq: ['$fullname', '$$full_name']
                }
             }
       }],
       as: 'addressInfo'
    }
}]).pretty()

OUTPUT:

{
        "_id" : ObjectId("628bc4a45c544feccff5a566"),
        "fullname" : "Mehvish Ashiq",
        "age" : 30,
        "gender" : "Female",
        "nationality" : "Pakistani",
        "addressInfo" : [
                {
                        "_id" : ObjectId("628bc4ae5c544feccff5a568"),
                        "fullname" : "Mehvish Ashiq",
                        "block_number" : 22,
                        "street" : "Johar Town Street",
                        "city" : "Lahore"
                }
        ]
}
{
        "_id" : ObjectId("628bc4a45c544feccff5a567"),
        "fullname" : "James Daniel",
        "age" : 45,
        "sex" : "male",
        "nationality" : "Canadian",
        "addressInfo" : [
                {
                        "_id" : ObjectId("628bc4ae5c544feccff5a569"),
                        "fullname" : "James Daniel",
                        "block_number" : 30,
                        "street" : "Saint-Denis Street",
                        "city" : "Montreal"
                }
        ]
}

We can use the pipeline operator with $lookup when we want to join two collections based on a specific condition (just like we use the WHERE clause in MySQL).

For instance, we are joining the collections where the fullname from userAddress is equal to the fullname in userInformation.

Use the $unwind Operator to Flat Array Before Attaching to the Resulted Documents

Example Code:

> db.userInformation.aggregate([
    { $lookup:
        {
           from: 'userAddress',
           localField: 'fullname',
           foreignField: 'fullname',
           as: 'address'
        }
    },
    {
       $unwind: '$address'
    }
]).pretty();

OUTPUT:

{
        "_id" : ObjectId("628bc4a45c544feccff5a566"),
        "fullname" : "Mehvish Ashiq",
        "age" : 30,
        "gender" : "Female",
        "nationality" : "Pakistani",
        "address" : {
                "_id" : ObjectId("628bc4ae5c544feccff5a568"),
                "fullname" : "Mehvish Ashiq",
                "block_number" : 22,
                "street" : "Johar Town Street",
                "city" : "Lahore"
        }
}
{
        "_id" : ObjectId("628bc4a45c544feccff5a567"),
        "fullname" : "James Daniel",
        "age" : 45,
        "sex" : "male",
        "nationality" : "Canadian",
        "address" : {
                "_id" : ObjectId("628bc4ae5c544feccff5a569"),
                "fullname" : "James Daniel",
                "block_number" : 30,
                "street" : "Saint-Denis Street",
                "city" : "Montreal"
        }
}

The $unwind operator does nothing but flattens out the array before attaching it to the resulted document. The fundamental difference for the $unwind operator is that it transforms an array with a single element into the flattened object, the element itself.

Remember, the name for this element will not be changed. It would be the same as before when the element was in the form of an array.

Execute the above query with and without the $unwind operator and observe the address field.

Use the $project Filter Stage in Aggregation Queries to Join Two Collections Into One

Before joining the collections using the $project, let’s understand its importance. For instance, if we don’t want to join the whole collection named userAddress with userInformation, we only want the city and street fields to be joined.

In that case, we need to use the $addFields stage. We use this stage to join/assign any field or multiple fields from an array/object to the document’s root level.

So, we execute the following query to retrieve the city and street from the userAddress collection.

Example Code:

> db.userInformation.aggregate([
    { $lookup:
        {
           from: 'userAddress',
           localField: 'fullname',
           foreignField: 'fullname',
           as: 'address'
        }
    },
    {
       $unwind: '$address'
    },
    {
       $addFields: {
           street: '$address.street',
           city: '$address.city'
       }
    }
]).pretty();

OUTPUT:

{
        "_id" : ObjectId("628bc4a45c544feccff5a566"),
        "fullname" : "Mehvish Ashiq",
        "age" : 30,
        "gender" : "Female",
        "nationality" : "Pakistani",
        "address" : {
                "_id" : ObjectId("628bc4ae5c544feccff5a568"),
                "fullname" : "Mehvish Ashiq",
                "block_number" : 22,
                "street" : "Johar Town Street",
                "city" : "Lahore"
        },
        "street" : "Johar Town Street",
        "city" : "Lahore"
}
{
        "_id" : ObjectId("628bc4a45c544feccff5a567"),
        "fullname" : "James Daniel",
        "age" : 45,
        "sex" : "male",
        "nationality" : "Canadian",
        "address" : {
                "_id" : ObjectId("628bc4ae5c544feccff5a569"),
                "fullname" : "James Daniel",
                "block_number" : 30,
                "street" : "Saint-Denis Street",
                "city" : "Montreal"
        },
        "street" : "Saint-Denis Street",
        "city" : "Montreal"
}

Carefully focus on the output given above. Are we getting the street and city? Yes, we are getting the street and city at the document’s root level but also have the address object that we don’t need now.

This is where the $project filter stage comes into the picture. It specifies what fields we should have in the resulted document.

See the following query for a better understanding.

Example Code:

> db.userInformation.aggregate([
    { $lookup:
        {
           from: 'userAddress',
           localField: 'fullname',
           foreignField: 'fullname',
           as: 'address'
        }
    },
    {
       $unwind: '$address'
    },
    {
       $addFields: {
           street: '$address.street',
           city: '$address.city'
       }
    },
    {
       $project: {
           fullname: 1,
           age: 1,
           gender: 1,
           street: 1,
           city: 1
       }
    }
]).pretty();

OUTPUT:

{
        "_id" : ObjectId("628bc4a45c544feccff5a566"),
        "fullname" : "Mehvish Ashiq",
        "age" : 30,
        "gender" : "Female",
        "street" : "Johar Town Street",
        "city" : "Lahore"
}
{
        "_id" : ObjectId("628bc4a45c544feccff5a567"),
        "fullname" : "James Daniel",
        "age" : 45,
        "street" : "Saint-Denis Street",
        "city" : "Montreal"
}

As you can see, we don’t have the address object now, but its two fields (street and city) are assigned to the document’s root level.

Join Two Collections Using Compass (Graphical Interface of MongoDB)

Aggregation using a graphical interface is easy. We only need to follow the following steps for the $lookup aggregation stage.

1. Open MongoDBCompass and connect to the server.

2. Create a brand new database and two collections if you want. We use the same database and collections we created using a Mongo shell.

3. Open your collections that will look as follows.

   

4. Add the stage as per your project requirements; we add the $lookup aggregation stage. Update the $lookup fields and see the desired results on the right side.