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

Question

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 } ]

Show show us some input data. I doubt you need all these $unwind and $group — Wernfried Domscheit
– Wernfried Domscheit, Commented Sep 21, 2021 at 20:54
I'm trying to get all the teammates of the player with the id in the match part of the aggregate. I want to a return an array of objects that contain an array of documents, for the specific team name and year. So I want all the players that have team.name equal to MIL and team.years equal to 2005. Do that for each year inside the given player. — laneherby
– laneherby, Commented Sep 21, 2021 at 21:16
In the $lookup stage, "$teams.name" will be an array, which will never be $eq a string, and $teams.years will be an array of arrays of integers, which will never be $eq an integer. If I get some time later, I'll see if I can think of a solution to this. — Joe
– Joe, Commented Sep 21, 2021 at 22:55
This is 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 }, ] etc. — laneherby
– laneherby, Commented Sep 21, 2021 at 23:09

YuTing · Accepted Answer · 2021-09-22 01:55:09Z

1

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

edited Sep 22, 2021 at 1:55

answered Sep 22, 2021 at 1:02

YuTing

6,6692 gold badges8 silver badges19 bronze badges

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

4 Comments

laneherby Over a year ago

This is close but I added another player that overlaps with this player and should show up for "MIN":2020 and "MIN":2021 mongoplayground

YuTing Over a year ago

i update my answer

laneherby Over a year ago

I supplied the $match because I want to get every teammate of that specific player with that id not just every team in the whole database which is what the query above does.

YuTing Over a year ago

i update my answer again, put match in the end of aggregate

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related