Grouping and Aggregating data based on List of dynamic JSON Fields in C#

Question

I have a JSONArray which contains multiple JSONObjects and each JSON object represents a row of data. (Like a SQL row)

JSON Example(JSON is dynamic)

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

A list containing the columns on which group by has to happen:

Example:

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

Finally, the aggregate that has to be applied, SUM,MIN,MAX and COUNT. Also the column on which the aggregate has to be applied:

Expected Output (Example 1): WITH aggregate being SUM

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

In the above JSON Col1,Col2,Col3 represents the different fields.

For Eg :

[
 {
  "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"
 }
]

1. if List containing the Columns on which Group by has to happen

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

Result

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

2. if List containing the Columns on which Group by has to happen

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

Result

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

I am looking for a solution which is similar to the jasson library(JAVA).

Answer 1

you can try this

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
  }
]

UPDATE

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
  }
]