Datatables not reading column data from JSON object

Datatables not reading column data from JSON object

parsonsparsonsparsonsparsons Posts: 29Questions: 8Answers: 0
edited September 2019 in Free community support

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.

This question has an accepted answers - jump to answer

Answers

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • parsonsparsonsparsonsparsons Posts: 29Questions: 8Answers: 0

    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.

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735
    edited September 2019

    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

  • parsonsparsonsparsonsparsons Posts: 29Questions: 8Answers: 0

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

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735
    edited September 2019 Answer ✓

    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

  • parsonsparsonsparsonsparsons Posts: 29Questions: 8Answers: 0

    Awesome thank you!

This discussion has been closed.