how to load dynamic json to jquery datatable

Question

I want to load dynamic data into my jquery datatable. That means, before I get the json data from server, I don't know what fields it contains, but I'm sure the json is valid. It will looks like below,

"data": [
{
  "first_name": "Airi",
  "last_name": "Satou",
  "position": "Accountant",
  "office": "Tokyo",
  "start_date": "28th Nov 08",
  "salary": "$162,700"
},
{
  "first_name": "Angelica",
  "last_name": "Ramos",
  "position": "Chief Executive Officer (CEO)",
  "office": "London",
  "start_date": "9th Oct 09",
  "salary": "$1,200,000"
}

]

sometimes, it may only contains 'first_name' and 'last_name'.

I've searched a long time, all of the samples specify 'aoColumnsDef' or 'aoColumns'. But I don't know the exact fileds. Is there a way to fill jquery datatable using the field name in json as the header of the table and the field value as the body of the table? For example, the json data only contains two fields, 'first_name' and 'last_name', the table should contains two columns 'first_name' and 'last_name'. If the json contains 3 fields, the table should display the 3 columns. I'm sure all of the items in "data" have the same fields.

Thanks in advance!

I understand that you don't know what kind of object you are going to receive in the array of data, but all the objects in the array would be equal? — josedefreitasc
– josedefreitasc, Commented Jul 6, 2015 at 15:35

BLSully · Accepted Answer · 2015-07-06 17:23:17Z

Using your example data, loop over the first record as your 'example' data, then create the column definitions on the fly like so:

EDIT: example of original code with an xhr call to retrieve data

$(document).ready(function() {
  
  //callback function that configures and initializes DataTables
  function renderTable(xhrdata) {
    var cols = [];

    var exampleRecord = xhrdata[0];

    var keys = Object.keys(exampleRecord);

    keys.forEach(function(k) {
      cols.push({
        title: k,
        data: k
          //optionally do some type detection here for render function
      });
    });

    var table = $('#example').DataTable({
      columns: cols
    });

    table.rows.add(xhrdata).draw();
  }

  //xhr call to retrieve data
  var xhrcall = $.ajax('/path/to/data');

  //promise syntax to render after xhr completes
  xhrcall.done(renderTable);
});

var data = [{
  "first_name": "Airi",
  "last_name": "Satou",
  "position": "Accountant",
  "office": "Tokyo",
  "start_date": "28th Nov 08",
  "salary": "$162,700"
},
{
  "first_name": "Angelica",
  "last_name": "Ramos",
  "position": "Chief Executive Officer (CEO)",
  "office": "London",
  "start_date": "9th Oct 09",
  "salary": "$1,200,000"
}];

$(document).ready( function () {
  var cols = [];
  
  var exampleRecord = data[0];
  
  //get keys in object. This will only work if your statement remains true that all objects have identical keys
  var keys = Object.keys(exampleRecord);
  
  //for each key, add a column definition
  keys.forEach(function(k) {
    cols.push({
      title: k,
      data: k
      //optionally do some type detection here for render function
    });
  });
  
  //initialize DataTables
  var table = $('#example').DataTable({
    columns: cols
  });
  
  //add data and draw
  table.rows.add(data).draw();
});

body {
  font: 90%/1.45em "Helvetica Neue", HelveticaNeue, Verdana, Arial, Helvetica, sans-serif;
  margin: 0;
  padding: 0;
  color: #333;
  background-color: #fff;
}


div.container {
  min-width: 980px;
  margin: 0 auto;
}

<!DOCTYPE html>
<html>
  <head>
    <script src="http://code.jquery.com/jquery-1.11.1.min.js"></script>

    <link href="//datatables.net/download/build/nightly/jquery.dataTables.css" rel="stylesheet" type="text/css" />
    <script src="//datatables.net/download/build/nightly/jquery.dataTables.js"></script>

    <meta charset=utf-8 />
    <title>DataTables - JS Bin</title>
  </head>
  <body>
    <div class="container">
      <table id="example" class="display" width="100%">
      </table>
    </div>
  </body>
</html>

Really thanks for your kindly answer! I'm a newbie of jquery, if I retrieve data from server by ajax, how to, how to deal with it? I researched many samples of jquery datatable, the ajax code should be in DataTable() method like: table.DataTable(){ serverSide: true, ajax: 'url' }
Given the fact you do not know the schema up front, you're better off using normal xhr calls to retrieve your data and only do the DataTables call after xhr completes. Will update answer with an example of that
Sounds like you're returning a string, not valid JSON. Or at the very least, content-type headers are wrong.

Sargon · Accepted Answer · 2015-07-06 15:51:17Z

1

How about build a simple html table from received JSON first and only after this build DataTable using created table.

var table = $("#tableId");
table.append('<thead>....</thead>');
table.append('<tbody>....</tbody>');

table.DataTable();

edited Jul 6, 2015 at 15:51

answered Jul 6, 2015 at 15:43

Sargon

2621 silver badge9 bronze badges

4 Comments

Gyrocode.com Over a year ago

Although your answer points to a correct direction, retrieve option is meaningless here.

Sargon Over a year ago

Yep, but it will be needed if you will reload the table using async call. In any case, I will remove it for now. THX!

Allen4Tech Over a year ago

Do you mean after retrieving the json, dynaically creating html table by build html string, and then convert it to jquery datatable?

Sargon Over a year ago

Yes, datatable plugin will just wrap existing table and extend it's functionality.

Collectives™ on Stack Overflow

how to load dynamic json to jquery datatable

2 Answers 2

9 Comments

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

4 Comments

Related