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.

CodePudding user response：

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>();

CodePudding user response：

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);
}