How to access nested information - MongoDB-CodePudding

Given the Collection below:

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 } ]
} ] );

I'm supposed to use find() or aggregate() to check the fName and lName of employees who have experience in Database Design.

However, the big problem I suspect I'm encountering is the way the document is stored with Employee as an array and with experience being an array within that document array.

I don't know if I'm making a mistake in my dot notation or not using the correct methods to access the data, I've tried using $eq, $elemMatch and other methods that worked for me for finding when the structure of the document was less complex but I just can't find a way to access the info correctly, everything I try returns empty or the document as a whole.

CodePudding user response：

The formatting is very bad, so I'm not sure I've got this correctly... Anyway, if Employees is a collection, you just need

db.collection.find({
  "experience": "Database Design"
},
{
  fName: 1,
  lName: 1
})

as shown here

EDIT: if the one inside your 'insert' is the actual model for a document, you will need an aggregation to clean up a bit the data before querying them, so something like the following:

db.collection.aggregate([
  {
    $project: {
      Employee: 1
    }
  },
  {
    "$unwind": "$Employee"
  },
  {
    "$replaceRoot": {
      "newRoot": "$Employee"
    }
  },
  {
    $match: {
      experience: "Database Design"
    }
  },
  {
    "$project": {
      fName: 1,
      lName: 1
    }
  }
])

(playground here)