DataTables: Generate whole table from JSON

Question

I am using jQuery DataTables and looking for a way how to generate DataTable fully from JSON. This should include the number of columns, their names, row data, but also may include other settings like sorting. I've seen this is possible, but among the many possible approaches not many work for me.

Here is my code, can you help me fix the error and elaborate on my current JSON config?

JSON - put here as much as possible:

{
    "columns": [
        [ "title" : "DT_RowId" ],
        [ "title" : "supplier" ],
        [ "title" : "color" ],
    ],
    "data": [
        [ "row_3", "small", "red" ],
        [ "row_3", "medium", "blue" ],
        [ "row_3", "medium", "blue" ],
        [ "row_11", "large", "blue" ],
    ]
}

JS:

$('#example').DataTable( {
    "ajax" : {
        "url": "http://127.0.0.1/tabledata.json",
        "type": "GET",
        "contentType" : "application/json",
        "dataType" : "jsonp",
    },
});

HTML - should be left to minimum:

<table id="example"></table>

Current error:

TypeError: undefined is not an object (evaluating 'e[i].aDataSort')

Fiacre

Answer 1

First you need to make the JSON correct. No need for each column as an array, and the data section is not valid JSON at all - it should be name->value pairs. When you want to have all info in the passed JSON you could use the following layout for tabledata.json :

{
    "columns": [
        { "data" : "DT_RowId", "title" : "Id" },
        { "data" : "supplier", "title" : "supplier" },
        { "data" : "color", "title" : "color" }
    ],
    "data": [
        { "DT_RowId" : "row_3", "supplier" : "small", "color" : "red" },
        { "DT_RowId" : "row_3", "supplier" : "medium", "color" : "blue" },
        { "DT_RowId" : "row_3", "supplier" : "medium", "color" : "blue" },
        { "DT_RowId" : "row_11", "supplier" : "large", "color" : "blue" }
    ]
}

Minimal markup :

<table id="example"/>

And the dataTable initialisation becomes very simple :

$.getJSON('http://127.0.0.1/tabledata.json', function(json) {
  $('#example').DataTable({
     data : json.data,
     columns : json.columns
  })
});

The reason here for not using the ajax attribute directly on the dataTable is due to the nature of javascript asynchronicity. If you used an ajax url on the dataTable it would be created before the JSON is loaded, thus it would fail because columns not yet were defined.

demo -> http://jsfiddle.net/xqu37Lka/

If you want to include other settings in the JSON, then simply add them to the JSON, for example default ordering :

{
    "columns": [...],
    "data" : [...],
    "order": [[ 3, "desc" ]]
}

and include json.order in the dataTable initialiation.

Brume

there's one problem with adapting your answer, my browser requires contentType: "application/json", dataType: "jsonp" to be attached to the HTTP request headers. I can't see where it could be attached to your dataTable initialization script. Probably I need a variant that uses $.ajax() call. – Fiacre

Got it working through calling this in success function of the $.ajax() call. Thanks for your kind help. – Fiacre

@PeterGerhat, yes - getJSON is just an easy wrapper, $.ajax is good as well. I was actually in the process of an $.ajax example when you wrote the comment above. Thank you for accepting the answer. – Brume