I have a widgets collection that has records like this:
{ "_id" : ObjectId("55a6abe193819c033d4d755a"), "name" : "widget1"}
{ "_id" : ObjectId("55a6abe193819c033d4d755a"), "name" : "widget2", "loc" : ObjectId("55a69475da314d9984fc6201") }
I also have a locations collection that looks like this:
{ "_id" : ObjectId("55a69475da314d9984fc6201"), "abbrev" : "CAN", "description" : "Canada" }
{ "_id" : ObjectId("55a6948eda314d9984fc6202"), "abbrev" : "USA", "description" : "United States" }
{ "_id" : ObjectId("55a69496da314d9984fc6203"), "abbrev" : "MEX", "description" : "Mexico" }
If I want to find all widgets that are located in Canada, using the Objectid, I know I can do this:
> db.widgets.find( { loc: ObjectId("55a69475da314d9984fc6201")})
{ "_id" : ObjectId("55a6abe193819c033d4d755a"), "name" : "widget2", "loc" : ObjectId("55a69475da314d9984fc6201") }
But what if I want to search for locations not using the objectid but the abbreviation or the description? How would I do that?
Best Answer
To query two collections with related data the common approach (as at MongoDB 3.0) is to find a list of matching
_id
values from the first collection which can used with a$in
query on the second.Here's an example using the
mongo
shell:Depending on the driver and/or framework you are using, there may be helper functions to make it easier to populate related data. These helper functions will ultimately result in multiple queries as there is currently no server-side support for joins.
Some examples of frameworks with helpers (not intended to be a comprehensive list):
mongo
interactive shell adding support for queryable views / virtual collectionsYou may note that I've said there is currently no server-side support for populating data. There is actually a new
$lookup
operator being added to the Aggregation Framework in the MongoDB 3.1.x development cycle (which will culminate in the eventual production release series for MongoDB 3.2). For more information see: SERVER-19095 in the MongoDB Jira issue tracker.