How to properly create a JSON file in Web API

Question

Following DataTable Data (The "|" is the separator to match respective Col3 to Col4/Col5):

Col1        Col2        Col3            Col4                                Col5
John        Doe         12              156-345                             792-098
Mike        Keller      12|15           145-394|909-203                     156-323|121-444,134-232
Hanes       Wara        34|12|18        180-655,202-175|123-654|118-000     121-343|654-222|109-220

I currently have a custom List which just displays the data like this in the API call:

[HttpGet]
public System.Web.Mvc.JsonResult RTData()
{
    //...call SQL to retrieve the data and populate the {custom List}
    //return...
    return new System.Web.Mvc.JsonResult { Data = {custom List} };
}

This is what I get (in Browser Console) when I call the API from JQuery:

Object
    ContentEncoding:null
    ContentType:null
    Data:Array(12)
        [0 … 11]
        length:12
        __proto__:Array(0)
    JsonRequestBehavior:1
    MaxJsonLength:null
    RecursionLimit:null
    __proto__:Object

Each result in the Data is like this:

Data:Array(12)
    [0 … 11]
    0:
        Col1: "John"
        Col2: "Doe"
        Col3: "12"
        Col4: "156-345"
        Col5: "792-098"
    1:
        Col1: "Mike"
        Col2: "Keller"
        Col3: "12|15"
        Col4: "145-394|909-203  "
        Col5: "156-323|121-444,134-232"

Desired output in JSON:

{
    "Col1": "John",
    "Col2": "Doe",
    "Col3Combined": [
        {
            "Col3": "12",
            "Col4Combined": [
                {
                    "Col4": "156-345"
                }
            ],
            "Col5Combined": [
                {
                    "Col5": "792-098"
                }
            ]
        }
    ]
},
{
    "Col1": "Mike",
    "Col2": "Keller",
    "Col3Combined": [
        {
            "Col3": "12",
            "Col4Combined": [
                {
                    "Col4": "145-394"
                }
            ],
            "Col5Combined": [
                {
                    "Col5": "156-323"
                }
            ]
        },
        {
            "Col3": "15",
            "Col4Combined": [
                {
                    "Col4": "909-203"
                }
            ],
            "Col5Combined": [
                {
                    "Col5": "121-444",
                    "Col5": "134-232"
                }
            ]
        }
    ]
}...//more data

How can I achieve the JSON format in the API call?

Class I came up with which should suffice?

public class RootObject
{
    public string col1 { get; set; }
    public string col2 { get; set; }
    public List<col3data> col3 { get; set; }
}

public class col3data
{
    public string col3d { get; set; }
    public List<col4data> col4d { get; set; }
    public List<col5data> col5d { get; set; }
}

public class col4data
{
    public string col4 { get; set; } //since col4 can also have comma separated values within each entry, as seen for Hanes, should this be a list too?
}

public class col5data
{
    public string col5 { get; set; } //since col5 can also have comma separated values within each entry, as seen for Mike, should this be a list too?
}

Something like this also:

public class RootObject
{
    public string col1 { get; set; }
    public string col2 { get; set; }
    public List<col3data> col3 { get; set; }
}

public class col3data
{
    public string col3d { get; set; }
    public List<col4data> col4d { get; set; }
    public List<col5data> col5d { get; set; }
}

public class col4data
{
    public List<col4subdata> col4sub { get; set; }
}

public class col4subdata
{
    public string col4_1 { get; set; }
    public string col4_2 { get; set; }
}

public class col5data
{
    public List<col5subdata> col5sub { get; set; }
}

public class col5subdata
{
    public string col5_1 { get; set; }
    public string col5_2 { get; set; }
}

I guess now I would have to iterate through each row and add to the RootObject class and create a JSON from it. Can I have some assistance with that?

please show how you are getting that output in the browser console. `console.log(JSON.stringify(data));` (or similar depending on your variable name) should show it as JSON. You can also check in the browser's network tab exactly what the raw response from the server looked like. That output looks like what you probably get if you just log a JS object directly to the console without stringifying it (bear in mind that your code probably automatically converted the JSON string into a JS object) — ADyson, Aug 15 '17 at 20:06
Web API 2... just to clarify, I just like to request help to create the subkey from the main key and iterate that. — Si8, Aug 15 '17 at 20:46

Madhan Kumar · Accepted Answer · 2017-08-15T20:10:23.107

1

You need to use JavascriptSerializer.

JavaScriptSerializer js = new JavaScriptSerializer();
Context.Response.Write(js.Serialize(lstCustom));

In the JQuery function when you call the API, if you use Console.log(response.data), you should see the custom list in JSON format.

You will have to create a class which has properties matching your columns in the resultset.

class Custom
{
    public string Col1 {get; set;}
    ....
    public List<int> Col3 {get; set;}
    ....
}

And then iterate over the columns in your result set to construct the custom list object (lstCustom). Use that with the js.Serialize() like above.

edited Aug 15 '17 at 20:10

answered Aug 15 '17 at 20:03

Madhan Kumar

121
7

Thanks for the reply. Notice how the data is coming in and I have to split them up. Also when I use JSSerializer, I get `"` around my JSON. – Si8 Aug 15 '17 at 20:04
Web API serialises data to JSON by default already, using JSON.NET. – ADyson Aug 15 '17 at 20:05
But the column data are in in different places, so how would the logic work? – Si8 Aug 15 '17 at 20:38
Create a list of custom objects like, List lstCustom = new List(); and then once you have the resultset from your sql query, iterate over the resultset, create Custom object for each row and add it to the lstCustom list. – Madhan Kumar Aug 15 '17 at 20:44
I added the class which should work, can you let me know your feedback. – Si8 Aug 15 '17 at 20:56
I have not tested it but it looks ok. You can update the answer after you tested it. – Madhan Kumar Aug 15 '17 at 20:57
Can you assist with this https://stackoverflow.com/questions/45716358/how-to-iterate-data-to-insert-into-a-list-to-create-a-json-format-data please – Si8 Aug 16 '17 at 15:27

score 0 · Answer 2 · answered Aug 15 '17 at 20:08

0

I believe the issue is that your list is not serialized and contenttype is null so the browser is unsure how to treat it. See the answer to this, hopefully this helps you too: Return a JSON string explicitly from Asp.net WEBAPI?

answered Aug 15 '17 at 20:08

Jacques

1
2

Everything is working for me but I want to create nested keys as you can see from the desired result. – Si8 Aug 15 '17 at 20:34
1

Ok cool. As others have said by now, your nested keys will need to be on your object as collection type( List etc.). Once you serialize that oject and return the serialized json object it will correctly have them nested. – Jacques Aug 15 '17 at 20:58

How to properly create a JSON file in Web API

2 Answers2