Parse json string using JSON.NET

Question

I have a string like the following in C#. I need to loop through and create an HTML table output. I tried with JSON.NET but couldn't figure out how to retrieve the keys (Name, Age & Job).

string data = "{items:[
{'Name':'AAA','Age':'22','Job':'PPP'}
,{'Name':'BBB','Age':'25','Job':'QQQ'}
,{'Name':'CCC','Age':'38','Job':'RRR'}]}";

The table format is

.........................  
| Name  | Age   | Job   |  
.........................  
| AAA   | 22    | PPP   |  
.........................  
| BBBB  | 25    | QQQ   |  
.........................  
| CCC   | 28    | RRR   |  
.........................   

Any help will be greatly appreciated.

---

The code provided by Dave is the ideal solution here.. but it work for .NET 4.0.. I have used following code with JSON.NET for .NET 3.5

using Newtonsoft.Json.Linq;

string jsonString = "{items:[{'Name':'Anz','Age':'29','Job':''},{'Name':'Sanjai','Age':'28','Job':'Developer'},{'Name':'Rajeev','Age':'31','Job':'Designer'}]}";

        JObject root = JObject.Parse(jsonString);

        JArray items = (JArray)root["items"];

        JObject item;
        JToken jtoken;

        for (int i = 0; i < items.Count; i++) //loop through rows
        {
            item = (JObject)items[i];
            jtoken = item.First;

            while (jtoken != null)//loop through columns
            {
                Response.Write(((JProperty)jtoken).Name.ToString() + " : " + ((JProperty)jtoken).Value.ToString() + "<br />");

                jtoken = jtoken.Next;
            }
        }

Solution 1

You can use .NET 4's dynamic type and built-in JavaScriptSerializer to do that. Something like this, maybe:

string json = "{\"items\":[{\"Name\":\"AAA\",\"Age\":\"22\",\"Job\":\"PPP\"},{\"Name\":\"BBB\",\"Age\":\"25\",\"Job\":\"QQQ\"},{\"Name\":\"CCC\",\"Age\":\"38\",\"Job\":\"RRR\"}]}";

var jss = new JavaScriptSerializer();

dynamic data = jss.Deserialize<dynamic>(json);

StringBuilder sb = new StringBuilder();

sb.Append("<table>\n  <thead>\n    <tr>\n");

// Build the header based on the keys in the
//  first data item.
foreach (string key in data["items"][0].Keys) {
        sb.AppendFormat("      <th>{0}</th>\n", key);
}

sb.Append("    </tr>\n  </thead>\n  <tbody>\n");

foreach (Dictionary<string, object> item in data["items"]) {
    sb.Append("    <tr>\n");

    foreach (string val in item.Values) {
        sb.AppendFormat("      <td>{0}</td>\n", val);
    }
}

sb.Append("    </tr>\n  </tbody>\n</table>");

string myTable = sb.ToString();

At the end, myTable will hold a string that looks like this:

<table>
    <thead>
        <tr>
            <th>Name</th>
            <th>Age</th>
            <th>Job</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>AAA</td>
            <td>22</td>
            <td>PPP</td>
        <tr>
            <td>BBB</td>
            <td>25</td>
            <td>QQQ</td>
        <tr>
            <td>CCC</td>
            <td>38</td>
            <td>RRR</td>
        </tr>
    </tbody>
</table>

Solution 2

If your keys are dynamic I would suggest deserializing directly into a DataTable:

    class SampleData
    {
        [JsonProperty(PropertyName = "items")]
        public System.Data.DataTable Items { get; set; }
    }

    public void DerializeTable()
    {
        const string json = @"{items:["
            + @"{""Name"":""AAA"",""Age"":""22"",""Job"":""PPP""},"
            + @"{""Name"":""BBB"",""Age"":""25"",""Job"":""QQQ""},"
            + @"{""Name"":""CCC"",""Age"":""38"",""Job"":""RRR""}]}";
        var sampleData = JsonConvert.DeserializeObject<SampleData>(json);
        var table = sampleData.Items;

        // write tab delimited table without knowing column names
        var line = string.Empty;
        foreach (DataColumn column in table.Columns)            
            line += column.ColumnName + "\t";                       
        Console.WriteLine(line);

        foreach (DataRow row in table.Rows)
        {
            line = string.Empty;
            foreach (DataColumn column in table.Columns)                
                line += row[column] + "\t";                                   
            Console.WriteLine(line);
        }

        // Name   Age   Job    
        // AAA    22    PPP    
        // BBB    25    QQQ    
        // CCC    38    RRR    
    }

You can determine the DataTable column names and types dynamically once deserialized.

Solution 3

I did not test the following snippet... hopefully it will point you towards the right direction:

    var jsreader = new JsonTextReader(new StringReader(stringData));
    var json = (JObject)new JsonSerializer().Deserialize(jsreader);
    var tableRows = from p in json["items"]
                 select new
                 {
                     Name = (string)p["Name"],
                     Age = (int)p["Age"],
                     Job = (string)p["Job"]
                 };

Comments

[Ansar Muhammad]

Hi, Thank you for the reply.. my problem is actually the Keys (Name, Age and Job) are not fixed.. it can vary.. so I need to retrieve the keys also from the json string... is there anyway to get it dynamically?

[Frank]

The above solution works for .NET versions prior to 4.0. The dynamic version proposed by Dave is nice too.

[Frank]

Nicely done. Is there a performance overhead associated with using dynamic as opposed to a DataTable? I am still using .NET 3.5 so I haven't used dynamic much in practice.

[rlorenzo]

@Frank Dynamics do carry an added overhead, but it's not that bad. Running the code above averages 9ms in LINQPad on my Core i7.

[Ansar Muhammad]

@Dave.. many thanks for the code snipplet.. but unfortunately my project is .NET 3.5 and couldnt use your solution.. I figured out a solution using JSON.NET for this.. if anyone else needs .NET 3.5 solution I have posted sample code above..

[Ansar Muhammad]

This solution also needs the JSON keys (Name, Age etc) predefined!!

[Frank]

@Anz The column names do not need to be predefined. They are inferred when the DataTable is deserialized. Please see my update above that demonstrates how to print the table without explicitly knowing the column names.

[Admin]

The following line var sampleData = JsonConvert.DeserializeObject<SampleData>(json); Returns error "Cannot desrialize to array