Linq in JSON.NET

Question

I have a json string as below

{
    'Sheet1': [{
        'EmployeeNo': '123456',
        'EmployeeName': 'Midhun Mathew'
    }, {
        'EmployeeNo': '123457',
        'EmployeeName': 'Bill Gates'
    }, {
        'EmployeeNo': '123456',
        'Address': 'AAAA'
    }, {
        'EmployeeNo': '123457',
        'Address': 'BBBB'
    }]
}
JObject parsedJson = JObject.Parse(jsonString);

// Get the EmployeeNo's 

List<JToken> tokenList = parsedJson["Sheet1"]
                           .GroupBy(d => d["EmployeeNo"])
                           .Select(s => s.Key)
                           .ToList();

// Get the tokens which have the Same 'EmployeeNo'

foreach (JToken j in tokenList)
{
    IEnumerable<JToken> t = parsedJson["Sheet1"].Where(s => s["EmployeeNo"] == j);
}

But in the foreach I am getting only the first one which is

{
  "EmployeeNo": "123456",
  "EmployeeName": "Midhun Mathew"
}

I am not getting what I am doing wrong here.

My original intent is to group the JTokens having same EmployeeNo into one JObject

So in the above case I will get 2 JObjects since there are 2 different EmployeeNo's

Hope I am clear

Answer 1

It's not entirely clear what you're trying to do here, but you seem to be grouping by EmployeeNo, then throwing away the results to get just the keys, and then trying to do the grouping again in a more manual way.

If you remove the Select(s => s.Key) part, then you could just use the groupings directly:

var tokensByEmployeeNo = parsedJson["Sheet1"].GroupBy(x => x["EmployeeNo"]);

foreach (var grouping in tokensByEmployeeNo)
{
    Console.WriteLine($"Key: {grouping.Key}");

    foreach (var token in grouping)
    {
        Console.WriteLine(token);
    }            
}

If you want to create a 'new' JObject for each of these groupings, then you probably want to create a JArray per group:

var groupedArrays = parsedJson["Sheet1"]
    .GroupBy(x => x["EmployeeNo"])
    .Select(groupedRecords => new JArray(groupedRecords));

The first of these would look like this, for example:

[
  {
    "EmployeeNo": "123456",
    "EmployeeName": "Midhun Mathew"
  },
  {
    "EmployeeNo": "123456",
    "Address": "AAAA"
  }
]

Answer 2

If you just wanted to go through the list of Employees, here's how I am used doing:

   var json = @"
                        {
                'Sheet1': [{
                    'EmployeeNo': '123456',
                    'EmployeeName': 'Midhun Mathew'
                }, {
                    'EmployeeNo': '123457',
                    'EmployeeName': 'Bill Gates'
                }, {
                    'EmployeeNo': '123456',
                    'Address': 'AAAA'
                }, {
                    'EmployeeNo': '123457',
                    'Address': 'BBBB'
                }]
            }";

            var array = JArray.Parse(JObject.Parse(json).SelectToken("Sheet1").ToString());

            foreach (var arrayElement in array)
            {
                var employeeNo = JObject.Parse(arrayElement.ToString()).SelectToken("EmployeeNo");
                var employeeName = JObject.Parse(arrayElement.ToString()).SelectToken("EmployeeName");

                Console.WriteLine("Number: {0}, Name: {1}", employeeNo, employeeName);
            }

Output: