I'm using three collections (employee, department and location ) in a mongodb database. I need to list all departments of employee lives in a city starts with "P".
I have created following query using nested db.runCommand which is working fine in mongoDB.
db.Department.find({"id":{ $in: db.runCommand( {
// FInd distinct employee
distinct: "employee",
key:"department_id",
query: {$and:[{ "location_id":{$in: db.runCommand ( {
// FInd distinct Location
distinct: "Location",
key:"id",
query: {"city":{ "$regex": "P.*" }}}).values
// End - Location
}}
]}}).values
// End - employee
}}).limit(100)
How to create above query using mongodb-java-driver?