How to Join Multiple Conditions Using the Lookup Operator in MongoDB

1. Join Multiple Conditions Using the $lookup Operator in MongoDB
2. Create New Collection and Use the $group Aggregation Stage to Join Multiple Conditions

Today, we will see how to join multiple conditions using the $lookup operator in MongoDB. Additionally, we will also explore some examples demonstrating the use of the $group stage and $unionWidth aggregation stage.

Join Multiple Conditions Using the $lookup Operator in MongoDB

If we have the MongoDB 3.6 or above, we can use the $lookup aggregation pipeline operator to join multiple conditions.

For this, we have two collections named users and salaries. You may also create that using the following commands.

Example Code for Creating Collections:

> db.createCollection('users')
> db.createCollection('salaries')

Example Code for Inserting Documents in the users Collection:

> db.users.insertMany(
    [
        {
            username: 'userone',
            age: 30,
            gender: 'Female',
            city: 'Lahore',
            country: 'Pakistan'
        },
        {
            username: 'usertwo',
            age: 35,
            gender: 'Male',
            city: 'Florida',
            country: 'United States'
        }
    ]
)

Example Code for Inserting Documents in the salaries Collection:

> db.salaries.insertMany(
    [
        {
            username: 'userone',
            salary: 3000
        },
        {
            username: 'usertwo',
            salary: 5000
        }
    ]
)

Display Data of the users Collection:

> db.users.find().pretty()

OUTPUT:

{
        "_id" : ObjectId("628deb40c1e812eeeb311439"),
        "username" : "userone",
        "age" : 30,
        "gender" : "Female",
        "city" : "Lahore",
        "country" : "Pakistan"
}
{
        "_id" : ObjectId("628deb40c1e812eeeb31143a"),
        "username" : "usertwo",
        "age" : 35,
        "gender" : "Male",
        "city" : "Florida",
        "country" : "United States"
}

Display Data of the salaries Collection:

> db.salaries.find().pretty()

OUTPUT:

{
        "_id" : ObjectId("628deb07c1e812eeeb311437"),
        "username" : "userone",
        "salary" : 3000
}
{
        "_id" : ObjectId("628deb07c1e812eeeb311438"),
        "username" : "usertwo",
        "salary" : 5000
}

After creating the collections and inserting documents, we can explore various scenarios to join multiple conditions. Let’s start with $lookup.

Use the $lookup Aggregation pipeline Operator

Example Code:

> db.users.aggregate([
    {
        $lookup: {
            from: 'salaries',
            let: {
                user_name: '$username',
                user_salary: 3000
            },
            pipeline: [{
                $match: {
                    $expr: {
                        $and: [
                            { $eq: ['$username', '$$user_name'] },
                            { $gte: ['$salary','$$user_salary'] }
                        ]
                    }
                }
           }],
           as: 'usersalary'
        }
    }
]).pretty()

OUTPUT:

{
        "_id" : ObjectId("628deb40c1e812eeeb311439"),
        "username" : "userone",
        "age" : 30,
        "gender" : "Female",
        "city" : "Lahore",
        "country" : "Pakistan",
        "usersalary" : [
                {
                        "_id" : ObjectId("628deb07c1e812eeeb311437"),
                        "username" : "userone",
                        "salary" : 3000
                }
        ]
}
{
        "_id" : ObjectId("628deb40c1e812eeeb31143a"),
        "username" : "usertwo",
        "age" : 35,
        "gender" : "Male",
        "city" : "Florida",
        "country" : "United States",
        "usersalary" : [
                {
                        "_id" : ObjectId("628deb07c1e812eeeb311438"),
                        "username" : "usertwo",
                        "salary" : 5000
                }
        ]
}

Here, we get the documents meeting two conditions.

1. The username field is the same in the users and salaries collections.
2. The value of the salary field is greater than or equal to 3000.

We only get the document meeting both conditions. You may have noticed that the usersalary is visible as an array of elements where each element is a document of salaries collection.

We can use the $unwind, $addFields, and $project to get the specific fields from both collections (users and salaries) and form one document, as demonstrated in the following example.

Example Code:

> db.users.aggregate([
    {
        $lookup: {
            from: 'salaries',
            let: {
                user_name: '$username',
                user_salary: 3000
            },
            pipeline: [{
                $match: {
                    $expr: {
                        $and: [
                            { $eq: ['$username', '$$user_name'] },
                            { $gte: ['$salary','$$user_salary'] }
                        ]
                    }
                }
           }],
           as: 'usersalary'
        }
    },
    {
        $unwind:'$usersalary'
    },
    {
        $addFields: {
            salary: '$usersalary.salary'
        }
    },
    {
       $project: {
           username: 1,
           salary: 1
       }
    }
]).pretty()

OUTPUT:

{
        "_id" : ObjectId("628deb40c1e812eeeb311439"),
        "username" : "userone",
        "salary" : 3000
}
{
        "_id" : ObjectId("628deb40c1e812eeeb31143a"),
        "username" : "usertwo",
        "salary" : 5000
}

The purpose of using the $unwind operator is to deconstruct an array field from input documents to the output one document for every element with the same name.

If there is only one element in the array, then the $unwind stage operator flattens the object, which is the element itself. The $addFields joins the salary field from an object or array to the root level of the document.

Let’s focus on the reason for using the $project filter stage before understanding its use in the example given above. If we don’t use the $project, we will get the salary field at the document’s root level and the usersalary object, which is unnecessary.

This is where we use the $project filter stage and specify what fields should be in the output.

We can use the alternative solution given below if the project requirements restrict using $unwind, $addFields, $project.

Example Code:

> db.users.aggregate([
    {
        $lookup: {
            from: 'salaries',
            let: {
                user_name: '$username',
                user_salary: 3000
            },
            pipeline: [{
                $match: {
                    $expr: {
                        $and: [
                            { $eq: ['$username', '$$user_name'] },
                            { $gte: ['$salary','$$user_salary'] }
                        ]
                    }
                }
           }],
           as: 'usersalary'
        }
    },
       {
          $replaceRoot: {
             newRoot: {
                $mergeObjects:[
                   {
                      $arrayElemAt: [
                         "$usersalary", 0
                      ]
                   },
                   {
                      salary: "$$ROOT.salary"
                   }
                ]
             }
          }
       }
    ]
).pretty()

OUTPUT:

{
        "_id" : ObjectId("628deb07c1e812eeeb311437"),
        "username" : "userone",
        "salary" : 3000
}
{
        "_id" : ObjectId("628deb07c1e812eeeb311438"),
        "username" : "usertwo",
        "salary" : 5000
}

We use the let field (optional) to assign the values of fields to variables. We access these variables in the pipeline stage, where we specify the pipeline to get executed on different collections.

Note that we are also using the $match stage to take advantage of the evaluation query operator named $expr, which compares the value of fields.

Further, $replaceRoot is the last aggregation pipeline stage in the pipeline where we are using the $mergeObjects operator to merge the $lookup output with the $$ROOT document’s part.

We used only the $and operator to join the conditions. You can also use $or or both operators.

Create New Collection and Use the $group Aggregation Stage to Join Multiple Conditions

Example Code:

> db.users_salaries.insertMany(
    db.users.find({}, {"_id": 0})
    .toArray()
    .concat(db.salaries.find({}, {"_id": 0}).toArray())
)

db.users_salaries.aggregate([
    { "$group": {
        "_id": { "username": "$username" },
        "salary": { "$push": "$salary" }
    }}
])

OUTPUT:

{ "_id" : { "username" : "userone" }, "salary" : [ 3000 ] }
{ "_id" : { "username" : "usertwo" }, "salary" : [ 5000 ] }

For this code example, we create a new collection named users_salaries, merge two collections named users and salaries, and then insert those documents into the newly created collection. Then, group by the username to get the desired output.

We can also get the same output (as given above) without creating a new collection. For that, we use the $unionWith aggregation stage, which performs a union for two collections.

Example Code:

> db.users.aggregate([
  { $set: { username: "$username" } },
  { $unionWith: {
    coll: "salaries",
    pipeline: [{ $set: { salary: "$salary" } }]
  }},
  { $group: {
    _id: { username: "$username"},
     "salary": { "$push": "$salary" }
  }}
])

OUTPUT:

{ "_id" : { "username" : "userone" }, "salary" : [ 3000 ] }
{ "_id" : { "username" : "usertwo" }, "salary" : [ 5000 ] }