c# - 将 JSON 数据动态嵌套到数据表 C#
问题描述
{
"STATUS": "OK",
"projects": [
{
"startDate": "",
"last-changed-on": "2019-01-03T11:46:14Z",
"logo": "",
"created-on": "2018-12-12T10:04:47Z",
"privacyEnabled": false,
"status": "active",
"boardData": {},
"replyByEmailEnabled": true,
"harvest-timers-enabled": false,
"description": "",
"category": {
"color": "",
"id": "",
"name": ""
},
"id": "322852",
"overview-start-page": "default",
"start-page": "projectoverview",
"integrations": {
"xero": {
"basecurrency": "",
"countrycode": "",
"enabled": false,
"connected": "NO",
"organisation": ""
},
"sharepoint": {
"account": "",
"foldername": "root",
"enabled": false,
"folder": "root"
},
"microsoftConnectors": {
"enabled": false
},
"onedrivebusiness": {
"account": "",
"foldername": "root",
"enabled": false,
"folder": "root"
}
},
"defaults": {
"privacy": ""
},
"notifyeveryone": false,
"filesAutoNewVersion": false,
"defaultPrivacy": "open",
"tasks-start-page": "default",
"starred": false,
"announcementHTML": "",
"isProjectAdmin": true,
"name": "Project 2",
"company": {
"is-owner": "1",
"id": "78494",
"name": "MCG Company"
},
"endDate": "",
"announcement": "",
"show-announcement": false,
"subStatus": "current",
"tags": []
},
{
"startDate": "",
"last-changed-on": "2018-12-11T17:52:57Z",
"logo": "",
"created-on": "2018-11-26T11:11:00Z",
"privacyEnabled": false,
"status": "active",
"boardData": {},
"replyByEmailEnabled": true,
"harvest-timers-enabled": false,
"description": "",
"category": {
"color": "",
"id": "",
"name": ""
},
"id": "321041",
"overview-start-page": "default",
"portfolioBoards": [
{
"card": {
"id": "4771"
},
"board": {
"id": "544",
"name": "Project Implementations",
"color": "#F39C12"
},
"column": {
"id": "1573",
"name": "Go Live",
"color": "#F1C40F"
}
}
],
"start-page": "projectoverview",
"integrations": {
"xero": {
"basecurrency": "",
"countrycode": "",
"enabled": false,
"connected": "NO",
"organisation": ""
},
"sharepoint": {
"account": "",
"foldername": "root",
"enabled": false,
"folder": "root"
},
"microsoftConnectors": {
"enabled": false
},
"onedrivebusiness": {
"account": "",
"foldername": "root",
"enabled": false,
"folder": "root"
}
},
"defaults": {
"privacy": ""
},
"notifyeveryone": false,
"filesAutoNewVersion": false,
"defaultPrivacy": "open",
"tasks-start-page": "default",
"starred": false,
"announcementHTML": "",
"isProjectAdmin": true,
"name": "Project One",
"company": {
"is-owner": "1",
"id": "78494",
"name": "MCG Company"
},
"endDate": "",
"announcement": "",
"show-announcement": false,
"subStatus": "current",
"tags": []
}
]
}
这是我从应用程序获得的 JSON 响应,并且有许多其他 API 获取返回相同类型的响应(嵌套),因此当用户添加 API 调用时,这必须动态完成一个配置文件,所以我不能用gets和sets制作预制类。我的目标是将此数据转换为要插入数据库的数据表当我看到嵌套列时,我的目标是使用“_”附加父列名称 例如:category_id = “”或 integrations_xero_basecurrency = “ “, ETC..
这是我用来将数据制成表格的代码,但在代码中,如果它是 JValue(键和值),它只会占用列,而且我无法在我的一生中创建一个合适的循环来做诀窍。
public DataTable Tabulate(string jsonContent)
{
var jsonLinq = JObject.Parse(jsonContent);
// Find the first array using Linq
var srcArray = jsonLinq.Descendants().Where(d => d is JArray).First();
//Console.WriteLine("extarcted data:" + srcArray);
var trgArray = new JArray();
foreach (JObject row in srcArray.Children<JObject>())
{
var cleanRow = new JObject();
foreach (JProperty column in row.Properties())
{
// Only include JValue types
if (column.Value is JValue)
{
cleanRow.Add(column.Name, column.Value);
}
}
trgArray.Add(cleanRow);
}
DataTable dt = JsonConvert.DeserializeObject<DataTable>(trgArray.ToString());
return dt;
}
解决方案
像这样的东西怎么样:
public DataTable Tabulate(string jsonContent)
{
var jsonLinq = JObject.Parse(jsonContent);
// Find the first array using Linq
var arrayProp = jsonLinq.Properties().First(p => p.Value is JArray);
var srcArray = (JArray)arrayProp.Value;
// Set up a regex consisting of the array property name and subscript
// (e.g. "projects[0]."), which we will strip off
var regex = new Regex($@"^{arrayProp.Name}\[\d+\]\.");
// Flatten each object of the original array
// into new objects and put them in a new array
var trgArray = new JArray(
srcArray.Children<JObject>()
.Select(row => new JObject(
row.Descendants()
.OfType<JProperty>()
.Where(p => p.Value is JValue)
.Select(p => new JProperty(
regex.Replace(p.Value.Path, "").Replace(".", "_"),
p.Value
))
))
);
// Convert the new array to a DataTable
DataTable dt = trgArray.ToObject<DataTable>();
return dt;
}
推荐阅读
- javascript - 如何使用 Javascript 中的循环从 Object 元素中减去
- javascript - 如何选择所有 javascripts 对象?
- android - 如何通过类获取资源?
- reactjs - 把你的 reactjs APP 分成多个“页面”有什么作用?
- docker - 容器使用network_mode时如何设置静态ip:服务:
- c# - 当服务更新模型时,UI 不会通过数据绑定更新
- javascript - ExtJS 从任何隐藏/显示逻辑中排除网格中的列
- function - 应用由关键字确定的其他功能的函数或宏
- javascript - 反应:类型 'boolean' 不可分配给类型 'filterOpenStateI | 派遣
>' - keras - keras.layers.MultiHeadAttention 支持并行吗?如果是这样,你如何使用它?