I retrieve data from an Excel file located on sharepoint.com using Microsoft.Graph like this:

        var workbookRange = await graphServiceClient

This returns a WorkbookRange. I access its Values property which returns a System.Text.Json.JsonDocument which contains the data from a range in a specific worksheet of the Excel file. If I use the GetRawText method, this is how the data looks that is retrieved:


Each combination is an email and a score. What I need to do is check if a particular email address is in the dataset, and what its score is.

So this is how I parse the JsonDocument:

  • First I get all the "rows":

    var lines = workbookRange.Values.RootElement.EnumerateArray().ToList();
  • Then I search for a row where the email address corresponds to the one I need to check, and retrieve its score:

          int? score = null;
          foreach (var emailAddressPlusScore in lines
              .Select(line => line.EnumerateArray().ToList())
              .Select(emailAddressPlusScore => new { emailAddressPlusScore, emailAddress = emailAddressPlusScore[0].GetString() })
              .Where(x => !string.IsNullOrEmpty(x.emailAddress) && x.emailAddress.Equals(employee.Email, StringComparison.OrdinalIgnoreCase))
              .Select(x => x.emailAddressPlusScore))
              score = emailAddressPlusScore[1].GetInt32();

    Which is a reworking of this logic:

              foreach (var line in lines)
                  var emailAddressPlusScore = line.EnumerateArray().ToList();
                  var emailAddress = emailAddressPlusScore[0].GetString();
                  if (!string.IsNullOrEmpty(emailAddress))
                      score = emailAddressPlusScore[1].GetInt32();

However, this feels inelegant, and I get the feeling I'm not parsing the contents of the JsonDocument in the proper way. Am I missing something? Is there perhaps a better, more efficient way, particularly considering that I need to call EnumerateArray() for each line?

  • \$\begingroup\$ Could you please share with us a sample input and an expected output? \$\endgroup\$ Dec 6 '21 at 13:00
  • 1
    \$\begingroup\$ @PeterCsala Input example is in the body, output is an ìnt?. \$\endgroup\$
    – BCdotWEB
    Dec 6 '21 at 13:09
  • \$\begingroup\$ Are the e-mail addresses unique? So could you load the retrieved data into a Dictionary<string, int>? \$\endgroup\$ Dec 6 '21 at 13:49
  • \$\begingroup\$ Yes, email addresses are unique. And all entries have a score. \$\endgroup\$
    – BCdotWEB
    Dec 6 '21 at 14:36
  • 1
    \$\begingroup\$ @PeterCsala My problem is handling the JsonDocument. This represents data in an IMHO completely ridiculous manner (IMHO), and I don't know how to handle it properly. \$\endgroup\$
    – BCdotWEB
    Dec 6 '21 at 14:39

My first suggestion is to separate your data parsing logic from your querying logic.

If you can load the retrieved data into a Dictionary<string, int> then the query part would look this:

string email = "...@...";
int? relatedScore = emailScoreMapping.TryGetValue(email, out var score) ? score : null;

My second suggestion is to do the parsing in an imperative way (with foreach and enumerators) than declarative (with LINQ). My reasoning is that with an imperative approach the error handling is a bit more convenient IMHO.

if (responseData.RootElement.ValueKind != JsonValueKind.Array)
    throw new FormatException("Excepted response is a json array");

Dictionary<string, int> emailScoreMapping = new ();
foreach (var innerDocument in responseData.RootElement.EnumerateArray())
    if (innerDocument.ValueKind != JsonValueKind.Array)
        continue; //or throw expception depending on your requirements
    var innerArray = innerDocument.EnumerateArray();

    var emailField = innerArray.Current;
    string emailValue = emailField.ValueKind == JsonValueKind.String
            ? emailField.GetString() : null;

    var scoreField = innerArray.Current;
    int? scoreValue = scoreField.ValueKind == JsonValueKind.Number
        ? scoreField.GetInt32() : null;

    if (emailValue is null || scoreValue is null)
        throw new FormatException("Expected inner array is a string, int pair");

    emailScoreMapping.Add(emailValue, scoreValue.Value);
  1. Check whether or not the received json is an array
    1.1) If not then throw exception to stop further processing
  2. Get an enumerator for the outer array and iterate through it
  3. Check whether or not the iterated element is an array
    3.1) If not then skip it or throw an exception to stop further processing
  4. Get an enumerator for the inner array
  5. Move to the first element
  6. Retrieve data if it is a string otherwise treat it as null
  7. Move to the second element
  8. Retrieve data if it is a number otherwise treat it as null
  9. If any of them is not present then throw exception to stop further processing
  10. Otherwise put data into the mapping lookup

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.