C# deserialize JSON without custom class into Dictionary or DataTable

Question

from an API I get a json like this:

66 results of an player with somewhat 31 attributes containing single values or an array of values.

{"api":
{"results":66,
    "players":
        [{
            "player_id":10,
            "player_name":"Gustavo Ferrareis",
            ... (some 31 stats)
            "shots":{
                    "total":13,
                    "on":2
                },
            ...
          },
            "player_id":21,
            ...
       }]
     }

And I wanted to know if there's a way to deserialize the collection of players into an Dictionary or better DataTable with all 31 attributes without a custom player class or accessing every attribute individually?

So far I tried accessing the players list by:

        var data = JObject.Parse(json);
        foreach (var field in data)
        {
            var data2 = JObject.Parse(field.Value.ToString());
            foreach (var field2 in data2)
            {
                if (field2.Key.ToString() == "players")
                {
                    dynamic array2 = JsonConvert.DeserializeObject(field2.Value.ToString());

                    foreach (var field3 in array2)
                        Console.WriteLine("Player_id: " + field3.player_id.ToString() + " - Player_name: " + field3.player_name.ToString());
                }
            }
        }

which returns

Player_id: 10 - Player_name: Gustavo Ferrareis
Player_id: 22 - Player_name: Getúlio
Player_id: 22 - Player_name: Getúlio

I imagine something like:

Dictionary<string, object> dict = new Dictionary<string, object>();

foreach (var player in array2)
    dict.Add(player.Key(), player.Value());

The answer can't be that I have to make an custom player class and then use that?

Open for any advice. Thank you.

Are you able to get into this for loop - foreach (var field in data)? Asking because its JObject not JArray — sam, Jan 22 '20 at 14:05
looks like this: https://stackoverflow.com/questions/3142495/deserialize-json-into-c-sharp-dynamic-object — Daniel Stackenland, Jan 22 '20 at 15:13

Johnathan Barclay · Answer 1 · 2020-01-22T14:14:34.533

1

You could parse into IEnumerable<string> like this:

IEnumerable<string> = JObject.Parse(json)["players"]
    .Children()
    .Select(jo => $"Player_id: {jo["player_id"]} - Player_name: {jo["player_name"]});

A similar approach would work for Dictionary using ToDictionary instead of Select, but it depends on what you consider key and value.

edited Jan 22 '20 at 14:14

answered Jan 22 '20 at 14:05

Johnathan Barclay

18,599
1
22
35

I am going to try this, but wouldn't I still have to access each attribute individually? – Daniel Maurer Jan 22 '20 at 14:53
@DanielMaurer no, it only accesses `player_id` and `player_name`. – Johnathan Barclay Jan 22 '20 at 14:56
1

I get an NullReferenceException when trying this. – Daniel Maurer Jan 22 '20 at 17:13

Krishna Varma · Answer 2 · 2020-01-22T15:32:49.100

1

Here is the single line code to get the playerid and playername to List or Dictionary

//To List
var resultToList = JObject.Parse(jsonstring)["api"]["players"]
                   .Select(p => (p["player_id"].ToString(), p["player_name"].ToString()))
                   .ToList();

//To Dictionary
var resultToDict = JObject.Parse(jsonstring)["api"]["players"]
                   .Select(p => (p["player_id"].ToString(), p["player_name"].ToString()))
                   .ToDictionary(x=>x.Item1, y=>y.Item2);

edited Jan 22 '20 at 15:32

answered Jan 22 '20 at 14:22

Krishna Varma

4,238
2
10
25

But I would still have to access each attribute individually, right? Is there no "easier" way? – Daniel Maurer Jan 22 '20 at 14:50
Sorry, but I want to get the whole player entry into a DataTable or Dictionary. All the 31 attributes. – Daniel Maurer Jan 22 '20 at 17:05

sam · Accepted Answer · 2020-01-22T19:27:28.267

1

You can use Newtonsoft.Json.Linq and get the required result as shown below:

var jObject = JObject.Parse(jsonFromAPI)["api"];
var formattedPlayers = jObject["Players"].Children()
                .Select(p => $"Player_id: {p["player_id"]} - Player_name: {p["player_name"]}");

or if you wanted dictionary, then use below:

var playersDictionary = jObject["Players"].Children().Select(p => new {player_id = p["player_id"], player_name = p["player_name"]}).ToDictionary(x => x.player_id, v => v.player_name);

If you want to display all properties of Players, then you need to run loop something like below:

var allPlayerDetails = new List<Dictionary<string, object>>();
foreach (JObject player in jObject["Players"].Children())
{
    var playerDictionary = player.Properties()
        .ToDictionary<JProperty, string, object>(property => property.Name, property => property.Value);

    allPlayerDetails.Add(playerDictionary);
}

for (var index = 0; index < allPlayerDetails.Count; index++)
{
    var playerDictionary = allPlayerDetails[index];
    Console.WriteLine(Environment.NewLine);
    Console.WriteLine(string.Format("Printing Player# {0}", index));
    foreach (var d in playerDictionary)
    {
        Console.WriteLine(d.Key + " - " + d.Value);
    }
}

If you want to convert to DataTable from list of players, then you can do something like below:

DataTable dt = new DataTable();

foreach (var column in allPlayerDetails.SelectMany(p => p.Keys).Select(k => k.Trim()).Distinct())
{
    dt.Columns.Add(new DataColumn(column));
}

foreach (var details in allPlayerDetails)
{
    var dr = dt.NewRow();
    foreach (DataColumn dc in dt.Columns)
    {
        dr[dc.ColumnName] = details.ContainsKey(dc.ColumnName) ? details[dc.ColumnName] : null;
    }
    dt.Rows.Add(dr);
}

Fiddler can be found here.

edited Jan 22 '20 at 19:27

answered Jan 22 '20 at 15:07

sam

1,937
1
8
14

This works so far, but I want to get all 31 attributes from the player, is there a simple way like in SQL "SELECT * "? – Daniel Maurer Jan 22 '20 at 17:17
@DanielMaurer What is the expectaction here? after accessing all the properties, you want to retrieve something or what? please explain the usecase clearly. Do you want to populate a dictionary with property name and its values? if that is the case, how do you want to go with shots object? – sam Jan 22 '20 at 17:30
@DanielMaurer Updated my answer. Please verify when you get a chance. – sam Jan 22 '20 at 17:58
@DanielMaurer I have updated my answer to populate DataTable also. – sam Jan 23 '20 at 14:26
The adapted answer works just the way I wanted it. Thank you very much for your help. – Daniel Maurer Jan 28 '20 at 09:06
I still have a issue where the attribute "shots" gets the whole shots json. How can I get Shots_total - 13 & Shots_on 2? – Daniel Maurer Jan 30 '20 at 13:51
1

@DanielMaurer your requirement only mentioned about 31 attributes under player but did not talk about shots. Since shots is an object, how do you want column names? – sam Jan 30 '20 at 14:24
1

you are correct. I found a way to solve this. still thank you – Daniel Maurer Jan 30 '20 at 14:51

C# deserialize JSON without custom class into Dictionary or DataTable

3 Answers3