Getting json in this format after serializing the data from data table

This is the result after reading the excel file and storing the data in a datatable. Later serialized using newtosoft.json into below json output

Json getting now below:

[{ "Id": "1", "Profit": "33.332999999999998", "Telephone": "123", "Email": "[email protected]" }, { "Id": "1", "Profit": "21.21", "Telephone": "43", "Email": "[email protected]" }, { "Id": "2", "Profit": "49.000999999999998", "Telephone": "22", "Email": "[email protected]" }, { "Id": "2", "Profit": "10.1", "Telephone": "876", "Email": "[email protected]" }]

Expected format

[{ "Id": "1", "Profits": ["33.332999999999998", "21.21"], "Telephones": ["43", "123"], "Emails": ["[email protected]", "[email protected]"] }, { "Id": "2", "Profits": ["49.000999999999998", "10.1"], "Telephones": ["876", "22"], "Emails": ["[email protected]", "[email protected]"] }]

Can anyone please help on this

CodePudding user response：

try this

var json=...origin json
var jD = JsonConvert.DeserializeObject<DataOrigin[]>(json);
jD=jD.OrderBy(d => d.Id).ToArray();
    var prevId=string.Empty;
    var list=new List<Data>();
    foreach (var item in jD)
    {
                
        if(item.Id!=prevId)
        {
            prevId=item.Id;
            list.Add(new Data(Convert.ToInt32(item.Id), item.Profit, item.Telephone, item.Email));
        }
        else
        {
            var prevIdInt=Convert.ToInt32(prevId);
            var prevItem=list.Last(l =>l.Id==prevIdInt );
            prevItem.MergeItem(item.Profit,item.Telephone,item.Email);
        }
    }

    var result =  JsonConvert.SerializeObject(list);
    

result

[
  {
    "Id": 1,
    "Profits": [
      33.333,
      21.21
    ],
    "Telephones": [
      "123",
      "43"
    ],
    "Emails": [
      "[email protected]",
      "[email protected]"
    ]
  },
  {
    "Id": 2,
    "Profits": [
      49.001,
      10.1
    ],
    "Telephones": [
      "22",
      "876"
    ],
    "Emails": [
      "[email protected]",
      "[email protected]"
    ]
  }
]

classes

public class DataOrigin
    {
        public string Id { get; set; }
        public double Profit { get; set; }
        public string Telephone { get; set; }
        public string Email { get; set; }
}
public class Data
{
    public int Id { get; set; }
    public  List<double> Profits { get; set; }
    public List<string> Telephones { get; set; }
    public List<string> Emails { get; set; }
    
    public Data(int id,double profit,string phone, string email)
    {  
         Id=id;
        Profits = new List<double>(){profit};
        Telephones = new List<string>(){phone};
        Emails = new List<string>(){email};
        
    }
    public void MergeItem (double profit,string phone, string email)
    {
        Profits.Add(profit);
        Telephones.Add(phone);
        Emails.Add(email);
    }
}

CodePudding user response：

Here is the solution:

    var data = [{
        "Id": "1",
        "Profit": "33.332999999999998",
        "Telephone": "123",
        "Email": "[email protected]"
    }, {
        "Id": "1",
        "Profit": "21.21",
        "Telephone": "43",
        "Email": "[email protected]"
    }, {
        "Id": "2",
        "Profit": "49.000999999999998",
        "Telephone": "22",
        "Email": "[email protected]"
    }, {
        "Id": "2",
        "Profit": "10.1",
        "Telephone": "876",
        "Email": "[email protected]"
    }]
    
    var temp = {};
    data.forEach(x => {
        if (temp[x.Id] == undefined) {
            temp[x.Id] = {}
            temp[x.Id]['Id'] = x.Id
            temp[x.Id]['Profit'] = []
            temp[x.Id]['Telephone'] = []
            temp[x.Id]['Email'] = []
        }
    
        temp[x.Id].Profit.push(x.Profit)
        temp[x.Id].Telephone.push(x.Telephone)
        temp[x.Id].Email.push(x.Email)
    
    })
    
    var finalResponse = []
    for (const [key, value] of Object.entries(temp)) {
        finalResponse.push(value)
    }