Using the db.collection.find query in a sub-document

Question

Is there a way to use db.collection.find() to query for a specific value in a sub-document and find those documents that match. For example:

{ { 'Joe' : {eyecolor : 'brown'}, { 'Mary' : {eyecolor : 'blue'}, .... }

I want to return the names of all people whose eyecolor is blue.

Solution 1

You need to specify the full path to a value for search to work:

db.people.find({ "Joe.eyecolor" : "brown" })

You can't switch to an array of people instead of an associative array style you're using now, as there is no way to return only array elements that match conditions. You can use $elemMatch to return the first match, but that's not likely what you'd want. Or, you could still use arrays, but you'd need to filter the array further within your client code (not the database).

You might be able to use the Aggregation framework, but it wouldn't use indexes efficiently, as you'd need to $unwind the entire array, and then do filtering, brute force. And if the data contained is more complex, the fact that projections when using the AF require you to manually specify all fields, it becomes a bit cumbersome.

To most efficiently do the query you're showing, you'd need to not use subdocuments, and instead place the people as individual documents:

{ 
    name: "Joe",
    eyecolor: "brown" 
}

Then, you could just do a simple search like:

db.people.find({eyecolor: "brown"})

Solution 2

Yes and no. You can query for all documents that have a matching person, but you can't query for all persons directly. In other words, subdocuments are not virtual collections, you'll always have the 'parent' document returned.

The example you posted comes with the additional complexity that you're using the name as a field key, which prevents you from using the dot notation.

In general, if you have a number of similar things, it's best to put them in a list, e.g.

{ 
   "_id" : 132,
   "ppl" : [ { "Name" : "John", "eyecolor" : "blue" },
             { "Name" : "Mary", "eyecolor" : "brown" },
             ...
           ]
}

Then, you can query using the aggregation framework:

db.collection.aggregate([ 
// only match documents that have a person w/ blue eyes (can use indexing)
{$match : { "ppl.eyecolor" : "blue" } }, 
// unwind the array of people
{$unwind : "$ppl" }, 
// match only those with blue eyes
{$match : { "ppl.eyecolor" : "blue" }}, 
// optional projection to make the result a list of people
{$project : { Name : "$ppl.Name", EyeColor: "$ppl.eyecolor" }} ]);

Which gives a result like

"result" : [
     {
             "_id" : 132,
             "Name" : "John",
             "EyeColor" : "blue"
     },
     {
             "_id" : 12,
             "Name" : "Jimmy",
             "EyeColor" : "blue"
     },
     {
             "_id" : 4312,
             "Name" : "Jimmy",
             "EyeColor" : "blue"
     },
     {
             "_id" : 4312,
             "Name" : "Marc",
             "EyeColor" : "blue"
     }
],
"ok" : 1