Find all employees who possess 4 experiences. Show only the employee's information.

Below is the database in question

db.empeProject.insert([ {
  "Employee": [ { "empeId": "e001",
             "fName": "James",
             "lName": "Bond",
             "email": "[email protected]",
             "experience": [
                    "Database Design",
                    "SQL",
                    "Java" ]
                },
                { "empeId": "e002",
              "fName": "Harry",
              "lName": "Potter",
              "experience": [
                    "Data Warehouse",
                    "SQL",
                    "Spark Scala",
                    "Java Scripts" ]
                } ],
  "Project": [ { "projectId": "p001",
            "projectTitle": "Install MongoDB" },
                {   "projectId": "p002",
            "projectTitle": "Install Oracle" },
                {   "projectId": "p003",
            "projectTitle": "Install Hadoop" } ],
  "EmployeeProject": [ {  "empeId": "e001",
                   "projectId": "p001",
                   "hoursWorked": 4 },
                     { "empeId": "e001",
                   "projectId": "p003",
                   "hoursWorked": 2 },
                     { "empeId": "e002",
                   "projectId": "p003",
                   "hoursWorked": 5 } ]
} ] );

Currently what I've tried is

db.empeProject.aggregate([
{"$match":{{"$count":"$Employee.experience"}:4}},
{"$project":{"Employee.fName":1,"Employee.lName":1,"Employee.email":1,"_id":0}}
]).pretty()

I'm not so sure as to how to use $count along with $match and whether do I need group by.

CodePudding user response：

You can use $size instead of $count for the task as follow:

db.collection.aggregate([
 {
 "$match": {
  "Employee.experience": {
    $size: 3
  }
 }
},
 {
"$addFields": {
  "Employee": {
    $filter: {
      input: {
        "$map": {
          "input": "$Employee",
          "as": "e",
          "in": {
            "$mergeObjects": [
              "$$e",
              {
                expCount: {
                  $size: "$$e.experience"
                }
              }
            ]
          }
        }
      },
      "as": "ef",
      "cond": {
        $eq: [
          "$$ef.expCount",
          3
        ]
      }
      }
     }
   }
 },
 {
"$project": {
  "Employee.fName": 1,
  "Employee.lName": 1,
  "Employee.email": 1,
  "_id": 0
  }
 }
 ])

Playground:

Explained:

1. Match document having only people with the $size experiance
2. Add size count to the array element and filter based on size
3. Project only the filtered Employee fields.