Extracting data from a dynamic JSON array

Question

I am in the process of converting a dynamic JSON object into Excel in C#. I have tried the below steps. From the DB, I am getting the below response as a string.

[ {"ResourceName":"AH","ProjectID":1,"WeekNo":2,"02 Dec 2019":7.00,"03 Dec 2019":7.50,"04 Dec 2019":7.00,"05 Dec 2019":7.50,"06 Dec 2019":7.00},{"ResourceName":"CK","ProjectID":1,"WeekNo":2,"02 Dec 2019":6.00,"03 Dec 2019":5.00,"04 Dec 2019":3.00,"05 Dec 2019":2.00,"06 Dec 2019":5.00}, {"ResourceName":"AH","ProjectID":1,"WeekNo":3,"09 Dec 2019":6.00,"10 Dec 2019":7.50,"11 Dec 2019":7.00,"12 Dec 2019":7.50,"13 Dec 2019":8.00},{"ResourceName":"CK","ProjectID":1,"WeekNo":3,"09 Dec 2019":6.00,"10 Dec 2019":4.00,"11 Dec 2019":null,"12 Dec 2019":5.00,"13 Dec 2019":4.00},{"ResourceName":"SL","ProjectID":1,"WeekNo":3,"09 Dec 2019":null,"10 Dec 2019":1.50,"11 Dec 2019":null,"12 Dec 2019":1.50,"13 Dec 2019":0.50}, {"ResourceName":"AH","ProjectID":1,"WeekNo":4,"16 Dec 2019":7.00},{"ResourceName":"CK","ProjectID":1,"WeekNo":4,"16 Dec 2019":3.00} ]

I am required to convert the above string to a JSON and do a loop operation in order to set headers and detail values for Excel creation. I have used the below method creating a dynamic object.

ResourceName and dynamic dates should act as column headers. It is unable to create a model since the date values are dynamic.

using System.Web.Helpers;
dynamic jsonObject = Json.Decode(response);

I was able to extract the values from QuickWatch, however, the output is only generated in runtime as below.

(new System.Collections.Generic.Mscorlib_DictionaryDebugView<string, object>(((System.Web.Helpers.DynamicJsonObject)((System.Web.Helpers.DynamicJsonArray)jsonObject)._arrayValues[0])._values).Items[0]).Key

Is there a way to loop through the dynamic array contents?

you should use JSON.NET and pass it a dictionary example [here](https://stackoverflow.com/questions/34213566/create-a-strongly-typed-c-sharp-object-from-json-object-with-id-as-the-name) — styx, Dec 17 '19 at 10:57

score 0 · Answer 1 · answered Dec 17 '19 at 10:59

0

Just take the string and use the JavaScriptSerializer to deserialize it into a native object. For example, having this json:

You can now deserialize the JSON string into an array of Dynamic by doing:

JavaScriptSerializer js = new JavaScriptSerializer();
var resourceData =  js.Deserialize<Dynamic[]>(json);

answered Dec 17 '19 at 10:59

Bhaumik Shah

382
2
8
20

Thanks Bhaumik. What is the definition for `Dynamic` – Harsha W Dec 17 '19 at 11:08
The dynamic keyword is new to C# 4.0, and is used to tell the compiler that a variable's type can change or that it is not known until runtime. Think of it as being able to interact with an Object without having to cast it. – Bhaumik Shah Dec 17 '19 at 12:21

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

I recommend deserializing the JSON to a collection of dictionaries instead of using Dynamic, this method offers type security and iteration. The following example is using the Newtonsoft.Json NuGet package:

sample.json:

[
  {
    "ResourceName": "AH",
    "ProjectID": 1,
    "WeekNo": 2,
    "02 Dec 2019": 7.00,
    "03 Dec 2019": 7.50,
    "04 Dec 2019": 7.00,
    "05 Dec 2019": 7.50,
    "06 Dec 2019": 7.00
  },
  {
    "ResourceName": "CK",
    "ProjectID": 1,
    "WeekNo": 2,
    "02 Dec 2019": 6.00,
    "03 Dec 2019": 5.00,
    "04 Dec 2019": 3.00,
    "05 Dec 2019": 2.00,
    "06 Dec 2019": 5.00
  },
  {
    "ResourceName": "AH",
    "ProjectID": 1,
    "WeekNo": 3,
    "09 Dec 2019": 6.00,
    "10 Dec 2019": 7.50,
    "11 Dec 2019": 7.00,
    "12 Dec 2019": 7.50,
    "13 Dec 2019": 8.00
  },
  {
    "ResourceName": "CK",
    "ProjectID": 1,
    "WeekNo": 3,
    "09 Dec 2019": 6.00,
    "10 Dec 2019": 4.00,
    "11 Dec 2019": null,
    "12 Dec 2019": 5.00,
    "13 Dec 2019": 4.00
  },
  {
    "ResourceName": "SL",
    "ProjectID": 1,
    "WeekNo": 3,
    "09 Dec 2019": null,
    "10 Dec 2019": 1.50,
    "11 Dec 2019": null,
    "12 Dec 2019": 1.50,
    "13 Dec 2019": 0.50
  },
  {
    "ResourceName": "AH",
    "ProjectID": 1,
    "WeekNo": 4,
    "16 Dec 2019": 7.00
  },
  {
    "ResourceName": "CK",
    "ProjectID": 1,
    "WeekNo": 4,
    "16 Dec 2019": 3.00
  }
]

Code:

static void Main(string[] args)
{
    string json = File.ReadAllTextAsync("sample.json").Result;
    var deserializedJson = JsonConvert.DeserializeObject<IEnumerable<IDictionary<string, string>>>(json);

    foreach(var dictionary in deserializedJson)
    {
        Console.WriteLine(" -- Record --");

        foreach(var keyValuePair in dictionary)
        {
            Console.WriteLine($"{keyValuePair.Key}:{keyValuePair.Value}");
        }

        Console.WriteLine();
    }
}

Output:

Extracting data from a dynamic JSON array

2 Answers2