so I have the following nested collection in a MongoDB database:

{'_id': ObjectId('615e9b8d17fa084f2e8d4b83'),
 'status': 'Played',
 'roundId': 4165363,
 'gameweek': 2,
 'teamsData': {'12274': {'scoreET': 0,
                         'coachId': 89543,
                         'side': 'home',
                         'teamId': 12274,
                         'score': 0,
                         'scoreP': 0,
                         'hasFormation': 1,
                         'formation': {'bench': [{'playerId': 101652,
                                       'assists': '0',
                                       'goals': 'null',
                                       'ownGoals': '0',
                                       'redCards': '0',
                                       'yellowCards': '0'},
                                        ....
                                        {'playerId': 90991,
                                       'assists': '0',
                                       'goals': 'null',
                                       'ownGoals': '0',
                                       'redCards': '0',
                                       'yellowCards': '0'},
            '9598': {'scoreET': 0,
                     'coachId': 122788,
                     'side': 'away',
                     'teamId': 9598,
                     'score': 3,
                     'scoreP': 0,
                     'hasFormation': 1,
                     'formation': {'bench': [{'playerId': 69964,
                                   'assists': '0',
                                   'goals': 'null',
                                   'ownGoals': '0',
                                   'redCards': '0',
                                   'yellowCards': '0'},
                                    ....
                                    'lineup': [{'playerId': 69616,
                                    'assists': '0',
                                    'goals': '1',
                                    'ownGoals': '0',
                                    'redCards': '0',
                                    'yellowCards': '39'}

I want a list of playerId and goals and I know I can project something like

list(db.matches.find({},
                     projection = ['teamsData.9598.formation.lineup.playerId', 'teamsData.9598.formation.lineup.goals']))

which would bring me

[{'_id': ObjectId('615e9b8d17fa084f2e8d4b83'),
  'teamsData': {'9598': {'formation': {'lineup': [{'playerId': 69616, 'goals': '1'},
      {'playerId': 14812, 'goals': 'null'},
      {'playerId': 69409, 'goals': 'null'},
      {'playerId': 25393, 'goals': 'null'},
      {'playerId': 135747, 'goals': 'null'},
      {'playerId': 3476, 'goals': '1'},
      {'playerId': 105361, 'goals': 'null'},
      {'playerId': 8287, 'goals': '1'},
      {'playerId': 69396, 'goals': 'null'},
      {'playerId': 69968, 'goals': 'null'},
      {'playerId': 14943, 'goals': '0'}]}}}}]

The problem is that I don't want to write down 9598 part in the project code (and then for each teamId), otherwise I won't be able to scale it through whole collection. Is there anyone know how can I take playerId and goals for all teams?

I read several questions regarding nested JSON but none in a structure like that, so I appreciate any help. Thanks :)

CodePudding user response：

From my personal experience, I do not recommend using dynamic value as key for MongoDB documents as it would be hard to maintain and difficult to compose queries.

Nevertheless, you may do the followings in an aggregation pipeline:

1. $objectToArray in $addFields to convert the teamsData into an array of k-v tuples
2. Here I take a guess that you just want team 9598's line up data. So I did a $unwind followed by a $match to filter out the data.
3. $project the teamsData back into an array of k-v tuples, which k is the team ID and v is the playerId and goals tuples in formation.lineup
4. $arrayToObject to convert the teamsData back into your desired form of output

Here is the Mongo playgronud for your reference.

CodePudding user response：

Query

• for all documents and for all teams keys
• gives a list of playerId and goals, for every team
• converts to array the object
• $map on members, keep only team-key and only the playerId and goals

*i dont know what you really want, but maybe this can help.
*if you want for a specific team-key, you can filter the array with $filter

Test code here

aggregate(
[{"$project": 
    {"teamsData": 
      {"$map": 
        {"input": {"$objectToArray": "$teamsData"},
          "in": 
          {"$mergeObjects": 
            [{"teamId": "$$this.k"},
              {"players-goals": 
                {"$map": 
                  {"input": "$$this.v.formation.lineup",
                    "in": {"playerId": "$$t.playerId", "goals": "$$t.goals"},
                    "as": "t"}}}]}}}}}])