How to convert a json with nested properties to data table in c#

Question

How can I convert a Json with nested properties into a DataTable for export as a csv file in a generic manner using c#. A sample json as follows:

{
  "Length": {
    "Value": 12.93,
    "Unit": "m"
  },
  "Temperature": {
    "Value": 28.32,
    "Unit": "DegC"
  },
  "Color": "Blue"
}

which I would like to flatten out in a table as follows:

"Property"    | "Value"  | "Unit"
---------------------------------
"Length"      | 12.93    | "m"
"Temperature" | 28.32    | "DegC"
"Color"       | "Blue"   |

Note that the Color property is not nested whereas the Length and Temperature properties are. In the case of a unit-less property such as Color nothing (or null) should be added to the "Unit" column in the data table.

Additionally the nested properties would mostly be of type double, but could contain any struct type.

Convert JSON to DataTable 's solution is the closest I have come, but of course does not work for the nested properties I have.

score 1 · Accepted Answer · answered Sep 22 '22 at 14:30

you can try this code

    var jsonParsed = JObject.Parse(json);

    var jsonArr = new JArray();

    foreach (var prop in jsonParsed.Properties())
    {
        var jObj = new JObject { ["Property"] = prop.Name };

        var valueType = prop.Value.GetType().Name;

        if (valueType == "JValue")
        {
            jObj["Value"] = (string)prop.Value;
            jObj["Unit"] = string.Empty;
        }
        else if (valueType == "JObject")
        {
            var nestedProp = (JObject)prop.Value;
            jObj["Value"] = (string)nestedProp["Value"];
            jObj["Unit"] = nestedProp["Unit"];
        }

        jsonArr.Add(jObj);
    }
    DataTable dataTable = jsonArr.ToObject<DataTable>();

Hi @Serge. I eventually solved it like below, but I tested your answer which is of course also suitable. Thanks for the effort. — Francois Louw, Sep 23 '22 at 07:22

score 0 · Answer 2 · answered Sep 23 '22 at 07:17

I eventually solved it directly by creating a DataTable and populating it as follows:

DataTable dataTable = new();

DataColumn[] dataColumns = new DataColumn[3];
dataColumns[0] = new DataColumn("Property");
dataColumns[1] = new DataColumn("Value");
dataColumns[2] = new DataColumn("Unit");

dataTable.Columns.AddRange(dataColumns);

JToken json = JToken.Parse(jsonContent);

foreach (JProperty content in json)
{
    DataRow row = dataTable.NewRow();

    row["Property"] = content.Name;

    if (content.Value.Type == JTokenType.Object)
    {
        row["Value"] = content.Value["Value"];
        row["Unit"] = content.Value["Unit"];
    }
    else
    {
        row["Value"] = content.Value;
        row["Unit"] = "-";
    }

    dataTable.Rows.Add(row);
}

How to convert a json with nested properties to data table in c#

2 Answers2