Aggregation With MongoDB

$lookup (aggregation)

$lookup is thus a stage in the MongoDB aggregation pipeline. $Lookup is used to perform a left outer join between two collections in a MongoDB database. A left outer join combines all the documents or entries on the left with matching documents or entries on the right.

Syntax:

The $lookup stage has the following syntaxes:

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}

Example:

db.q_employee.aggregate( [
   {
     $lookup:
       {
         from: "q_employee",
         localField: "managerId",
         foreignField: "_id",
         as: "managerInfo"
       }
 }
 ])

$match (aggregation)

Filters the documents to pass only the documents that match the specified condition(s) to the next pipeline stage.

Syntax:

{ $match: { <query> } }

Example:

db.q_employee.aggregate([
			{ $match : { firstName : "Daisy" } }
]);

$match with $lookup

$match is an aggregation pipeline stage used to filter the document stream to allow only those documents that meet the given condition to proceed to the next stage in the aggregation pipeline.

//Example

db.q_timesheet.aggregate( [
   {
   $match: {userId: "553434"}},
     {
     	$lookup:
       	{
         from: "q_employee",
         localField: "userId",
         foreignField: "_id",
         as: "approvedByInfo"
       }
  }
] )

Multiple $lookup with $group

MongoDB $lookup is a useful stage in an aggregation pipeline in MongoDB. Although it is not a requirement that an aggregation pipeline in MongoDB must have a $lookup stage, the stage is crucial when performing complex queries that require joining data across multiple collections.

//Example

db.q_timesheet.aggregate( [
 {$match: {_id: "122233"}},
 {
      		$lookup: {
         	 from: "q_timesheet_row",
         	 localField: "_id",
         	 foreignField: "timesheetId",
         	 as: "timesheetRowInfo"
      }
   },
  {
      $unwind: "$timesheetRowInfo"
   },
   {
      		$lookup: {
         	 from: "q_employee",
         	 localField: "userId",
         	 foreignField: "_id",
         	 as: "employeeInfo"
      }
   },
   {
   		$group : {
        			_id : '$_id',
                        tenantId: {'\$first': '\$tenantId'},
                        timesheetRows: {'\$addToSet': '\$timesheetRowInfo'},
                        status: {'\$first': '\$status'},
                        employeeInfo: {'\$first': '\$employeeInfo'},
                        userId: {'$first': '$userId'},
                        createdOn: {'$first': '$createdOn'},
                 }
   }
])

Multiple $lookup

$lookup can also be used to perform more complex joins. $lookup is not just limited to performing joining on two collections. You can implement multiple $lookup stages to perform joins on more than two collections.

//Example

db.q_timesheet.aggregate( [
   {
   	$match: {status : { $in : ["APPROVED_BY_MANAGER", "REJECTED_BY_ADMIN"]}}},
      {
      	$lookup: {
         from: "q_employee",
         localField: "userId",
         foreignField: "_id",
         as: "employeeInfo"
      }
   },
   {
     $lookup: {
         from: "q_employee",
         localField: "approvedByUserId",
         foreignField: "_id",
         as: "approvedByInfo"
      }
   }
] )

$project With $match

$project is a stage used to reshape documents by specifying which fields to include, exclude or add to documents.
For instance, in case you’re processing documents with ten fields each, but only four fields in the documents contain data that you need for your data processing, you can use $project to filter outer the fields you do not need.

db.q_jobcode.aggregate([
            {
                $project: { id: 1, code: 1, createdBy: 1, status: 1, createdOn: 1 , description: 1}

            },
            {
                $match: {code: "2244"}
            },
                 {
                    $lookup: {
                    from: "q_employee",
                    localField: "createdBy",
                    foreignField: "_id",
                    as: "createdByInfo"
            } },
])

$unwind

$unwind is an aggregation stage used to deconstruct or unwind an array field creating new documents for each element in the array. This is useful in case you want to run some aggregation on the array field values.

Using the q_jobcode and q_employee collections, we can use $lookup and $unwind together like so:

db.q_jobcode.aggregate([
   {
      $lookup: {
         from: "q_employee",
         localField: "createdBy",
         foreignField: "_id",
         as: "createdByInfo"
      }
   },
   {
      $unwind: "$createdByInfo"
   }
])

AND Example:

db.q_employee.find({ $and: [{status: "ACTIVE"}, {vacations: {$gte: 1, $lte: 3}}]})

OR Example:

db.q_employee.find({$or: [{name: "Daisy"}, {employeeNumber: "EMP9191"}]})

NOT Example:

db.q_employee.find({ "vacations": { $not: { $gte: 1}}})

NOR Example:

db.q_employee.find({$nor: [{vacations: 2}, {employeeNumber: "EMP9191"}]})

EQ Example:

db.q_employee.find({role: { $eq: "MANAGER"}})

GT Example:

db.q_employee.find({vacations: { $gt: 1}})

LT Example:

db.q_employee.find({vacations: { $lt: 2}})

NOT Example:

db.q_employee.find({vacations:: {$not: {$gt: 3}}})

GTE Example:

db.q_employee.find({vacations: { $gte: 1}})

LTE Example:

db.q_employee.find({vacations: { $lte: 1}})

IN Example:

db.q_employee.find({vacations: { $in: [3, 1]}})

NIN Example:

db.q_employee.find({vacations: { $nin: [0, 1, 3, 4]}})

NE Example:

db.q_employee.find({vacations: { $ne: 1}})