Deserialize JSON and adding back into DataTable with main records

Question

I have a DataTable and it has some JSON data, I want to deserialize the JSON, and add it to the primary record. JSON columns can be none or many.

If the column has JSON data, I am fetching and adding it as the columns. It is working fine.

dt = (DataTable)JsonConvert.DeserializeObject(jsonType2Column, (typeof(DataTable)));
for (int i = 0; i < dt.Columns.Count; i++)
{
    dtable.Columns.Add(dt.Columns[i].ColumnName, typeof(string));
}

I am struggling with how to add data into the table, where JSON records with no. of rows are not the same.

This code works fine while I have only a JSON column.

for (int i = 0; i < dtJson2.Rows.Count; i++)
{
    for (int k = 0; k < dtJson2.Columns.Count; k++)
    {
        try
        {
            string ColumnName = dtJson2.Columns[k].ColumnName.ToString();
            dtrow[ColumnName] = dtJson2.Rows[i][ColumnName].ToString();                                       
        }
        catch (Exception ex)
        {
            continue;
        }
    }
    dtable.Rows.Add(dtrow);
}

Answer 1

My solution may not be a great one but should be able to give you the direction to handle and extract the JSON rows to the respective DataTable column.

From your question, I am concerned that it is possible that the JSON properties that have the same name appeared in different columns. The DataTable column name should not be redundant, else may result in behavior like the value being overwritten. In that case, you should name the column for example: "<Original column name>_<JSON property name>".

Skip the part to extract the property name from the JSON array and add it to the DataTable column which you have completed.

1. Iterate each data row and column and extract the JSON. Also, rename each property name to remark it is from which (original) column.

2. Determine the maximum number of rows to be inserted based on the maximum number of rows from each JSON array.

3. Combine each JSON object from the different columns row by row and add it as a DataRow into the DataTable.

using System.Collections.Generic;
using System.Linq;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

public static DataTable AddJsonRowsToDataTable(DataTable dt, string[] jsonColumns, string[] toRemoveColumns)
{
    string jsonColumnNamePrefix = "{0}_{1}";
    DataTable resultDt = dt.Clone();

    foreach (string toRemoveColumn in toRemoveColumns)
        resultDt.Columns.Remove(toRemoveColumn);

    if (dt.Rows.Count == 0)
        return dt;

    // Add column for columns in JSON row
    foreach (string jsonColumn in jsonColumns)
    {
        if (dt.Columns.IndexOf(jsonColumn) == -1)
            continue;

        string jsonTypeColumn = dt.Rows[0][jsonColumn].ToString();
        if (String.IsNullOrEmpty(jsonTypeColumn))
            continue;

        DataTable jsonColDt = (DataTable)JsonConvert.DeserializeObject(jsonTypeColumn, (typeof(DataTable)));
        for (int i = 0; i < jsonColDt.Columns.Count; i++)
        {
            resultDt.Columns.Add(String.Format(jsonColumnNamePrefix, jsonColumn, jsonColDt.Columns[i].ColumnName), typeof(string));
        }
    }

    DataColumnCollection dataCols = dt.Columns;
    foreach (DataRow row in dt.Rows)
    {
        DataRow newRow = resultDt.NewRow();

        List<List<JObject>> jsonArrays = new List<List<JObject>>();
        foreach (DataColumn col in dataCols)
        {
            if (jsonColumns.Contains(col.ColumnName))
            {
                List<JObject> jObjects = new List<JObject>();

                string jsonTypeColumn = row[col].ToString();                    
                JArray jArray = JArray.Parse(jsonTypeColumn);

                // Extract JSON array, rename each property with prefix to know it is from which (original column)
                foreach (JObject jObj in jArray)
                {
                    JObject newJObj = new JObject();

                    foreach (var kvp in jObj)
                    {
                        newJObj.Add(String.Format(jsonColumnNamePrefix, col.ColumnName, kvp.Key), kvp.Value.ToString());
                    }

                    jObjects.Add(newJObj);
                }

                jsonArrays.Add(jObjects);
            }
            else
            {
                newRow[col.ColumnName] = row[col];
            }
        }

        int maxJsonRowsToAdd = jsonArrays.Select(x => x.Count).Max();
        if (maxJsonRowsToAdd > 0)
        {
            // Add number of rows based on max number of rows from JSON column
            for (int i = 0; i < maxJsonRowsToAdd; i++)
            {
                // Merge multiple JObjects from multiple JSON array by row
                JObject jObject = new JObject();
                foreach (var jsonArray in jsonArrays)
                {
                    if (jsonArray.ElementAtOrDefault(i) != null)
                    {
                        jObject.Merge(jsonArray.ElementAtOrDefault(i));
                    }
                }

                // Insert new data row for each merged JObject 
                DataRow jsonDtRow = resultDt.NewRow();
                jsonDtRow.ItemArray = newRow.ItemArray;
                foreach (var kvp in jObject)
                {
                    jsonDtRow[kvp.Key] = kvp.Value.ToString();
                }

                resultDt.Rows.Add(jsonDtRow);
            }
        }
        else
        {
            // Add default row if no rows in JSON column(s)
            resultDt.Rows.Add(newRow);
        }
    }

    return resultDt;
}

Caller:

string[] jsonColumnNames = new string[] { "Col3", "Col4" };
string[] toRemoveColumnNames = new string[] { "Col3", "Col4" };
DataTable resultDt = AddJsonRowsToDataTable(inputDt, jsonColumnNames, toRemoveColumnNames);