How to convert the following data from a DataTable to a JSON object

Question

Currently I am getting data from a database in the following format which I am storing in a DataTable.

Data in Present Format:

Block   HouseNo   ResidentId
  A       101        1
  A       102        2
  A       103        3   
  B       101        4
  B       102        5  
  B       104        6

I want to convert it into a JSON object in the following format.

Required Format:

{
    "Block A" : [{1:"101"},{2:"102"},{3:"103"}],
    "Block B" : [{4:"101"},{5:"102"},{6:"104"}] 
}

I have edited the above formats. Can you help me in getting the desired results?

The idea is pretty simple: Retrieve the initial data, convert the data, format the result as JSON. Now, which part thereof makes problems? — Ulrich Eckhardt, Jan 07 '18 at 13:06
But I want to get distinct values as well. Can you please suggest for that? — Prashant Gupta, Jan 07 '18 at 13:12
The linked answer [Convert datatable to JSON in C#](https://stackoverflow.com/q/17398019/3744182) doesn't seem particularly relevant so I went ahead and reopened this. — dbc, Jan 07 '18 at 19:46

dbc · Answer 1 · 2018-01-07T21:40:17.917

You can do this by transforming your DataTable to a Dictionary<string, string []> and then serializing it with a JSON serializer that supports formatting of dictionaries as JSON objects with dictionary keys mapped to JSON property names. json.net and javascriptserializer support this out of the box; as does datacontractjsonserializer in .Net 4.5 and later if you set UseSimpleDictionaryFormat = true.

Transforming from a data table to a dictionary can be done using Linq as follows, after which the dictionary can be serialized directly:

// Choose the property name and value keys.
var propertyKey = "Block";
var valuesKey = "HouseNo";

// Generate the Dictionary<string, string []>
var dictionary = dataTable.AsEnumerable()
    // Generate a grouping of all houses for each block
    .GroupBy(r => r.Field<string>(propertyKey), r => r.Field<string>(valuesKey))
    // And convert to a dictionary of names and array values for JSON serialization.
    .ToDictionary(g => propertyKey + " " + g.Key, g => g.ToArray());

// Now serialize to JSON with your preferred serializer.  Mine is Json.NET
var json = JsonConvert.SerializeObject(dictionary, Formatting.Indented);

Which produces the following JSON:

{
  "Block A": [
    "101",
    "102",
    "103"
  ],
  "Block B": [
    "101",
    "102",
    "104"
  ]
}

Notes:

Be sure to use the namespace using System.Data; and add a reference to System.Data.DataSetExtensions.dll as I am using some extension methods from there.
I am not aware of any JSON serializer that formats the IEnumerable<IGrouping<TKey, TElement>> returned by GroupBy() as a JSON object with group keys mapped to property names, which is why it is necessary to generate a final dictionary representation from the intermediate grouped representation.

Sample working .Net fiddle.

Brian Rogers · Accepted Answer · 2018-01-14T19:51:12.893

One way to achieve the result you want is by using Json.Net's LINQ-to-JSON API. Here is how you would do that:

// Create a new JObject by grouping the data table rows by block and then
// selecting the groups into JProperties where the block is the property name
// and the property value is a JArray containing JObjects with the resident IDs 
// and corresponding house numbers in the group making up the properties of those. 

var obj = new JObject(
    table.Rows.Cast<DataRow>()
         .GroupBy(r => r["Block"])
         .Select(g => new JProperty("Block " + g.Key,
             new JArray(g.Select(r =>
                 new JObject(
                     new JProperty(r["ResidentId"].ToString(), r["HouseNo"])
                 )
             ))
         ))
);

// Convert the JObject to a JSON string
var json = obj.ToString();

Working demo: https://dotnetfiddle.net/smb9oW

var obj = new JObject( table.Rows.Cast() .GroupBy(r => r["Block"]) .Select(g => new JProperty("Block " + g.Key, new JArray(g.Select(r => new JObject(new JProperty(""+r["Houseno"],r["ResidentId"])))))) ); — Prashant Gupta, Jan 14 '18 at 13:31

How to convert the following data from a DataTable to a JSON object

Data in Present Format:

Required Format:

2 Answers2