JIYIK CN >

$lookupToday, we will see how to concatenate multiple conditions using the operator in MongoDB . In addition, we will explore some examples to demonstrate the use of $groupthe stage and $unionWidththe aggregation stage.

$lookupUse the operator to connect multiple conditions in MongoDB

If we have MongoDB 3.6 or higher, we can use $lookupthe aggregate pipelineoperator to concatenate multiple conditions.

For this purpose, we have two collections named usersand salaries. You can also create it using the following command.

Sample code to create a collection:

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

usersSample code to insert a document into a 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'
        }
    ]
)

salariesSample code to insert a document into a collection:

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

usersDisplay data of the 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"
}

salariesDisplay data of the collection:

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

Output:

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

After creating the collection and inserting the documents, we can explore various scenarios to join multiple conditions. Let's $lookupstart with .

Using the $lookupAggregate pipelineOperator

Sample 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 that satisfy both conditions.

We get only the documents that satisfy these two conditions. You may have noticed usersalarythat is an array of elements, where each element is salariesa document in the collection.

We can use $unwind, $addFields, and to get specific fields $projectfrom two collections ( usersand salaries) and form a document as shown in the following example.

Sample 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 $unwindthe operator is to deconstruct an array field from the input documents into one output document for each element with the same name.

If there is only one element in the array, the $unwindstage operator flattens the object, that is, the element itself. Concatenates the fields $addFieldsin the object or array salaryto the root level of the document.

Before understanding the usage in the example given above, let us focus on $projectthe reason for using the filter stage. If we don't use it $project, we will get the document root level salaryfields and usersalaryobjects, which is unnecessary.

This is where we use $projectthe filtering stage and specify which fields should be included in the output.

If the project requirements restrict the use of $unwind, $addFields, $project, we can use the alternative solution given below.

Sample 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 letfields (optional) to assign the value of the field to a variable. We pipelineaccess these variables in the stage, where we specify pipelineto execute on a different collection.

Note that we also use $matchthe stage to take advantage of $expran evaluation query operator called , which compares the values ​​of fields.

Additionally, $replaceRootbeing pipelinethe last aggregation stage in pipeline, we merge the output of with the parts of the document using $mergeObjectsthe operator .$lookup$$ROOT

We have only used $andthe operator to connect the conditions. You can also use $orthe or two operators.

Create a new collection and use $groupthe aggregation stage to join multiple conditions

Sample 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 users_salariesnew collection named , merge two collections named usersand salaries, and then insert the documents into the newly created collection. We then 用户名group by to get the desired output.

We can also get the same output without creating a new collection (as shown above). To do this, we use $unionWiththe Aggregate stage, which performs a union of two collections.

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