How to lookup and match multiple keys in array of objects Mongodb?

Question

I'm using MongoDB, and I tried to do it with many pipeline steps but I couldn't find a way.

I have a team_squads collection, where a document looks like this with an array players that contain objects where a players is present in one of the objects.

    {
        "seasonId": 363,
        "seasonLabel": "2020/21",
        "teamId": 1,
        "players": [
            {
                "appearances": 12,
                "p_id": 4985
            },
            ....
        ]
    },

I have a pipeline from an other collection that returns the following

  {
    totalPlaytime: 1058,
    averagePasses: 58.52551984877127,
    name: 'Mateusz Klich',
    id: 13089,
    seasonId: 363,
    appearances: 12,
    averagePlaytime: 88.16666666666667
  }

What I want to do is get the field teamId and appearances by matching id with p_id and matching seasonId. How can this be done, I'm having not been successful in creating a pipeline that does this.

The expected result is,

  {
    totalPlaytime: 1058,
    averagePasses: 58.52551984877127,
    name: 'Mateusz Klich',
    id: 13089,
    seasonId: 363,
    appearances: 12,
    averagePlaytime: 88.16666666666667
    appearances: 12,
    teamId: 1,
  }

MisterButter · Accepted Answer · 2020-12-26 22:38:13Z

Found answer in an other post where the trick is to match the top level field first and unwind the array of interest before matching the field in the array. Below is the pipeline.

    .lookup({
        'from': 'team_squads',
        'let': {'id': '$id', 'seasonId': '$seasonId'},
        'pipeline': [
            { '$match': {"$expr": { '$eq': [ "$seasonId", "$$seasonId" ] } } },
            { "$unwind": "$players" },
            { "$match": { "$expr": { "$eq": ["$players.p_id", "$$id"] } } },
         ],
        'as': 'player_stats'
    })
    .unwind(
        'player_stats'
    )
    .project({
        'totalPlaytime': 1,
        'total_pass': 1,
        'averagePasses': 1,
        'name': 1,
        'id': 1,
        'seasonId': 1,
        'teamId': '$player_stats.teamId',
        'teamName': '$player_stats.teamName',
        'appearances': '$player_stats.players.appearances',
        '_id': 0
    })

Collectives™ on Stack Overflow

How to lookup and match multiple keys in array of objects Mongodb?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related