Query nested JSON in MongoDB

Question

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 :)

you are using data on keys, and that makes queries hard, think about replacing teamsData with an array, and make the key part of the embeded document. Unknown keys make data querying/updating hard — Takis
– Takis, Commented Oct 10, 2021 at 8:37

Takis · Accepted Answer · 2021-10-10 09:44:53Z

1

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

answered Oct 10, 2021 at 9:44

Takis

8,7552 gold badges17 silver badges28 bronze badges

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

3 Comments

Luiz Felipe Freitas Over a year ago

Thanks @Takis _. This is exactly what I was looking for.

Takis Over a year ago

ok good, think about using arrays for data, because unknown keys are hard in MongoDB. saving data on your schema(as field names for example) is making the queries harder in general.

Luiz Felipe Freitas Over a year ago

Thanks again. I will study more about $map and $objectToArray. This is a JSON file made publicly available from Russia 2018 World Cup and I though it would be more interesting to practice in a real dataset, with real issues instead of those perfectly examples we found in the documentations.

ray · Accepted Answer · 2021-10-10 04:07:01Z

0

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:

$objectToArray in $addFields to convert the teamsData into an array of k-v tuples
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.
$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
$arrayToObject to convert the teamsData back into your desired form of output

Here is the Mongo playgronud for your reference.

answered Oct 10, 2021 at 4:07

ray

15.6k12 gold badges96 silver badges104 bronze badges

1 Comment

Luiz Felipe Freitas Over a year ago

Thanks @ray. I will study more about it to undestand the steps you suggested. This is a public dataset from the 64 matches of 2018 World Cup, so I want a query that works for both teams on 64 matches. I though It would be challenging to practice with a real dataset, that comes with real issues we face right? Really appreciate your help :)

Collectives™ on Stack Overflow

Query nested JSON in MongoDB

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related