MongoDB query IN array of object

arrays, mongodb, mongodb-query

Solution

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

Problem

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.

Original source