CODEWITHSUNDEEP
jsonmongodb
aliigleed
aliigleed

Reputation: 238

MongoDB nested document queries with conditions

I'm trying to wrap my head around some more complex MongoDB queries. I've read through a number of somewhat-related questions , but I haven't quite been able to figure out what would be the best approach for the situation I'm in. Say I'm trying to index a collection of DVD's with different club soccer matches recorded, and I've modeled the documents in the collection like the following (abbreviated):

{ 
  "dvdId" : "10021", 
  "dateSubmitted" : ISODate("2015-11-17T15:42:21.248Z"),
  "featuredPlayerNames": [
    "Sam B. Clement", 
    "John C. Carter", 
    "Gabriel M. Malinowski", 
    "Jimmy I. Vincent", 
    "George L. Spears", 
    "Roland M. Nelson", 
  ],
  "matches" : [ 
    { 
      "startTime" : 0,
      "winningTeamIndex": 0,
      "teams" : [ 
        { 
          "teamName": "Hornets",
          "players" : [ 
            { 
              "name" : "Sam B. Clement", 
              "position" : "striker" 
            }, 
            { 
              "name" : "John C. Carter", 
              "position" : "halfback" 
            },
          ] 
        }, 
        { 
          "teamName": "Hurricanes",
          "players" : [ 
            { 
              "name" : "Gabriel M. Malinowski", 
              "position" : "fullback" 
            }, 
            { 
              "name" : "Jimmy I. Vincent", 
              "position" : "keeper" 
            },
          ] 
        } 
      ] 
    },
    { 
      "startTime": 5602,
      "winningTeamIndex": 1,
      "teams" : [ 
        { 
          "teamName": "Raiders",
          "players" : [ 
            { 
              "name" : "Sam B. Clement", 
              "position" : "halfback" 
            }, 
            { 
              "name" : "George L. Spears", 
              "position" : "striker" 
            },
          ] 
        }, 
        { 
          "teamName": "Hurricanes",
          "players" : [ 
            { 
              "name" : "Roland M. Nelson", 
              "position" : "striker" 
            }, 
            { 
              "name" : "Jimmy I. Vincent", 
              "position" : "keeper" 
            },
          ] 
        } 
      ] 
    },    
  ], 
}

What I've done so far. I've figured out how to get it to do some simple operations on the first-level properties and arrays, such as returning DVD's featuring a player of interest and/or another player of interest; e.g., db.dvds.find({featuredPlayerNames: {$in: ['Sam B. Clement', 'John C. Carter']}});.

Where I need help. I'm now trying to figure out how I would query the Mongo database with more complex operations, such as return documents containing match(es) featuring the following conditions:

What would be the best approach to accomplish advanced queries such as these? After wandering in the documentation and from my reading of similar questions (1 2 3 4 5 6 7 8 9 10 11 12* 13*), I've found I may need to use $elemMatch, $cond, $in, $all,$match, $unwind, $group, and/or the aggregation framework, but in addition to being unfamiliar with many of these, I also saw a lot of different opinions and approaches, and none that really seemed to combine nested queries with specific conditions (like a player on the winning team or players on different teams).

Now I could probably learn to do most of these given a push in the right direction. I'm also open to remodeling the structure of the data here if it seems something would work better for my case (provided I would be able to add more teams to a match as in 2v2v2).

Thank you!

Upvotes: 1

Views: 269

Answers (1)

John Vinopal
John Vinopal

Reputation: 561

In general it is faster and easier to structure your data in a manner that allows simple queries than to write complicated queries. Reformatting your data set might be a one-time cost, whereas a costly query might be executed millions of times.

Your questions:

  • teamX is a winning team
    • reformat the data to add matches.winningTeamName field
    • { matches.winningTeamName: teamX }
    • or reformat the data to add matches.teams.winner field
    • { matches.teams: { $elemMatch: { winner: true, teamName: teamX } } }
  • playerX and playerY on same team
    • matches.teams: { $and: [ {players.name: playerX}, {players.name, playerY} ] }
    • Or reformat the data to add matches.teams.playerList field
    • { matches.teams.playerList: { $all: [playerX, playerY] } }
  • playerX and playerY on different teams
    • not sure, I think you can do this with a combination of $and and $not but without having the db set up I'd be guessing.
  • playerX on winning team
    • reformat the data to add matches.teams.winner field
    • { matches.teams: { $elemMatch: { winner: true, players.name: playerX } } }
  • playerX playing positionX
    • { matches.teams.players: { $elemMatch: { name: playerX, position: positionX} } }
  • again with the different teams...

Hope that helps...

Upvotes: 2

Related Questions