Home > Enterprise >  How can convert json to dataset with data coming in this way?
How can convert json to dataset with data coming in this way?

Time:07-05

I have a json data like this coming through the API.

{
    "getDataJSON": {
        "message": "",
        "tables": [
            {
                "rows": [
                    {
                        "cols": [
                            {
                                "colName": "columnName1",
                                "colValue": "columnValue"
                            },
                            {
                                "colName": "columnName2",
                                "colValue": "columnValue"
                            }
                        ]
                    },
                    {
                        "cols": [
                            {
                                "colName": "columnName1",
                                "colValue": "columnValue"
                            },
                            {
                                "colName": "columnName2",
                                "colValue": "columnValue"
                            }
                        ]
                    }
                ]
            }
        ]
    }
}

I deserialize this data. But using nested loops. How can i deserialize more effectively? Any ideas?

I deserialize like this;

            var jsonData = JsonConvert.DeserializeObject<Root>(response.Content);
            foreach (Row item in jsonData.GetDataJSON.tables[0].rows)
            {
                Model nodeModel = new Model();
                foreach (Col itemCol in item.cols)
                {
                    if (itemCol.colName == "columnName" && !string.IsNullOrEmpty(itemCol.colValue))
                        nodeModel.columnName = itemCol.colValue;
                    else if (itemCol.colName == "columnName" && !string.IsNullOrEmpty(itemCol.colValue))
                        nodeModel.columnName = itemCol.colValue;
                }
                returnModel.Add(nodeModel);
            }

thank you everyone.

CodePudding user response:

Try this

var tables = (JArray)JObject.Parse(json)["getDataJSON"]["tables"];
DataSet ds = new DataSet();

for (var i = 0; i < tables.Count; i  )
{
    var rows = (JArray)tables[i]["rows"];
    DataTable dt = JArray.FromObject(rows.Select(r => CreateJobject((JArray)r["cols"])))
   .ToObject<DataTable>();
    
        ds.Tables.Add(dt);
}


public JObject CreateJObject(JArray jArr)
{
    var jObj = new JObject();
    foreach (var c in jArr)
        jObj.Add((string)c["colName"], c["colValue"]);
    return jObj;
}

CodePudding user response:

I would use linq:

using Newtonsoft.Json.Linq;

// Assuming Model
class Model {
    public string ColName1 {get;set;}
    public string ColName2 {get;set;}
}

// Use som Json Linq
var jsonData = JObject.Parse(someJson);
var rows = from row in jsonData["getDataJSON"]["tables"][0]["rows"]
            let cols = (from col in row["cols"] 
                        select new 
                        { 
                            Name = (string)col["colName"], 
                            Value = (string)col["colValue"] 
                        })
                        .ToDictionary(k=>k.Name, v=>v.Value)
            select new Model() {
                ColName1 = cols["columnName1"],
                ColName2 = cols["columnName2"]
            };
            
return rows;

There are some assumptions here. I assumed that there will always be one single table and indexed it using [0] - also I assumed that the Column Names will always exist on the Model.

Basically I just converted the array of columns in each row into a Dictionary and then created a new object for each record and set the value of the field using that dictionary.

CodePudding user response:

Not enough points on this site to comment. Hence posting as answer.

Is there any influence you have over API response schema? This is extremely error prone way to integrate.

Update: I am not well versed with parallel programming so please definitely improve this code. There is also another issue of traversing multiple times. Depending on how performance sensitive the code is, this would also need improvement.

Here is a potential alternative:

The object model.

    public class Parent
    {
        public GetDataJSON getDataJSON { get; set; }

    }

    public class GetDataJSON
    {
        public string message { get; set; }

        public List<Tables> tables { get; set; }
    }

    public class Tables
    {
        public List<Rows> rows { get; set; }
    }

    public class Rows
    {
        public List<Cols> Cols { get; set; }
    }

    public class Cols
    {
        public string colName { get; set; }
        public string colValue { get; set; }
    }

Calling method:

        var entireJSON = JsonConvert.DeserializeObject<Parent>(json);
        
        DataSet dataset = new DataSet();

        Parallel.ForEach(entireJSON.getDataJSON.tables, x => { dataset.Tables.Add(PopulateTable(x)); });

Worker method:

    public static DataTable PopulateTable(Tables table)
    {
        DataTable returnTable = new DataTable();

        // Traverse JSON to get all column names
        var columnNames = table.rows.SelectMany(x => x.Cols.Select(y => y.colName)).Distinct();

        // Populate table with these columns
        foreach (var columnName in columnNames)
        {
            returnTable.Columns.Add(columnName);
        }

        // Travsers JSON again for values
        table.rows.ForEach(x =>
        {
            var newRow = returnTable.NewRow();

            x.Cols.ForEach(y =>
            {
                newRow[y.colName] = y.colValue;
            });

            returnTable.Rows.Add(newRow);
        });

        return returnTable;
    }
  • Related