One doesn't always know the type into which to deserialize. So it would be handy to be able to take any JSON (that contains some array) and dynamically produce a table from that.
An issue can arise however, where the deserializer doesn't know where to look for the array to tabulate. When this happens, we get an error message similar to the following:
Unexpected JSON token when reading DataTable. Expected StartArray, got StartObject. Path '', line 1, position 1.
Even if we give it come encouragement or prepare our json accordingly, then "object" types within the array can still prevent tabulation from occurring, where the deserializer doesn't know how to represent the objects in terms of rows, etc. In this case, errors similar to the following occur:
Unexpected JSON token when reading DataTable: StartObject. Path '[0].__metadata', line 3, position 19.
The below example JSON includes both of these problematic features:
{
"results":
[
{
"Enabled": true,
"Id": 106,
"Name": "item 1",
},
{
"Enabled": false,
"Id": 107,
"Name": "item 2",
"__metadata": { "Id": 4013 }
}
]
}
So how can we resolve this, and still maintain the flexibility of not knowing the type into which to derialize?
Well here is a simple approach I came up with (assuming you are happy to ignore the object-type properties, such as __metadata in the above example):
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Data;
using System.Linq;
...
public static DataTable Tabulate(string json)
{
var jsonLinq = JObject.Parse(json);
// Find the first array using Linq
var srcArray = jsonLinq.Descendants().Where(d => d is JArray).First();
var trgArray = new JArray();
foreach (JObject row in srcArray.Children<JObject>())
{
var cleanRow = new JObject();
foreach (JProperty column in row.Properties())
{
// Only include JValue types
if (column.Value is JValue)
{
cleanRow.Add(column.Name, column.Value);
}
}
trgArray.Add(cleanRow);
}
return JsonConvert.DeserializeObject<DataTable>(trgArray.ToString());
}
I know this could be more "LINQy" and has absolutely zero exception handling, but hopefully the concept is conveyed.
We're starting to use more and more services at my work that spit back JSON, so freeing ourselves of strongly-typing everything, is my obvious preference because I'm lazy!
