{ _id: '/players/c/cruzne02.shtml',
  url: '/players/c/cruzne02.shtml',
  name: 'Nelson Cruz',
  image: 'https://www.baseball-reference.com/req/202108020/images/headshots/f/fea2f131_mlbam.jpg',
  teams: 
   [ { name: 'MIL', years: [ 2005 ] },
     { name: 'TEX',
       years: [ 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013 ] },
     { name: 'BAL', years: [ 2014 ] },
     { name: 'SEA', years: [ 2015, 2016, 2017, 2018 ] },
     { name: 'MIN', years: [ 2019, 2020, 2021 ] },
     { name: 'TBR', years: [ 2021 ] } ] 
}

If I want to find all the teammates for this player how can I structure a query to be called only once from my node backend instead of looping through each team and calling a find for each one?

Instead of Looping through each object inside the teams object on Node and doing a query on each item like db.collection.find($and: {[{"teams.name":"MIL"}, {"teams.years":2005]}); and looping through each year for each team. Because I need an array for each team. So in this case instead of calling 18 finds for this object I want to call one query and return 18 arrays.

CodePudding user response：

Query

• match stage
• check all members of teams
• a member for example { name: 'MIN', years: [ 2019, 2020, 2021 ] }
• passes because
  • MIL<=MIN<=TBR
  • AND
  • [ 2019, 2020, 2021 ].instersetion(range 2005 2022(exclusive)) is not empty so player played this year also in that team
• if even 1 member passed (array result of filter not empty) => player played in that that time range, in a team inside in the teams range
• so all returned players were teamates at least for 1 year in some team

*i am not sure if i got what you need, test it before use it, if it works the way you need it to work

Test with 1 member(its easier to test this way)

Test with your data

db.collection.aggregate([
  {
    "$match": {
      "$expr": {
        "$ne": [
          {
            "$filter": {
              "input": "$teams",
              "cond": {
                "$and": [
                  {
                    "$gte": [
                      "$$this.name",
                      "MIL"
                    ]
                  },
                  {
                    "$lte": [
                      "$$this.name",
                      "TBR"
                    ]
                  },
                  {
                    "$ne": [
                      {
                        "$setIntersection": [
                          "$$this.years",
                          {
                            "$range": [
                              2005,
                              2022
                            ]
                          }
                        ]
                      },
                      []
                    ]
                  }
                ]
              }
            }
          },
          []
        ]
      }
    }
  }
])

CodePudding user response：

You can do it with $elemMatch

const player = { 
  _id: '/players/c/cruzne02.shtml',
  url: '/players/c/cruzne02.shtml',
  name: 'Nelson Cruz',
  image: 'https://www.baseball-reference.com/req/202108020/images/headshots/f/fea2f131_mlbam.jpg',
  teams: 
   [ { name: 'MIL', years: [ 2005 ] },
     { name: 'TEX',
       years: [ 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013 ] },
     { name: 'BAL', years: [ 2014 ] },
     { name: 'SEA', years: [ 2015, 2016, 2017, 2018 ] },
     { name: 'MIN', years: [ 2019, 2020, 2021 ] },
     { name: 'TBR', years: [ 2021 ] } ] 
}; // the player which you search for teammates

db.collection.find({
  "teams": {
    $elemMatch: {
      $or: player.teams.map(t => {name: t.name, years: {$in: t.years}}
    }
  }
})

It finds players that in thier teams at least one have the same name and there exists a year contined in player's same group name and years.
See example here