.net C#中基于动态JSON字段列表的数据分组与聚合

bprjcwpo 于 2023-01-06 发布在 .NET

关注(0)|答案(1)|浏览(141)

我有一个包含多个JSON对象的JSONArray，每个JSON对象表示一行数据（类似于SQL行）。

- JSON示例**（JSON是动态的）

[{
"col1": "a1",
"col2": "b1",
"col3": 10
}, 
{
"col1": "a1",
"col2": "b1",
"col3": 11
}, 
{
"col1": "a2",
"col2": "b2",
"col3": 5
}, 
{
"col1": "a2",
"col2": "b2",
"col3": 7
}]

一个列表，其中包含必须按其分组的列：

- 示例**：

["col1","col2","MAX(col3)"]

最后是必须应用的聚合，SUM、MIN、MAX和COUNT。还有必须应用聚合的列：
预期输出（示例1）：* * 合计为SUM**

[{
"col1": "a1",
"col2": "b2",
"col3": 21
},
{
"col1": "a2",
"col2": "b2",
"col3": 12
}]

在上面的JSON中，Col1、Col2、Col3表示不同的字段。
例如：

[
 {
  "PayrollID" : "101",
  "SSN" : "123456789",
  "BatchID" : "123",
  "PayRate" : "12",
  "Hours" : "5"
 },
 {
  "PayrollID" : "101",
  "SSN" : "123456789",
  "BatchID" : "123",
  "PayRate" : "12",
  "Hours" : "10"
 },
 {
  "PayrollID" : "101",
  "SSN" : "123456789",
  "BatchID" : "123",
  "PayRate" : "14",
  "Hours" : "12"
 }
]

if包含必须按其分组的列的列表

["PayrollID","SSN","BatchID","PayRate","SUM(Hours)"]

- 结果**

[
{
  "PayrollID" : "101",
  "SSN" : "123456789",
  "BatchID" : "123",
  "PayRate" : "12",
  "Hours" : "15"
 },
 {
  "PayrollID" : "101",
  "SSN" : "123456789",
  "BatchID" : "123",
  "PayRate" : "14",
  "Hours" : "12"
 }
]

if包含必须按其分组的列的列表
["工资ID"，"SSN"，"批次ID"，"MIN（工资率）"，"SUM（小时数）"]

- 结果**

[
{
  "PayrollID" : "101",
  "SSN" : "123456789",
  "BatchID" : "123",
  "PayRate" : "12",
  "Hours" : "27"
 }     
]

我正在寻找一个解决方案，这是类似于贾森库（JAVA）。

.net

来源：https://stackoverflow.com/questions/74986297/grouping-and-aggregating-data-based-on-list-of-dynamic-json-fields-in-c-sharp

1条答案

按热度按时间

wnavrhmk1#

你可以试试这个

JArray jArr = JArray.Parse(json);
string[] names = jArr.Select(a => ((JObject)a).Properties().Select(a => a.Name)).First().ToArray();
json = new JArray(jArr.GroupBy(s => new { col1 = (string)s[names[0]], col2 = (string)s[names[1]] })
                  .Select(g => new JObject
                  {
                      [names[0]] = g.Key.col1,
                      [names[1]] = g.Key.col2,
                      [names[2]] = g.Sum(s => (int)s[names[2]])
                  })).ToString();

Json

[
  {
    "col1": "a1",
    "col2": "b1",
    "col3": 21
  },
  {
    "col1": "a2",
    "col2": "b2",
    "col3": 12
  }
]

更新

var json2=@"[
 {
  ""PayrollID"" : ""101"",
  ""SSN"" : ""123456789"",
  ""BatchID"" : ""123"",
  ""PayRate"" : ""12"",
  ""Hours"" : ""5""
 },
 {
  ""PayrollID"" : ""101"",
  ""SSN"" : ""123456789"",
  ""BatchID"" : ""123"",
  ""PayRate"" : ""12"",
  ""Hours"" : ""10""
 },
 {
  ""PayrollID"" : ""101"",
  ""SSN"" : ""123456789"",
  ""BatchID"" : ""123"",
  ""PayRate"" : ""14"",
  ""Hours"" : ""12""
 }
]";

JArray jArr = JArray.Parse(json2);
string[] names = jArr.Select(a => ((JObject)a).Properties().Select(a => a.Name)).First().ToArray();
json = new JArray(jArr.GroupBy(s => new { col1 = s[names[0]], col2 = s[names[3]] })
                  .Select(g => new JObject
                  {
                      [names[0]] = g.Key.col1,
                      [names[3]] = g.Key.col2,
                      [names[4]] = g.Sum(s => (int)s[names[4]])
                  })).ToString();

Json

[
  {
    "PayrollID": "101",
    "PayRate": "12",
    "Hours": 15
  },
  {
    "PayrollID": "101",
    "PayRate": "14",
    "Hours": 12
  }
]

赞(0）回复(0）举报 2023-01-06

我来回答

.net C#中基于动态JSON字段列表的数据分组与聚合

1条答案

相关问题

热门标签

最新问答