Converting a DataTable to nested JSON output

Question

I have a SQL Server source table defined as:

 sku    store  qty
 20000  100    3
 20000  132    1
 20000  320    0
 30000  243    2
 30000  210    1
 10000  410    5

I need the output to be:

{
  "skus": {
    "20000": {
      "100": 3,
      "132": 1,
      "320": 0
    },
    "30000": {
      "243": "2",
      "410": "1"
    },
    "10000": {
      "410": "5"
    }
  }
}

I have the source SQL Server table being imported to a DataSet, then was going to use JSON.NET to parse the results. I was thinking I should create some sort of Class structure with a sku having a list of store/qty key/value pairs, but I'm not totally sure if that's the right track or not.

Have you tried anything to get the desired output?

Ilija Dimov
– Ilija Dimov

2014-11-05 10:12:11 +00:00
Commented Nov 5, 2014 at 10:12 — Ilija Dimov
– Ilija Dimov, Commented Nov 5, 2014 at 10:12

Brian Rogers · Accepted Answer · 2014-11-05 17:16:37Z

You're on the right track. To get the structure you outlined in your question, you need to use a dictionary of dictionaries representing mappings of store number to quantity per SKU. The class would look like this:

class RootObject
{
    [JsonProperty("skus")]
    public Dictionary<string, Dictionary<string, int>> Skus { get; set; }
}

You will need a small amount of code to group your data table rows into the nested dictionaries, as shown below. Note: this code assumes that each store number will be encountered only once per SKU. If this is not the case, you will need to adjust it accordingly.

DataTable table = new DataTable();
table.Columns.Add("sku", typeof(int));
table.Columns.Add("store", typeof(int));
table.Columns.Add("qty", typeof(int));
table.Rows.Add(20000, 100, 3);
table.Rows.Add(20000, 132, 1);
table.Rows.Add(20000, 320, 0);
table.Rows.Add(30000, 243, 2);
table.Rows.Add(30000, 210, 1);
table.Rows.Add(10000, 410, 5);

var skus = new Dictionary<string, Dictionary<string, int>>();

foreach (DataRow row in table.Rows)
{
    string sku = row["sku"].ToString();
    Dictionary<string, int> stores;
    if (!skus.TryGetValue(sku, out stores))
    {
        stores = new Dictionary<string, int>();
        skus.Add(sku, stores);
    }
    stores.Add(row["store"].ToString(), (int)row["qty"]);
}

RootObject root = new RootObject { Skus = skus };

Once you have the data gathered into your RootObject, it is trivial to serialize it to JSON using Json.Net:

string json = JsonConvert.SerializeObject(root, Formatting.Indented);

Deserializing the JSON back to your RootObject is just as easy:

RootObject root = JsonConvert.DeserializeObject<RootObject>(json);

Here is a full round-trip demo: https://dotnetfiddle.net/qR3wbE

meda · Accepted Answer · 2014-11-05 16:05:31Z

Right now you have values as keys , because JSON is meant to group your records by objects. I recommend you to change your JSON to the following, which will make it easier to work with

{
    "skus": [
        {
            "sku": 20000,
            "store": 100,
            "qty": 1
        },
        {
            "sku": 20000,
            "store": 132,
            "qty": 0
        }
    ]
}

Model:

public class Sku
{
    public int sku { get; set; }
    public int store { get; set; }
    public int qty { get; set; }
}

public class RootObject
{
    public List<Sku> skus { get; set; }
}

Parse JSON to C# Objects

var skusObjects =  JsonConvert.DeserializeObject<RootObject>(json);

foreach (var item in skusObjects.skus)
{   
    Console.WriteLine(item.sku);
}

Output

DEMO

Muhammad Nour · Accepted Answer · 2014-11-05 08:29:19Z

0

You can do select form the table using linq-to-sql and do groupby and put the data in to object that you have defined before which have the structure that you described after that you can deserialize and send back as json

answered Nov 5, 2014 at 8:29

Muhammad Nour

2,3572 gold badges20 silver badges25 bronze badges

1 Comment

Muhammad Nour Over a year ago

I am actually doing this in my development and it is working fine with me, so this is considered as a real solution as I suppose !

Collectives™ on Stack Overflow

Converting a DataTable to nested JSON output

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related