split JSON column containing array to multiple rows

Question

_{Thursday, January 11, 2018 3:45 PM}

In relation to my thread:https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2bdcbe98-8d0f-425d-9dd8-dbd20e005643/how-to-parse-a-json-column-into-multiple-columns-in-ssis?forum=sqlintegrationservices

Sample data set attached.

I am already parsing the column into multiple columns like ID,Record Type,Creation Time etc.Now,I want to also parser Datasets array of name/value pairs into multiple rows.

My code should convert :

{"Id":"f41dab","RecordType":20,"CreationTime":"2017-07-23T10:18:40","Operation":"ViewDashboard","Workload":"PowerBI","WorkSpaceName":"My Workspace","WorkspaceId":"My Workspace","Datasets":[{"DatasetId":"798038e9","DatasetName":"2015-hourly-loads"},{"DatasetId":"247","DatasetName":"export_waitstat1"},{"DatasetId":"f9e","DatasetName":"hydro_May18"}]

TO

ATTACHED IMAGE.

All replies (2)

_{Monday, January 15, 2018 6:42 AM}

Hi msdnpublic 1234,

Thank you for posting here.

For your question, the json file and the table you provided does not match. I make a simple example for your reference.

   private void button1_Click(object sender, EventArgs e)
        {
            string jsonResult = @"{[{'Id':'f41dab','RecordType':20,'CreationTime':'2017-07-23T10:18:40','Operation':'ViewDashboard','Workload':'PowerBI'},{'Id':'f41','RecordType':10,'CreationTime':'2018-07-23T10:18:40','Operation':'ViewDashboard','Workload':'PowerBI'}}]}";

            DataTable dt = ConvertJsonToDatatable(jsonResult);

            dataGridView1.DataSource = dt;

        }
      
        // how to convert json to datatable in asp.net c#
        protected DataTable ConvertJsonToDatatable(string jsonString)
        {
            DataTable dt = new DataTable();
            //strip out bad characters
            string[] jsonParts = Regex.Split(jsonString.Replace("[", "").Replace("]", ""), "},{");

            //hold column names
            List<string> dtColumns = new List<string>();

            //get columns
            foreach (string jp in jsonParts)
            {
                //only loop thru once to get column names
                string[] propData = Regex.Split(jp.Replace("{", "").Replace("}", ""), ",");
                foreach (string rowData in propData)
                {
                    try
                    {
                        int idx = rowData.IndexOf(":");
                        string n = rowData.Substring(0, idx - 1);
                        string v = rowData.Substring(idx + 1);
                        if (!dtColumns.Contains(n))
                        {
                            dtColumns.Add(n.Replace("'", ""));//'
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(string.Format("Error Parsing Column Name : {0}", rowData));
                    }

                }
                break; // TODO: might not be correct. Was : Exit For
            }

            //build dt
            foreach (string c in dtColumns)
            {
                dt.Columns.Add(c);
            }
            //get table data
            foreach (string jp in jsonParts)
            {
                string[] propData = Regex.Split(jp.Replace("{", "").Replace("}", ""), ",");
                DataRow nr = dt.NewRow();
                foreach (string rowData in propData)
                {
                    try
                    {
                        int idx = rowData.IndexOf(":");
                        string n = rowData.Substring(0, idx - 1).Replace("'", "");
                        string v = rowData.Substring(idx + 1).Replace("'", "");
                        nr[n] = v;
                    }
                    catch (Exception)
                    {
                        continue;
                    }

                }
                dt.Rows.Add(nr);
            }
            return dt;
        }

    }

Best Regards,

Wendy

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [email protected].

_{Wednesday, January 17, 2018 1:34 PM}

Thanks Wendy,i have already achieved the results depicted in your image.I am looking to split the datasets array into multiple rows