MongoDB Query Array of Objects within Array of Objects

Question

I'm very new to the mongoDB scene and I'm trying to figure out the best way to return a bottom level object that is within an array that is contained in an object that is in an array of objects. I know that sounds confusing so let me show you what I am working with.

{
    "_id": ObjectId("58b833a04953db3dd302102b"),
    "team": "Dragons",
    "rosters": [{
        "season": "2016-17",
        "players": [{
            "pitcher": "Oliver queen"
        }, {
            "catcher": "Slade Wilson"
        }]
    }, {
        "season": "2015-16",
        "players": [{
            "pitcher": "Joker"
        }, {
            "catcher": "Batman"
        }]
    }]
}

So my end goals are:

Display a single player, based on their position (i.e 'pitcher'), for a specific season for a specific team. For example I want to know who the pitcher was for season 2015-16 for the dragons team.
Display ALL players, based on their position, for all seasons for the team Dragons.
Update the name for a specific player in a specific season for a specific team. (Example, update "pitcher" in season 2016-17 for the Dragons from "Oliver Queen" to "Green Arrow"

Is this even a plausible query to build? If not, where could I fix my data model to be more performant?

Thanks in advance.

Bertrand Martel · Accepted Answer · 2017-03-03 18:23:53Z

2

You have multiple nested array in your collections and in MongoDB you can't use multiple positional operator to perform update operation on your players collection. And you should define a name field and a role field for your players. You could structure your data like the following :

Team / Season document

{
    "_id": ObjectId("58b99f08a7a2dfe1174e8eb9"),
    "team": "Dragons",
    "season": "2016-17",
    "players": [{
        "name": "Oliver queen",
        "role": "pitcher"
    }, {
        "name": "SomeOne",
        "role": "pitcher"
    }, {
        "name": "Slade Wilson",
        "role": "catcher"
    }]
}

So you will have one document per unique team/season pair, so :

1st document : Dragons/2016-17
2nd document : Dragons/2015-17
etc...

Display a single player, based on their position (i.e 'pitcher'), for a specific season for a specific team. For example I want to know who the pitcher was for season 2015-16 for the dragons team.

_ If you have only one player by role use :

db.players.find({
    "team": "Dragons",
    "season": "2016-17",
    "players.role": "pitcher"
}, { "players.$": 1 })

It will return only the first player matching the pitcher role :

{ "_id": ObjectId("58b99f08a7a2dfe1174e8eb9"), "players": [{ "name": "Oliver queen", "role": "pitcher" }] }

_ If you can have multiple player by role (like in the model above), use an aggregation to $filter the players array with the matching role :

db.players.aggregate([{
    $match: {
        "team": "Dragons",
        "season": "2016-17",
        "players.role": "pitcher"
    }
}, {
    $project: {
        players: {
            $filter: {
                input: "$players",
                as: "player",
                cond: { $eq: ["$$player.role", "pitcher"] }
            }
        }
    }
}])

That gives you :

{ "_id": ObjectId("58b99f08a7a2dfe1174e8eb9"), "players": [{ "name": "Oliver queen", "role": "pitcher" }, { "name": "SomeOne", "role": "pitcher" }] }

Display ALL players, based on their position, for all seasons for the team Dragons.

Use an aggregation grouping by players.role :

db.players.aggregate([{
    $match: {
        "team": "Dragons"
    }
}, {
    $unwind: "$players"
}, {
    $group: {
        _id: "$players.role",
        players: {
            $push: "$players.name"
        }
    }
}])

That gives :

{ "_id": "catcher", "players": ["Slade Wilson", "Batman"] } { "_id": "pitcher", "players": ["Oliver queen", "SomeOne", "Joker"] }

Update the name for a specific player in a specific season for a specific team. (Example, update "pitcher" in season 2016-17 for the Dragons from "Oliver Queen" to "Green Arrow"

Update with positional parameter matching the item found :

db.players.update({
    "team": "Dragons",
    "season": "2016-17",
    "players.name": "Oliver queen"
}, {
    $set: {
        "players.$.name": "Green Arrow"
    }
});

Team / Season / Player document

{
    "team": "Dragons",
    "season": "2016-17",
    "name": "Oliver queen",
    "role": "pitcher"
}, {
    "team": "Dragons",
    "season": "2016-17",
    "name": "SomeOne",
    "role": "pitcher"
}, {
    "team": "Dragons",
    "season": "2016-17",
    "name": "Slade Wilson",
    "role": "catcher"
}

Each document features a player in a specific team, in a specific season

Display a single player, based on their position (i.e 'pitcher'), for a specific season for a specific team. For example I want to know who the pitcher was for season 2015-16 for the dragons team.

db.players.find({ "team": "Dragons", "season": "2016-17", "role": "pitcher" })

Display ALL players, based on their position, for all seasons for the team Dragons.

db.players.find({"team": "Dragons", "role": "pitcher"})

Update the name for a specific player in a specific season for a specific team. (Example, update "pitcher" in season 2016-17 for the Dragons from "Oliver Queen" to "Green Arrow"

db.players.update({
    "team": "Dragons",
    "season": "2016-17",
    "name": "Oliver queen"
}, {
    $set: {
        "name": "Green Arrow"
    }
})

edited Mar 3, 2017 at 18:23

answered Mar 3, 2017 at 17:07

Bertrand Martel

46k17 gold badges156 silver badges170 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Daniel Vincent Over a year ago

Thank you for the VERY detailed response! I'm still trying to shake out the relational thinking when building my schemas. I will use the new structure you posted and keep it as a reference for myself in the future to not make the same mistake again.

Bertrand Martel Over a year ago

Note that you can also use 1 document per unique team/season/player if you prefer, this way you don't have any array, and it's easier to perform find, update, insert, no $unwind required etc... I can add this solution to the post if you want

Daniel Vincent Over a year ago

If you wouldn't mind adding it. I might be able to figure it out on my own but it never hurts to have the answer sheet to look at just in case. Will the performance be noticeably better with the team/season/player document set-up creating a larger amount of documents as opposed to the array set-up?

Bertrand Martel Over a year ago

I think it would be better to find in your collection with a find query rather than an aggregate. If you want to have more nested fields in players it will also be more easier to use filters and update nested elements inside players if you want to add some later. But you can keep on the model with players array if it's ok

Bertrand Martel Over a year ago

I've updated my post, note that with the last model there is no need to use aggregation especially for find

Collectives™ on Stack Overflow

MongoDB Query Array of Objects within Array of Objects

1 Answer 1

Team / Season document

Team / Season / Player document

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Team / Season document

Team / Season / Player document

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related