How to specify multiple nested documents in mongoimport?

Question

I have the following CSV:

matchId,       score, players.Name, players.Goals
2730319610399, 5-0,   John,         3

When I use mongoimport on Studio 3T it is imported in the form I need because of the dot notation:

{ 
    "matchId" : "2730319610399", 
    "score" : "5-0", 
    "players" : {
        "Name" : "John", 
        "Goals" : "3"
    }
}

My issue is that the csv actually has one more player that I want to add in this import. The array of "players" has two entries.

This is the actual CSV format:

matchId,       score, players.Name, players.Goals, players.Name, players.Goals
2730319610399, 5-0,   John,         3,             Kyle,         2

But this does not work and I get an error of:

Every row will be parsed as one column.

The header contains a duplicate name "players.Name" in columns: [3, 5]

Is it possible to format the CSV so that I can add multiple values into the "players" array? I was thinking of naming it something like players[0].Name and players[1].Name

But that doesn't work because it creates two arrays: players[0] and players[1]

This is what I need the database structure to look like:

{ 
    "matchId" : "2730319610399", 
    "score" : "5-0", 
    "players" : {
        "Name" : "John", 
        "Goals" : "3"
    },
    {
        "Name" : "Kyle", 
        "Goals" : "2"
    }
}

score 0 · Answer 1 · answered Dec 23 '20 at 13:28

0

Try with this:

matchId,    score,     players.Name,     players.Goals

2730319610399,    5-0,    John.Kyle,             3.2

then, use:

db.collection.find().snapshot().forEach(function (test) {
   test.players.Name = test.players.Name.split('.');  
 db.whatevercollection.save(test); 
});

db.collection.find().snapshot().forEach(function (test) {
   test.players.Goals = test.players.Goals.split('.');  
 db.whatevercollection.save(test); 
});

answered Dec 23 '20 at 13:28

MauriRamone

469
1
3
21

Ah thanks for the suggestion. I have edited my question and added the format I need it in. This answer splits the Name and Goals fields but that structure doesn't look ideal to me as the "Goals" of 2 belong to Kyle so I want each player and their stats in their own subcollection – user2633709 Dec 24 '20 at 09:09
ok now I understand. I added another answer with an option to do it. regards – MauriRamone Dec 28 '20 at 13:30

score 0 · Answer 2 · answered Dec 28 '20 at 13:29

0

ok, so the best option would be to import a json file instead of a csv. For example:

{ "matchId":"2730319610399","score":"5-0","players":[{"Name":"John","Goals":"3"},{"Name":"Kyle","Goals":"2"}]}
{ "matchId":"2830319610399","score":"1-0","players":[{"Name":"Mauri","Goals":"1"}]}

then use mongoimport as follows:

mongoimport --db=TestDB --collection=TestCol --type=json example_file.json

you should see something like this on Robo3T:

answered Dec 28 '20 at 13:29

MauriRamone

469
1
3
21

Hi! Thanks again for the suggestion. My issue is that my current data is in an excel row. So each row is an entry and then each player’s information is currently in a separate column on the same row because each row is a match. So I need a way to get the data in this excel format into mongodb. My current workaround is to create a subdocument with each player’s name but this isn’t ideal because if a new player joins I’ll have to do a lot of changes – user2633709 Dec 28 '20 at 19:04

How to specify multiple nested documents in mongoimport?

2 Answers2