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?

You're not doing anything wrong, that's just the way those queries work -- they only include the first matching element. I think you'll need to use the aggregation framework to do what you want. — JohnnyHK, Mar 14 '13 at 17:27

score 17 · Accepted Answer · answered Mar 14 '13 at 19:24

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
}

One more related question if you don't mind how to restrict selected tracks to have olny title field (no other like language field). — WHITECOLOR, Mar 14 '13 at 20:31
@mnemosyn I tried this solution on my related problem and it worked. Now I have to go back and understand it! — carbontax, May 05 '13 at 16:34
I had to add array brackets inside the `aggregate` call. `db.Album.aggregate([...]);` — Swoop, May 14 '20 at 02:59

MongoDB: select matched elements of subcollection

1 Answers1