MongoDB query IN array of object

Question

I have problem to retrieve information between two collections. The first collection stores employees information:

{
        "_id" : ObjectId("4f9643967f8b9a3f0a00005a"),
        "birth_date" : "1963-09-09",
        "departments" : [
                {
                        "departments_id" : ObjectId("4f9643957f8b9a3f0a000007"),
                        "from_date" : "1990-01-03",
                        "to_date" : "1990-01-15"
                }
        ],
        "first_name" : "Parviz",
        "gender" : "M",
        "hire_date" : "1990-01-03",
        "last_name" : "Lortz",
}

the second one the departments information

{
        "_id" : ObjectId("4f9643957f8b9a3f0a000004"),
        "dept_name" : "Marketing",
        "managers" : [
                {
                        "employees_id" : ObjectId("4f96439b7f8b9a3f0a0186a9"),
                        "from_date" : "1985-01-01",
                        "to_date" : "1991-10-01"
                },
                {
                        "employees_id" : ObjectId("4f96439b7f8b9a3f0a0186aa"),
                        "from_date" : "1991-10-01",
                        "to_date" : "9999-01-01"
                }
        ]
}

I try to find: All departments for a given employee.

I tried something like:

employees = db.employees.find({_id:ObjectId("some_id")});
db.departments.find({_id:{$in:...}});

But I don't know how I can explain $in department_id of all departments from var employees.

Answer 1

There is an easy way to do that in Mongo 3.2, at least in just a single operation:

const employees = db.employees.find(); // query the employees collection
db.departments.find({
  managers: {
    $elemMatch: {
      employees_id: {
        $in: employees.map(e => e._id)
      }
    }
  }
});

The $elemMatch modifier (see ref) helps query against an array-like value of an object property.

Answer 2

This can not be done with a simple query. You will have to loop over employees.departments and for each iteration add its departments_id to an array. This array you then can use in your second line. This is something best done in your language of choice.

In order to make this easier, you'll have to change your schema. One option is to store the department information in the employee record, but in your case you'd be duplicating a lot of data.

I would instead suggest to have each department contain a list with employee IDs and dates instead like this:

{
        "_id" : ObjectId("4f9643957f8b9a3f0a000004"),
        "dept_name" : "Marketing",
        "managers" : [
        ]
        "employees" : [
            {
                    "employee_id" : ObjectId("4f9643967f8b9a3f0a00005a"),
                    "from_date" : "1990-01-03",
                    "to_date" : "1990-01-15"
            }
        ]
}

In that case, you can then simply run:

db.departments.find( { "employees.employee_id": ObjectId("some_id") } );