JIYIK CN >

Today, we will merge two collections into one using $lookupthe Aggregate stage, the pipelineAND $unwindoperator, the Filter stage, and MongoDB Compass.$project

Merge two collections into one using MongoDB

We have different ways to combine two collections into one using MongoDB. Some of them are given below and we will cover them in this tutorial.

For all the above scenarios, we must have a database with two collections (same as tables in MySQL) filled with documents (same as records in MySQL). We did this with the following query; you can do the same.

Create two collections named usersInformationand in the database. Also, populate them with the following documents.userAddressusers

Create a database and collection:

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

Populate the collection with two documents userInformation:

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

Populate the collection with two documents userAddress:

> 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 inserted multiple documents using insertMany()the function. Now, we can view the data of both collections using the following command.

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

Showing userInformationdocuments from:

> 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"
}

Showing userAddressdocuments from:

> 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 $lookupthe aggregation stage. Once both collections are ready, we can use various queries to join the data of both collections according to our scenario.

Use $lookupthe aggregation stage to combine two collections into one

Sample 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 MongoDB database, $lookupthe aggregation phase performs a left outer join with other collections and filters the information (data) from the joined documents. For example, we use a query to get the information of all users and their addresses.

$lookupThe function accepts four fields. The first is fromthe field, where we specify the collection that should be joined with the other collection.

The second is localFieldthe field. It is fromone of the attributes (fields) of the input document of the collection specified in the field.

It is used to perform matching on localFieldand in the collection documents.foreignField

Similarly, foreignFieldthe third field named also performs an equality match on foreignFieldand in the collection documents.localField

asWe write down the name of the new array for the fourth field . $lookupSee the following description for an explanation of the aggregation phase.

Use pipelinethe operator to combine two collections into one based on the specified condition.

Sample 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"
                }
        ]
}

When we want to join two sets based on a certain condition, we can use the operator $lookupwith the CONNECT pipelineclause (just like we use the SELECT clause in MySQL WHERE).

For example, we are joining the set of from equals userAddressto fullnamethe userInformationset of in fullname.

$unwindUse the operator to flatten the array before appending to the result document

Sample 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"
        }
}

$unwindThe operator does nothing but flatten the array before appending it to the result document. $unwindThe fundamental difference of the operator is that it converts an array with a single element into a flattened object, that is, the element itself.

Remember that the name of this element does not change. When the element is in array form, it is the same as before.

Execute the above query with and without $unwindthe operator and observe addressthe field.

$projectUse the filter stage in an aggregation query to combine two collections into one

Before joining $projectcollections with , let's understand its importance. For example, if we don't want to join userAddressthe entire collection named with userInformation, we only want to join the cityand streetfields.

In this case, we need to use $addFieldsthe stage. We use this stage to join/assign any field or multiple fields in an array/object to the root level of the document.

Therefore, we execute the following query to retrieve and from userAddressthe collection .citystreet

Sample 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"
}

Pay close attention to the output given above. Did we get 街道and 城市? Yes, we got streetand at the root level of the document city, but there is also an object which we don't need right now address.

This is $projectwhere the filtering stage comes in. It specifies which fields we should include in the resulting document.

See the following query for better understanding.

Sample 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 now have no addressobject, but its two fields ( streetand city) are assigned to the root level of the document.

Use Compass to connect two collections (MongoDB's graphical interface)

Aggregation is easy using the graphical interface. We just need to $lookupperform the following steps during the aggregation phase.