How to convert JSON to Datatable

Question

I have a JSON below:

{
    "open": [
                3978,3856,3925,3918,3877.85,3992.7,4033.95,4012,3910,3807,3840,3769.5,3731,3646,3749,
                3770,3827.9,3851,3815.3,3791
            ],
    "high": [
            3978,3925,3929,3923,3977,4043,4041.7,4012,3920,3851.55,3849.65,3809.4,3733.4,3729.8,
            3758,3808,3864,3882.5,3824.7,3831.8
            ],
    "low":  [
            3861,3856,3836.55,3857,3860.05,3962.3,3980,3910.5,3811,3771.1,3740.1,3722.2,3625.1,
            3646,3721.4,3736.4,3800.65,3816.05,3769,3756.15
            ],
    "close": [
            3879.85,3915.9,3859.9,3897.9,3968.15,4019.15,3990.6,3914.65,3826.55,3833.5,3771.35,
            3769.9,3649.25,3690.05,3736.25,3800.65,3856.2,3824.6,3814.9,3779
            ],
    "volume":[
            3937092,1906106,3203744,6684507,3348123,3442604,2389041,3102539,6176776,3112358,     
            3258414,3330501,5718297,3143862,2739393,2105169,1984212,1960538,2307366,1919149
            ],
    "start_Time": [
            1326220200,1326306600,1326393000,1326479400,1326565800,1326825000,1326911400,
            1326997800,1327084200,1327170600,1327429800,1327516200,1327689000,1327775400,
            1328034600,1328121000,1328207400,1328293800,1328380200,1328639400
            ]
}

Also, I installed the NuGet package Newtonsoft.Json.

I tried the code:

var unwrappedJson = JsonConvert.DeserializeObject<string>(result);
DataTable dataTable = (DataTable)JsonConvert.DeserializeObject(unwrappedJson, (typeof(DataTable)));

but get an error.

Answer 1

As mentioned in the comment, your JSON structure which is an object in which each field is an array of values doesn't support deserializing into a DataTable.

You need to expand/transpose the object data into an array of objects (Sorry, I am not sure which English term is suitable to describe).

[
  { "high": /* value */, "low": /* value */, ... }
]

For the summary of implementation, you need:

1. Get the column name(s) with the field(s) in the object.

2. Get the number of rows by the number of values in the fields' array (Assume that all the fields have the same number).

3. Get the number of columns.

4. Iterate each row and column (index) to get the respective value by indexes to form an array of values for each row.

5. Add the array into DataRow.ItemArray and add the DataRow into the DataTable.

public DataTable ExpandObjectToDataTable(JObject jObj)
{
    DataTable dt = new DataTable();

    if (jObj == null)
        return null;

    List<string> columns = jObj.Properties()
        .Select(x => x.Name)
        .ToList();

    // Add columns
    dt.Columns.AddRange(columns.Select(x => new DataColumn(x)).ToArray());

    var arrayValues = jObj.Values();
    // Get number of rows by calculating the number of values in the field
    int totalRow = arrayValues.Select(x => x.Count()).Max();
    int totalColumn = columns.Count;

    // Iterate row
    for (int i = 0; i < totalRow; i++)
    {
        DataRow row = dt.NewRow();
        List<object> values = new List<object>();

        // Iterate field
        for (int j = 0; j < totalColumn; j++)
        {
            values.Add(arrayValues.ElementAt(j).ElementAt(i));
        }

        row.ItemArray = values.ToArray();
        dt.Rows.Add(row);
    }       

    return dt;
}

Caller:

JObject jObj = JObject.Parse(json);
DataTable dt = ExpandObjectToDataTable(jObj);