C#-压扁嵌套Json

Question

我有多个具有不同布局的JSON，我正试图创建一些代码来压平这些JSON，然后将其转换为Datatable。

示例JSON 1

{
"d": {
"results": [
{
"__metadata": {
"uri": "myuri.com",
"type": "String"
},
"jobNumber": "123456789",
"numberVacancy": "1",
"some_obj": {
"__metadata": {
"uri": "myuri.com",
"type": "String"
},
"code": "000012356"
},
"anothernested": {
"results": [
{
"__metadata": {
"uri": "myuri.com",
"type": "String"
},
"picklistLabels": {
"results": [
{
"__metadata": {
"uri": "myuri.com",
"type": "String"
},
"label": "Casual"
},
{
"__metadata": {
"uri": "myuri.com",
"type": "String"
},
"label": "Casual"
}
]
}
}
]
}
},
{
"__metadata": {
"uri": "myuri.com",
"type": "String"
},
"jobNumber": "987654321",
"numberVacancy": "1",
"some_obj": {
"__metadata": {
"uri": "myuri.com",
"type": "String"
},
"code": "000012356"
},
"anothernested": {
"results": [
{
"__metadata": {
"uri": "myuri.com",
"type": "String"
},
"picklistLabels": {
"results": [
{
"__metadata": {
"uri": "myuri.com",
"type": "String"
},
"label": "Casual"
},
{
"__metadata": {
"uri": "myuri.com",
"type": "String"
},
"label": "Casual"
}
]
}
}
]
}
}
]
}
}

我希望如何将JSON扁平化为Datatable的示例。

空缺>some_obj/_metadata/uri>some_obk/code其他嵌套的/results/0/_metadata/uri其他嵌套的/results/0/__metadata/type/__metadata/uri其他其他嵌套的/results/0-picklistLabels/results/1/__metadata/uri<12346789>>myuri.com字符串>9876543211myuri.com字符串字符串

__metadata/uri	__metadata/type	作业编号	some_obj/__metadata/type	其他嵌套的/results/0/picklistLabels/results/0/__metadata/type	其他嵌套的/results/0/picklistLabels/results:0/label	另一个嵌套的/resources/0/picklistLabels/1/__data/type
myuri.com	字符串	1		字符串	212356		myuri.com
myuri.com	字符串			myuri.com	字符串			myuri.com	临时

要从源json创建数据表，您需要以下代码：

JObject jsonObject = JObject.Parse(json);
List<string> jpaths = jsonObject.Descendants().OfType<JProperty>().Where(jp => jp.Value is JArray).Select(jp => jp.Path).ToList();
List<JToken> rowtokens = jsonObject.SelectTokens("$.d.results.[*]").ToList();
DataTable resultTable = new DataTable();
resultTable.Columns.AddRange(((JObject)rowtokens[0]).Descendants().OfType<JProperty>().Where(jp => jp.Value is JValue).Select(jp => new DataColumn(jp.Path)).ToArray());
foreach (JToken rowtoken in rowtokens)
{
resultTable.Rows.Add(((JObject)rowtoken).Descendants().OfType<JProperty>().Where(jp => jp.Value is JValue).Select(jp => jp.Value.ToString()).ToArray());
}

您可以尝试Cinchoo ETL-一个满足您需求的开源库。

using (var r = new ChoJSONReader("*** YOUR JSON FILE PATH ***")
.WithJSONPath("$..d.results")
.Configure(c => c.NestedColumnSeparator = '/')
)
{
var dt = r.AsDataTable();
Console.WriteLine(dt.Dump());
}

输出：

__metadata/uri,__metadata/type,jobNumber,numberVacancy,some_obj/__metadata/uri,some_obj/__metadata/type,some_obj/code,anothernested/results/0/__metadata/uri,anothernested/results/0/__metadata/type,anothernested/results/0/picklistLabels/results/0/__metadata/uri,anothernested/results/0/picklistLabels/results/0/__metadata/type,anothernested/results/0/picklistLabels/results/0/label,anothernested/results/0/picklistLabels/results/1/__metadata/uri,anothernested/results/0/picklistLabels/results/1/__metadata/type,anothernested/results/0/picklistLabels/results/1/label
myuri.com,String,123456789,1,myuri.com,String,000012356,myuri.com,String,myuri.com,String,Casual,myuri.com,String,Casual
myuri.com,String,987654321,1,myuri.com,String,000012356,myuri.com,String,myuri.com,String,Casual,myuri.com,String,Casual

样品小提琴：https://dotnetfiddle.net/DXBOYC

免责声明：我是这个图书馆的作者。