CODEWITHSUNDEEP
mongodbaggregation-framework
j-p
j-p

Reputation: 3818

MongoDB aggregate lookup with nested array

I have a complicated structure I am trying to "join".

The best way to describe it is that I have "Favorite Teams" stored with a user, as an array of name/IDs - however they are stored in a nested object. I want to return the users Favorite Teams Players WITH the team.

Here are the data models

PLAYERS
{
    _id:
    team_id:
    name:
    position:
}


TEAMS
{   
    _id:
    name:
}


USER
{
    _id:
    name:
    favs: {
        mascots: [{
            _id:
            name:       
        }],
        teams: [{
            _id:
            name:       
        }],
    }
}

I have an array of Team IDs from the user.favs.teams - and what I want back is the players with their team name.

This is the current aggregation I am using - it is returning the players but not the teams...I am pretty sure I need to unwind, or similar.

players.aggregate([
    {
        $match: {
            team_id: {
                $in: [--array of team ID's--]
            }
        }
    },
    {
        $lookup: {
            from: 'teams',
            localField: 'team_id',
            foreignField: '_id',
            as: 'players_team'
        }
    },
    {
        $project: {
            _id: 1,
            name: 1,
            position: 1,
            'players_team[0].name': 1
        }
    }
])

What I am getting back...

_id: 5c1b37b6fd15241940b11111
name:"Bob"
position:"Test"
team_id:5c1b37b6fd15241940b441dd
player_team:[   
    _id:5c1b37b6fd15241940b441dd
    name:"Team A"
    ...other fields...
]

What I WANT to get back...

_id: 5c1b37b6fd15241940b11111
name:"Bob"
position:"Test"
team_id:5c1b37b6fd15241940b441dd
player_team: "Team A"

Upvotes: 0

Views: 1386

Answers (1)

Ashok
Ashok

Reputation: 2932

Use Below $lookup (Aggregation)

db.players.aggregate([
  {
    $lookup: {
      from: "teams",
      let: { teamId: "$team_id" },
      pipeline: [
        {
          $match: { $expr: { $eq: [ "$_id", "$$teamId" ] } }
        },
        {
          $project: {  _id: 0 }
        }
      ],
      as: "players_team"
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          {
            "_id": "$_id",
            "name": "$name",
            "position": "$position",
            "team_id": "$team_id"
          },
          {
            player_team: { $arrayElemAt: [ "$players_team.name", 0 ] }
          }
        ]
      }
    }
  }
])

Sorry If your MongoDB version is less then 3.6. Because of new changes in MongoDB 3.6.

Upvotes: 1

Related Questions