Datatables not reading column data from JSON object

parsonsparsons

I am returning the following JSON object from an MVC controller (edited slightly):

{"data":
{"regs":[{"id":xyzz,"state_id":4,"firm_id":xxx,"status_id":2,"state_abbr":"XX","req_date":"/Date(-231312312312)/","app_date":"/Date(-12312312312)/","trm_date":"/Date(-13212312312)/","status_desc":"Registered"},
[{"id":zzzsss,"state_id":3,"firm_id":xyx,"status_id":2,"state_abbr":"XY","req_date":"/Date(-231312312312)/","app_date":"/Date(-666688788)/","trm_date":"/Date(-13212312312)/","status_desc":"Registered"},


"columns":["State","Requested Date","Approved Date","Terminated Date","Status"]}
}

My javascript below (edited slightly)

 $("#mytable").DataTable({

        ajax: "/myurl/" + btnClicked.id + "?id=" + id + "&type=" + btnClicked.id,
          
        columns: [
            { "data": "regs.state_abbr" },
            { "data": "regs.req_date" },
            { "data": "regs.app_date" },
            { "data": "regs.trm_date" },
            { "data": "regs.status_desc" }

        ]
    });

This isn't working however, I get no data back in my datatable. If I change the object returned in the controller to just the regs object and modify the columns like below, it returns data just fine:

columns: [
            { "data": "state_abbr" },
            { "data": "req_date" },
            { "data": "app_date" },
            { "data": "trm_date" },
            { "data": "status_desc" }

        ]

Any help you can provide would be appreciated.

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

colin

Hi @parsonsparsons ,

The returned data isn't valid JSON. It would be worth looking at this example as it shows how to deal with nested data structures.

Cheers,

Colin

parsonsparsons

Here is the returned JSON from my controller (edited slightly):

{"data":{"regs":[{"id":8118,"state_id":4,"f_id":376,"status_id":2,"state_abbr":"AR","req_date":"/Date(-2208970800000)/","app_date":"/Date(-2208970800000)/","trm_date":"/Date(-2208970800000)/","status_desc":"Registered"},{"id":8119,"state_id":31,"f_id":376,"status_id":2,"state_abbr":"NJ","req_date":"/Date(-2208970800000)/","app_date":"/Date(-2208970800000)/","trm_date":"/Date(-2208970800000)/","status_desc":"Registered"},{"id":8120,"state_id":29,"f_id":376,"status_id":2,"state_abbr":"NV","req_date":"/Date(-2208970800000)/","app_date":"/Date(-2208970800000)/","trm_date":"/Date(-2208970800000)/","status_desc":"Registered"},{"id":8121,"state_id":23,"f_id":376,"status_id":2,"state_abbr":"MI","req_date":"/Date(-2208970800000)/","app_date":"/Date(-2208970800000)/","trm_date":"/Date(-2208970800000)/","status_desc":"Registered"}],"columns":["State","Requested Date","Approved Date","Terminated Date","Status"]}}

This is not valid JSON? It is returned from my MVC controller.

kthorngren

Its valid according to https://jsonlint.com/ . Here is is reformatted so its easier to read:

{
    "data": {
        "regs": [{
            "id": 8118,
            "state_id": 4,
            "f_id": 376,
            "status_id": 2,
            "state_abbr": "AR",
            "req_date": "/Date(-2208970800000)/",
            "app_date": "/Date(-2208970800000)/",
            "trm_date": "/Date(-2208970800000)/",
            "status_desc": "Registered"
        }, {
            "id": 8119,
            "state_id": 31,
            "f_id": 376,
            "status_id": 2,
            "state_abbr": "NJ",
            "req_date": "/Date(-2208970800000)/",
            "app_date": "/Date(-2208970800000)/",
            "trm_date": "/Date(-2208970800000)/",
            "status_desc": "Registered"
        }, {
            "id": 8120,
            "state_id": 29,
            "f_id": 376,
            "status_id": 2,
            "state_abbr": "NV",
            "req_date": "/Date(-2208970800000)/",
            "app_date": "/Date(-2208970800000)/",
            "trm_date": "/Date(-2208970800000)/",
            "status_desc": "Registered"
        }, {
            "id": 8121,
            "state_id": 23,
            "f_id": 376,
            "status_id": 2,
            "state_abbr": "MI",
            "req_date": "/Date(-2208970800000)/",
            "app_date": "/Date(-2208970800000)/",
            "trm_date": "/Date(-2208970800000)/",
            "status_desc": "Registered"
        }],
        "columns": ["State", "Requested Date", "Approved Date", "Terminated Date", "Status"]
    }
}

You will need to use ajax.dataSrc to have Datatables retrieve the data from data.regs. Something like this:

  ajax: {
    url: ""/myurl/" + btnClicked.id + "?id=" + id + "&type=" + btnClicked.id,
    dataSrc: "data.regs"
  }

And use the second columns.data you show.

Kevin

parsonsparsons

So you are correct, but lets say I wanted to access the columns array for the column titles, is that possible?

kthorngren

Not if you are using Datatables ajax. You will need to define the columns before initialization. You will need to use an external ajax call like this example:
http://live.datatables.net/huyexejo/1/edit

Note the use of data to add the data to Datatables.

Kevin

parsonsparsons

Awesome thank you!