using System;

using System.Data;

using System.Collections.Generic;

using System.Linq;

using Newtonsoft.Json;

using Newtonsoft.Json.Linq;

public class Program

{

    public static void Main()

    {

        #region Initialize Input DataTable

        DataTable inputDt = new DataTable();

        inputDt.Columns.AddRange(new DataColumn[]

                                 {

                                     new DataColumn("Col1"),

                                     new DataColumn("Col2"),

                                     new DataColumn("Col3"),

                                     new DataColumn("Col4")

                                 });

        DataRow inputDtRow = inputDt.NewRow();

        inputDtRow["Col1"] = "1";

        inputDtRow["Col2"] = "Name 1";

        inputDtRow["Col3"] = @"[{""JsonCol1"": ""JsonCol1 Value 1"", ""JsonCol2"": ""JsonCol2 Value 1"", ""JsonCol3"": ""JsonCol3 Value 1""}, 

            {""JsonCol1"": ""JsonCol1 Value 2"", ""JsonCol2"": ""JsonCol2 Value 2"", ""JsonCol3"": ""JsonCol3 Value 2""}]";

        inputDtRow["Col4"] = @"[{""JsonCol1"": ""JsonCol1 Value 1"", ""JsonCol2"": ""JsonCol2 Value 1""}, 

            {""JsonCol1"": ""JsonCol1 Value 2"", ""JsonCol2"": ""JsonCol2 Value 2""},

            {""JsonCol1"": ""JsonCol1 Value 3"", ""JsonCol2"": ""JsonCol2 Value 3""},

            {""JsonCol1"": ""JsonCol1 Value 3"", ""JsonCol2"": ""JsonCol2 Value 4""}]";

        inputDt.Rows.Add(inputDtRow);

        DataRow inputDtRow2 = inputDt.NewRow();

        inputDtRow2["Col1"] = "2";

        inputDtRow2["Col2"] = "Name 2";

        inputDtRow2["Col3"] = @"[{""JsonCol1"": ""JsonCol1 Value 1"", ""JsonCol2"": ""JsonCol2 Value 1"", ""JsonCol3"": ""JsonCol3 Value 1""}]";

        inputDtRow2["Col4"] = @"[{""JsonCol1"": ""JsonCol1 Value 1"", ""JsonCol2"": ""JsonCol2 Value 1""}]";

        inputDt.Rows.Add(inputDtRow2);

        #endregion Initialize Input DataTable

        string[] jsonColumnNames = new string[] { "Col3", "Col4" };

        string[] toRemoveColumnNames = new string[] { "Col3", "Col4" };

        DataTable resultDt = AddJsonRowsToDataTable(inputDt, jsonColumnNames, toRemoveColumnNames); 

        FiddleHelper.WriteTable(resultDt);

    }

    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;

    }

}