MongoDB: select matched elements of subcollection

Question

I'm using mongoose.js to do queries to mongodb, but I think my problem is not specific to mongoose.js.

Say I have only one record in the collection:

var album = new Album({
    tracks: [{
        title: 'track0',
        language: 'en',

    },{
        title: 'track1',
        language: 'en',
    },{
        title: 'track2',
        language: 'es',
    }]
})

I want to select all tracks with language field equal to 'en', so I tried two variants:

Album.find({'tracks.language':'en'}, {'tracks.$': 1}, function(err, albums){

and tied to to the same thing with $elemMatch projection:

Album.find({}, {tracks: {$elemMatch: {'language': 'en'}}}, function(err, albums){

in either case I've got the same result:

{tracks:[{title: 'track0', language: 'en'}]}

selected album.tracks contain only ONE track element with title 'track0' (but there should be both 'track0', 'track1'):

 {tracks:[{title: 'track0', language: 'en'}, {title: 'track1', language: 'en'}]}

What am I doing wrong?

Answer 1

Like @JohnnyHK already said, you'll have to use the aggregation framework to accomplish that because both $ and $elemMatch only return the first match.

Here's how:

db.Album.aggregate(
    // This is optional. It might make your query faster if you have
    // many albums that don't have any English tracks. Take a larger 
    // collection and measure the difference. YMMV.
    { $match: {tracks: {$elemMatch: {'language': 'en'}} } },

    // This will create an 'intermediate' document for each track
    { $unwind : "$tracks" },

    // Now filter out the documents that don't contain an English track
    // Note: at this point, documents' 'tracks' element is not an array
    { $match: { "tracks.language" : "en" } },

    // Re-group so the output documents have the same structure, ie.
    // make tracks a subdocument / array again
    { $group : { _id : "$_id", tracks : { $addToSet : "$tracks" } }} 
);

You might want to try that aggregate query with only the first expression and then add expressions line by line to see how the output is changed. It's particularly important to understand how $unwind creates intermediate documents that are later re-merged using $group and $addToSet.

Results:

> db.Album.aggregate(
     { $match: {tracks: {$elemMatch: {'language': 'en'}} } }, 
     { $unwind : "$tracks" },
     { $match: { "tracks.language" : "en" } },
     { $group : { _id : "$_id", tracks : { $addToSet : "$tracks" } }}  );
{
    "result" : [
            {
                    "_id" : ObjectId("514217b1c99766f4d210c20b"),
                    "tracks" : [
                            {
                                    "title" : "track1",
                                    "language" : "en"
                            },
                            {
                                    "title" : "track0",
                                    "language" : "en"
                            }
                    ]
            }
    ],
    "ok" : 1
}