MongoDB how to query nested object array by matching multiple object values?

Question

I have many multiple data records structured in the following way:

example record 1
{
    "_id" : ObjectId("5c3aa368dd1460b0bc434156"),
    "id" : "XXWE124124123",
    "accountId" : "XXX2311ewqee",
    "name" : "BOB",
    "region" : "euw",
    "champions" : [ 
        {
            "_id" : ObjectId("5c3aa368dd1460b0bc4341ba"),
            "id" : 412,
            "rank" : "DIAMOND",
            "kills" : "5",
            "assists" : "18",
            "deaths" : "7",
            "wins" : true,
            "lane" : "BOTTOM",
            "spells" : "4-14",
            "trinket" : "3364",
            "items" : "items-3117-3109-3069-2055-3190-3801-3364",
            "runes" : "runes-8400-8300-8439-8463-8429-8451-8345-8347-5007-5002-5002"
        }, 
        {
            "_id" : ObjectId("5c3aa368dd1460b0bc4341b9"),
            "id" : 12,
            "rank" : "DIAMOND",
            "kills" : "0",
            "assists" : "4",
            "deaths" : "8",
            "wins" : false,
            "lane" : "BOTTOM",
            "spells" : "4-14",
            "trinket" : "3364",
            "items" : "items-3117-3050-3069-2055-3067-1031-3364",
            "runes" : "runes-8400-8300-8439-8463-8429-8451-8345-8347-5007-5003-5002"
        }, 
        {
            "_id" : ObjectId("5c3aa368dd1460b0bc4341b8"),
            "id" : 412,
            "rank" : "DIAMOND",
            "kills" : "2",
            "assists" : "15",
            "deaths" : "7",
            "wins" : false,
            "lane" : "BOTTOM",
            "spells" : "4-14",
            "trinket" : "3364",
            "items" : "items-3117-3050-3069-2055-3105-3109-3364",
            "runes" : "runes-8400-8300-8439-8463-8429-8451-8345-8347-5007-5002-5002"
        }, 
        ...
example record 2
{
    "_id" : ObjectId("8888a368dd1460b0bc434156"),
    "id" : "YYWE124124123",
    "accountId" : "YYY2311ewqee",
    "name" : "JOE",
    "region" : "euw",
    "champions" : [ 
        {
            "_id" : ObjectId("5c3aa368dd1460b0bc4342cb"),
            "id" : 112,
            "rank" : "GOLD",
            "kills" : "5",
            "assists" : "18",
            "deaths" : "7",
            "wins" : true,
            "lane" : "BOTTOM",
            "spells" : "4-14",
            "trinket" : "3364",
            "items" : "items-3117-3109-3069-2055-3190-3801-3364",
            "runes" : "runes-8400-8300-8439-8463-8429-8451-8345-8347-5007-5002-5002"
        }, 
        {
            "_id" : ObjectId("5c3aa368dd1460b0bc434cs9"),
            "id" : 412,
            "rank" : "DIAMOND",
            "kills" : "0",
            "assists" : "4",
            "deaths" : "8",
            "wins" : false,
            "lane" : "BOTTOM",
            "spells" : "4-14",
            "trinket" : "3364",
            "items" : "items-3117-3050-3069-2055-3067-1031-3364",
            "runes" : "runes-8400-8300-8439-8463-8429-8451-8345-8347-5007-5003-5002"
        }, 
        {
            "_id" : ObjectId("5c3aa368dd1460b0bc434666"),
            "id" : 412,
            "rank" : "GOLD",
            "kills" : "2",
            "assists" : "15",
            "deaths" : "7",
            "wins" : false,
            "lane" : "BOTTOM",
            "spells" : "4-14",
            "trinket" : "3364",
            "items" : "items-3117-3050-3069-2055-3105-3109-3364",
            "runes" : "runes-8400-8300-8439-8463-8429-8451-8345-8347-5007-5002-5002"
        }, 
        ...
example record 3..
example record 4..
etc

I wanted to query by multiple object values inside of the champions array of objects. For example, I want to return all the arrays that contain BOTH champions.id:412 AND champions.rank:'DIAMOND'.

I tried using the following two queries already:

ChampionData.find({ champions: {$elemMatch: {id:412,rank:'DIAMOND'} } }

and I also tried

ChampionData.find({$and:[{"champions.id": id},{"champions.rank":rank}]}

However, this seems to return the entire example record, which some of its champion.id is not 412.

score 1 · Accepted Answer · answered Jan 13 '19 at 13:42

Your query is right, but as you found out it returns the entire document that matches the find criteria and not just the nested document like you need.

In order to get back only the matched nested document, you have to "project" out only the matched item using MongoDB's $ positional operator. So you could do:

ChampionData.find({ champions: {$elemMatch: {id:412,rank:'DIAMOND'} } },
   { 'champions.$': 1 }
)

MongoDB how to query nested object array by matching multiple object values?

1 Answers1