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')

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.