CODEWITHSUNDEEP
mongodbjoinaggregation-frameworkmatch
amythious
amythious

Reputation: 55

How to add multiple $match conditions from different collections in MongoDB aggregates

I have 2 different MongoDB collections - Employees and Departments containing both deptid in common. I want to join these 2 collections and add multiple $match conditions from both the collections.

Employees:

{
    Empid: 001
    Name: "John"
    Age: 41
    Location: "Belfast"
    deptid: "D101"
}

Dept:

{
    deptID: "D101"
    deptNM: "HR"
    deptPr: "O"
}

Query:

db.getCollection('Employees').aggregate([
    { $match:{
        deptNM: "HR",
        Age : {$gt: 40}
       }
    },
    { $lookup: {
        from: "Dept",
        localField: "deptid",
        foreignField: "deptID",
        as: "HR EMP"
        }
     },
     { $project: {
         Empid: 1, Name: 1, Location: 1, deptNM: 1, deptPr: 1
         }
     }
])

The above query isn't working, Is there any other way?

Upvotes: 2

Views: 1193

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17915

You query has couple of issues - you can not filter on field deptNM: "HR" prior to $lookup stage as deptNM field is not from Employees collection, try below query :

db.getCollection('Employees').aggregate([
    /** filter employees to retain whose age > 40 */
    {
        $match: {
            Age: { $gt: 40 }
        }
    },
    /** Get their respective dept */
    {
        $lookup: {
            from: "Dept",
            localField: "deptid",
            foreignField: "deptID",
            as: "HR_EMP"
        }
    },
    /** As lookup's field HR_EMP is an array unwind it to get it into object */
    { $unwind: '$HR_EMP' },
    /** filter depts for HR */
    { $match: { 'HR_EMP.deptNM': "HR" } },
    /** Project only needed fields or transform fields */
    {
        $project: {
            Empid: 1, Name: 1, Location: 1, deptNM: '$HR_EMP.deptNM', deptPr: '$HR_EMP.deptPr'
        }
    }])

Test : MongoDB-Playground

Upvotes: 5

Related Questions