Why is my results array returning as empty in this mongo query?-CodePudding

Here is my query I'm new to Mongo so I'm kind of fumbling through this query. My goal is to get the teammates of the player supplied. I will show the query and then the document for the player.

db.Players.aggregate([{
    $match: {_id: "/players/c/cruzne02.shtml"}}, 
    {$unwind: "$teams"},
    {$unwind: "$teams.years"}, 
    {$lookup: {
        from: "Players",
        let: {team_name: "$teams.name", team_year: "$teams.years"},
        pipeline: [{
            $match: {
                $expr: {
                    $and: [
                        {$eq: ["$teams.name", "$$team_name"]},
                        {$eq: ["$teams.years", "$$team_year"]},
                    ]
                }
            },
        }],
        as: "results"
    }},
    {$unwind: {
        path: "$results",
        preserveNullAndEmptyArrays: true
    }}, 
    {$group: {
        _id: {
            team: "$teams.name",
            year: "$teams.years"
        },
        results: {
            $push: "$results"
        }
    }}, 
    {$project: {
        team: "$_id.team",
        year: "$_id.year",
        results: 1,
        _id: 0
    }}
]);

{
    "_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]
    }]
}

I'm able to get the group back but the results array is always empty. The teams and years line up but the results never populates. My current result has empty arrays what do I need to change.

EDIT: Here's my result

[ { results: [], team: 'MIN', year: 2020 },
  { results: [], team: 'TEX', year: 2006 },
  { results: [], team: 'MIL', year: 2005 },
  { results: [], team: 'TEX', year: 2008 },
  { results: [], team: 'TBR', year: 2021 },
  { results: [], team: 'SEA', year: 2018 },
  { results: [], team: 'TEX', year: 2007 },
  { results: [], team: 'MIN', year: 2019 },
  { results: [], team: 'SEA', year: 2017 },
  { results: [], team: 'TEX', year: 2013 },
  { results: [], team: 'TEX', year: 2011 },
  { results: [], team: 'TEX', year: 2012 },
  { results: [], team: 'SEA', year: 2016 },
  { results: [], team: 'TEX', year: 2010 },
  { results: [], team: 'SEA', year: 2015 },
  { results: [], team: 'BAL', year: 2014 },
  { results: [], team: 'MIN', year: 2021 },
  { results: [], team: 'TEX', year: 2009 } ]

CodePudding user response：

Put $match in the end of aggregate.

db.collection.aggregate([
  {
    $unwind: "$teams"
  },
  {
    $unwind: "$teams.years"
  },
  {
    $group: {
      _id: {
        team: "$teams.name",
        year: "$teams.years",
        
      },
      results: {
        $push: {
          "name": "$name",
          "id": "$_id"
        }
      }
    }
  },
  {
    $match: {
      "results.id": "/players/c/cruzne02.shtml"
    }
  }
])

mongoplayground