mongoDB union-like compoundIndex-CodePudding

Hi my db look like this:

Team: {
    subteam1: {
       members: [
          { age: 23, exp: 5 },
          { age: 25, exp: 2 }
       ]
    },
    subteam2: {
        members: [
          { age: 33, exp: 12 },
          { age: 54, exp: 31 }
       ]
    },
}

I want to create mongo index such as i can find team by members' (age,exp) I can use compoundIndex("subteam1.members.age","subteam1.members.exp") but thats only for subteam1. How can i do it for both subteams?

CodePudding user response：

If you want to query both subTeams members by age AND exp you will need to change the schema this way:

Team: {
  members: [
    { subTeam: "subteam1", age: 23, exp: 5 },
    { subTeam: "subteam1", age: 25, exp: 2 },
    { subTeam: "subteam2", age: 33, exp: 12 },
    { subTeam: "subteam2", age: 54, exp: 31 }
  ]
}

Then you can add an index { "members.age": 1", "members.exp": 1 }.

Mind that this index will only support queries that inlcude its prefix (members.age).
That is it won't help if you want to query only by members.exp, you'll need to add another single field index for that.

Alternatively you can create wildcard index

db.Team.createIndex(
  { "$**" : 1 },
  { "wildcardProjection" :
    {
      "subteam1.members.age" : 1,
      "subteam1.members.exp" : 1,
      "subteam2.members.age" : 1,
      "subteam2.members.exp" : 1
    }
  }
)

But it will only support single field queries,
that is querying subTeams members exclusively by age or exp.