mongoDB: querying for a document that has an object reference

Question

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?

Answer 1

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:

// Find _ids for matching locations based on some criteria (eg. "abbrev")
var matches = db.locations.find({ "abbrev": "CAN"}, { _id: 1 })

// Transform matching _id values into an array
var locs = [];
matches.forEach(function(match) { locs.push(match._id) });

// Look for widgets matching the location criteria
db.widgets.find({"loc": {$in : locs} } )

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):

• MongooseJS: query population: Node.js Object-Document Model (ODM) framework including declarative schema & query population for relationships
• Mongoid: relations: Ruby ODM including declarative schema & query population for relationships
• mongo-views: experimental add-in for the mongo interactive shell adding support for queryable views / virtual collections

You 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.

Answer 2

Please try the below Query

db.getCollection("widgets").aggregate([
    {
        $addFields: {
            "loc": {
                $arrayElemAt: [{ $objectToArray: "$loc" }, 1]
            }
        }
    },
    {
        $addFields: {
            "loc": "$loc.v"
        }
    },
    $lookup: {
           "from": "locations",
           "localField": "loc",
           "foreignField": "_id",
           "as": "locations"
        }

    },
    { $match: { "locations.abbrev" : "CAN"} }
])

Above query will join locations object with widget and filter with Canada location.