Deserialize JSON nested arrays to DataTable using C#

Question

I need help deserialize JSON output to a datatable in C# using Newtsonsoft.json (JSON.NET). I have looked at many examples an diskussions on this and many other forums but i can not figure out how to do it.

JSON sample to deserialize:

[{
"display_name": "Check MWExternal",
"plugin_output": "MWExternal.exe: not running",
"host": {
  "name": "WIN2008.arlaplast.local"
} },{
"display_name": "Swap usage",
"plugin_output": "Paging File usage is  = 39.19 %",
"host": {
  "name": "srvdccz01.arlaplast.local"
}},{
"display_name": "Swap usage",
"plugin_output": "Paging File usage is  = 40 %",
"host": {
  "name": "srvdccz02.arlaplast.local"
}}]

The response structure always looks the same but can have many blocks

I want this in a table looking like this

Table example

Here are my code so far: (Only gives me First two columns in table, not the host.name)

    public partial class test : System.Web.UI.Page
{


protected void Page_Load(object sender, EventArgs e)
{
    PopulateList();
}

    public class Host
    {
        public string name { get; set; }
    }

    public class RootObject
    {
        public string display_name { get; set; }
        public string plugin_output { get; set; }
        public Host host { get; set; }
    }

    public static DataTable ToDataTable<T>(List<T> items)
{
    DataTable dataTable = new DataTable(typeof(T).Name);

    //Get all the properties
    System.Reflection.PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    foreach (PropertyInfo prop in Props)
    {
        //Defining type of data column gives proper data table 
        var type = (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType);

            //Setting column names as Property names
        dataTable.Columns.Add(prop.Name, type);
    }
    foreach (T item in items)
    {
        var values = new object[Props.Length];
        for (int i = 0; i < Props.Length; i++)
        {
            //inserting property values to datatable rows
            values[i] = Props[i].GetValue(item, null);
        }
        dataTable.Rows.Add(values);
    }
    //put a breakpoint here and check datatable
    return dataTable;
}


public void PopulateList()
{
    string uri = "https://op5.ateavdc.se/api/filter/query?query=[services]%20state!=0&columns=display_name,plugin_output,host.name";
    string _auth = string.Format("{0}:{1}", "USERNAME", "PASSWÒRD");
    string _enc = Convert.ToBase64String(Encoding.ASCII.GetBytes(_auth));
    string _cred = string.Format("{0} {1}", "Basic", _enc);

    try
    {
        dynamic webRequest__1 = (HttpWebRequest)WebRequest.Create(uri);
        webRequest__1.Headers.Add("Authorization", _cred);
        webRequest__1.ContentType = "application/json; charset=utf-8";
        webRequest__1.Method = "GET";
        dynamic webResponse = (HttpWebResponse)webRequest__1.GetResponse();

        if (webResponse.StatusCode == HttpStatusCode.OK)
        {
            dynamic reader = new StreamReader(webResponse.GetResponseStream());
            string s = reader.ReadToEnd();
                TextBox1.Text = s.ToString();

                var data = JsonConvert.DeserializeObject<List<RootObject>>(s);
                DataTable dt = ToDataTable(data);
                GridView1.DataSource = dt;
                GridView1.DataBind();

            }
        else
        {
                Response.Write(webResponse.StatusCode);
        }
    }
    catch (Exception ex)
    {
            Response.Write(ex.Message);
        }}}

Possible duplicate of [Convert JSON to DataTable](https://stackoverflow.com/questions/11981282/convert-json-to-datatable) — Riv, Oct 04 '17 at 09:00
I would say that you need to create a new type, with a collection of the blocks that may be repeated and deserialise it to that type? — rmjoia, Oct 04 '17 at 09:02
http://json2csharp.com/ is great to help creating the classes to use for the deserialization. — StefanE, Oct 04 '17 at 09:08
i can deserialize it to a datatable for first two colums (display_name and plugin_ouput) but i can not figure out how to access the host.name data. also i have created the classes using json2chsharp tool. — David Lindström, Oct 04 '17 at 09:11

score 0 · Accepted Answer · answered Oct 04 '17 at 09:21

Thinking that you know how the nested Json is going to come up, based on the example provided, lets create the class,

public class Host
{
   public string name { get; set; }
}

public class RootObject
{
    public string display_name { get; set; }
    public string plugin_output { get; set; }
    [JsonProperty("host")]
    public Host h { get; set; }
    public string Host { get { return this.h.name;} set {this.h.name = value;}}
}

Now lets do the DeserializeObject using NewtonSoft

string s = "[{\"display_name\": \"Check MWExternal\",\"plugin_output\": \"MWExternal.exe: not running\",\"host\": {  \"name\": \"WIN2008.arlaplast.local\"} },{\"display_name\": \"Swap usage\",\"plugin_output\": \"Paging File usage is  = 39.19 %\",\"host\": {  \"name\": \"srvdccz01.arlaplast.local\"}},{\"display_name\": \"Swap usage\",\"plugin_output\": \"Paging File usage is  = 40 %\",\"host\": {  \"name\": \"srvdccz02.arlaplast.local\"}}]";

JsonConvert.DeserializeObject<List<RootObject>>(s);

You will get the output as below,

Then if you filter against that using linq,

JsonConvert.DeserializeObject<List<RootObject>>(s).Select(x => new {x.display_name, x.plugin_output, x.Host})

Hope this helps..!

Will this approach also work if for some reason the host is missing? — David Lindström, Oct 04 '17 at 14:17
For that in the above code that was provided, change the below line from `public string Host { get { return this.h.name;} set {this.h.name = value;}}` to `public string Host { get { return this.h != null ? this.h.name : null;} set {this.h.name = value ?? null;}}` so it will return null or you can assign a value you like. — Thennarasan, Oct 04 '17 at 14:43

score 0 · Answer 2 · answered Oct 04 '17 at 09:37

Simplest solution:

var json = File.ReadAllText("data.json");

var arr = JArray.Parse(json);

var data = arr.Select(x => new
{
    DisplayName = x.Value<string>("display_name"),
    PluginOutput = x.Value<string>("plugin_output"),
    Host = x.Value<JToken>("host").Value<string>("name")
})
.ToList();

Deserialize JSON nested arrays to DataTable using C#

2 Answers2