将包含数组的JSON列拆分为多行 [英] split JSON column containing array to multiple rows
问题描述
关于我的主题: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
附加样本数据集。
我已经将列解析为多个列,如ID,记录类型,创建时间现在,我想将数据集数组的名称/值对解析为多行。
我的代码应该转换:
{" ID":" f41dab","记录类型":20," CREATIONTIME":" 2017-07-23T10:18:40","操作":" ViewDashboard","工作量":" PowerBI","WorkSpaceName":"My Workspace","WorkspaceId":"My
Workspace","Datasets":[{" DatasetId":" 798038e9"," DatasetName" :" 2015年,时薪负荷和曲OT;},{" datasetID所":" 247"," DatasetName来":" export_waitstat1"},{" datasetID所":" F9E"," DatasetName来":" hydro_May18"}]
TO
ATTACHED IMAGE。
|
Hi msdnpublic 1234,
感谢您发布此处。
对于您的问题,您提供的json文件和表格不匹配。我举一个简单的示例供您参考。
private void button1_Click(object sender,EventArgs e)
{
string jsonResult = @" {[{'Id':'f41dab ", '记录类型':20, 'CREATIONTIME': '2017-07-23T10:18:40', '操作': 'ViewDashboard', '工作量': 'PowerBI'},{ 'ID': 'F41', 'RecordType':10,'CreationTime':'2018-07-23T10:18:40','Operation':'ViewDashboard','Workload':'PowerBI'}}]}" ;;
DataTable dt = ConvertJsonToDatatable(jsonResult);
dataGr idView1.DataSource = dt;
}
//如何在jc.net中将json转换为datatable c#
protected DataTable ConvertJsonToDatatable(string jsonString)
{
DataTable dt = new DataTable();
//剥离坏字符
string [] jsonParts = Regex.Split(jsonString.Replace(" [","")。Replace(")","" ;),"},{");
//保留列名
List< string> dtColumns = new List< string>();
//获取列
foreach(jsonParts中的字符串jp)
{
//只循环一次获取列名
string [] propData = Regex.Split(jp.Replace(" {","")。Replace("}",""),",");
foreach(propData中的字符串rowData)
{
尝试
{
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));
}
}
休息; // TODO:可能不正确。是:退出
}
//构建dt
foreach(dtColumns中的字符串c)
{
dt.Columns.Add(c);
}
//获取表数据
foreach(jsonParts中的字符串jp)
{
string [] propData = Regex.Split(jp.Replace(" { ","")。替换("}",""),",");
DataRow nr = dt.NewRow();
foreach(propData中的字符串rowData)
{
尝试
{
int idx = rowData.IndexOf(":");
string n = rowData.Substring(0,idx - 1).Replace("'","");
string v = rowData.Substring(idx + 1).Replace("'","");
nr [n] = v;
}
catch(例外)
{
继续;
}
}
dt.Rows.Add(nr);
}
返回dt;
}
}
最好的问候,
Wendy
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.
|
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
这篇关于将包含数组的JSON列拆分为多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!