I can't manage to put this JSON data inside DataTables

Question

So, the situation is this. There is a HTML page with a table in it, that is using the DataTables plugin. I have to show data that I'm receiving from a jQuery POST call in the table, but I always seem to get errors and am lost in how to go about doing that.

This is what the response from the POST call looks like:

[{"idoperatore":10,"nome_cognome":"Daniele Torrini","tariffa_esterno":"50.00","tariffa_interno":"0.00","tariffa_viaggio":"30.00","idtariffa_esterno":11,"idtariffa_interno":16,"idtariffa_viaggio":13,"attivo":1,"rs":0,"iniziali":"DT"},{"idoperatore":12,"nome_cognome":"Irene Cavalletto","tariffa_esterno":"75.00","tariffa_interno":"45.00","tariffa_viaggio":"30.00","idtariffa_esterno":9,"idtariffa_interno":15,"idtariffa_viaggio":13,"attivo":1,"rs":1,"iniziali":"IC"},{"idoperatore":14,"nome_cognome":"Sandra Moschetti","tariffa_esterno":"50.00","tariffa_interno":"0.00","tariffa_viaggio":"30.00","idtariffa_esterno":11,"idtariffa_interno":16,"idtariffa_viaggio":13,"attivo":1,"rs":0,"iniziali":"SM"},{"idoperatore":15,"nome_cognome":"Federica Coucourde","tariffa_esterno":"90.00","tariffa_interno":"0.00","tariffa_viaggio":"30.00","idtariffa_esterno":8,"idtariffa_interno":16,"idtariffa_viaggio":13,"attivo":1,"rs":0,"iniziali":"FC"},{"idoperatore":16,"nome_cognome":"Matteo Belgero","tariffa_esterno":"75.00","tariffa_interno":"0.00","tariffa_viaggio":"30.00","idtariffa_esterno":9,"idtariffa_interno":16,"idtariffa_viaggio":13,"attivo":1,"rs":0,"iniziali":"MB"},{"idoperatore":17,"nome_cognome":"Luca Belgero","tariffa_esterno":"90.00","tariffa_interno":"0.00","tariffa_viaggio":"30.00","idtariffa_esterno":8,"idtariffa_interno":16,"idtariffa_viaggio":13,"attivo":1,"rs":0,"iniziali":"LB"},{"idoperatore":18,"nome_cognome":"Federico Bottoni","tariffa_esterno":"50.00","tariffa_interno":"0.00","tariffa_viaggio":"30.00","idtariffa_esterno":11,"idtariffa_interno":16,"idtariffa_viaggio":13,"attivo":1,"rs":0,"iniziali":"FB"},{"idoperatore":19,"nome_cognome":"Giuseppe Pantaleo","tariffa_esterno":"60.00","tariffa_interno":"0.00","tariffa_viaggio":"30.00","idtariffa_esterno":10,"idtariffa_interno":16,"idtariffa_viaggio":13,"attivo":1,"rs":0,"iniziali":"GP"},{"idoperatore":20,"nome_cognome":"Matteo Ferrario","tariffa_esterno":"90.00","tariffa_interno":"75.00","tariffa_viaggio":"30.00","idtariffa_esterno":8,"idtariffa_interno":9,"idtariffa_viaggio":13,"attivo":1,"rs":1,"iniziali":"MF"},{"idoperatore":21,"nome_cognome":"Alessandro Mazzeranghi","tariffa_esterno":"100.00","tariffa_interno":"0.00","tariffa_viaggio":"30.00","idtariffa_esterno":7,"idtariffa_interno":16,"idtariffa_viaggio":13,"attivo":1,"rs":0,"iniziali":"AM"}]

I have no way of modifying the call, I have to work with that. I just have access to the variable that contains that response from the callback, but I can however transform or modify that data if needed.

This is what the HTML table looks like:

<table class="display nowrap" id="table_operatori">
  <thead>
    <tr>
      <th>
        <span></span>
      </th>
      <th class="mdl-data-table__cell--non-numeric">Nome e Cognome</th>
      <th>Tariffa Esterno</th>
      <th>Tariffa Interno</th>
      <th>Tariffa Viaggio</th>
      <th>Attivo?</th>
      <th>RS?</th>
      <th class="mdl-data-table__cell--non-numeric">Iniziali</th>
    </tr>
  </thead>
  <tbody id="table_operatori_tbody">
  </tbody>
</table>

There are not the same number of columns in the table as fields in the JSON because the fields in JSON starting with "id" have to be hidden values, and were used before as attributes of the HTML elements, to use them in later moments. It's also the reason for the empty header: the table was actually filled with pure HTML before, and had a checkbox in front to select the row, like this:

data.forEach(function (element) {
    element["attivo"] == "1" ? element["attivo"] = "Si" : element["attivo"] = "No";
    element["rs"] == "1" ? element["rs"] = "Si" : element["rs"] = "No";

    var i = element['idoperatore'];
    var tableRow = '<tr><td><label class="mdl-checkbox mdl-js-checkbox mdl-js-ripple-effect mdl-data-table__select" for="table_operatori_checkbox_row[' + i + ']"><input type="checkbox" id="table_operatori_checkbox_row[' + i + ']" class="mdl-checkbox__input" onClick="fOperatore_Checkbox_SelectUnique(' + i + ')" /></label></td>'
    tableRow += '<td class="mdl-data-table__cell--non-numeric" id="table_operatori_nomecognome_row[' + i + ']">' + element['nome_cognome'] + '</td>';
    tableRow += '<td id="table_operatori_tariffaesterno_row[' + i + ']" idtariffa="' + element["idtariffa_esterno"] + '">' + element['tariffa_esterno'] + '</td>';
    tableRow += '<td id="table_operatori_tariffainterno_row[' + i + ']" idtariffa="' + element["idtariffa_interno"] + '">' + element['tariffa_interno'] + '</td>';
    tableRow += '<td id="table_operatori_tariffaviaggio_row[' + i + ']" idtariffa="' + element["idtariffa_viaggio"] + '">' + element['tariffa_viaggio'] + '</td>';
    tableRow += '<td id="table_operatori_attivo_row[' + i + ']">' + element['attivo'] + '</td>';
    tableRow += '<td id="table_operatori_rs_row[' + i + ']">' + element['rs'] + '</td>';
    tableRow += '<td class="mdl-data-table__cell--non-numeric" id="table_operatori_iniziali_row[' + i + ']">' + element['iniziali'] + '</td></tr>';

    $("#table_operatori_tbody").append(tableRow);

This worked, in a sense, (apart from being extremely ugly) meaning that the table formed and you could select rows like we wanted and act on those later. But you couldn't sort, or filter with search, any of the rows in the table.
Still, I was willing to maintain the ugly HTML building if it meant getting the DataTable to work, since with .row.add() you can add a element, I tried that as well, changing the .append(tableRow) with:
.DataTable().row.add($.parseHTML(tableRow)); This didn't work either, and gave the same error. Also displayed this on the table though: Displays object picture

At the moment of initialization, I don't have the data to put inside the table. The table has to be initialized empty, and rows from the response added at a later time. I tried (with "data" being the variable containing the response from the server):

$("#table_operatori").DataTable().rows.add(data);

Which would remove a lot of the ugly HTML building, but it gives error:

DataTables warning: table id=table_operatori - Requested unknown parameter '1' for row 0, column 1. For more information about this error, please see http://datatables.net/tn/4

So, by looking at that tech-notes link, it says that it may be that you have more columns in the table head than in the table body, so I matched exactly the fields that I get, when defining the table, thinking that I may eventually be able to hide the columns that I don't need if that works.

    $("#table_offerte").DataTable({
      paging: false,
      info: false,
      columns: [
        { title: "idoperatore" },
        { title: "nome_cognome" },
        { title: "tariffa_esterno" },
        { title: "tariffa_interno" },
        { title: "tariffa_viaggio" },
        { title: "idtariffa_esterno" },
        { title: "idtariffa_interno" },
        { title: "idtariffa_viaggio" },
        { title: "attivo" },
        { title: "rs" },
        { title: "iniziali" }
      ]
    });

But it still gives the same error. It also does if I match the table structure with the DataTable initialization :

      columns: [
        { title: "idoperatore" },
        { title: "nome_cognome" },
        { title: "tariffa_esterno" },
        { title: "tariffa_interno" },
        { title: "tariffa_viaggio" },
        { title: "attivo" },
        { title: "rs" },
        { title: "iniziali" }
      ]

Documentation from DataTables also says that it looks inside a data: property when looking at JSON data, and you have to specify if it is not an object but an array by setting an empty string in the dataSrc property:

DataTable({ ajax: { url: "something.json", dataSrc: "" } });

The problem is that it requires the data being requested by the url: property, and I cannot do that, because I only have the "data" variable which contains the JSON.

I should also mention that by maintaining the old HTML building and appending it inside the table body, makes the table work and display stuff right, like this, but of course as soon as you try to sort or filter anything, it all disappears because the DataTable doesn't ACTUALLY have the rows inside it, just the HTML does. I have no idea how to get this data in there. I hope I explained everything clearly, otherwise feel free to ask anything and I will try my best to clarify.
Thanks in advance for any help.

Answer 1

The doc is a bit confusing, because there are so many different ways to set up a table, and it's pretty much one guy (Allan Jardine) both writing the plugin and documenting it.

First, you don't need any table headers. Change your HTML to this:

 <table class="display nowrap" id="table_operatori"></table>

If you want to add ids or classes to tbody tags, then you'll need to add them in as well. But to start, this is all the HTML you need.

What's confusing here is that a lot of Allan's examples include the data hard-coded into the HTML, with no JSON or AJAX or anything involved. When you do that, then you do need to set up the HTML headers, and all the cells, and everything else. Take a look at the HTML in one of his examples (this one, for example) and see this for yourself. And then, when he moves on to JSON examples, he pulls the data but he leaves the headers in. And again, you can put them in, but don't have to.

Rather, if you're pulling your data from JSON, you can either specify your headers with HTML th tags or you can do it with the columns (or colDefs) option. You don't need to do both. This isn't as clear from the doc as it might be, since in most of the examples Allan does do both.

Whichever way you specify the headers, they have to match the column count of the JSON feed. If they don't, you'll get some form of the error you're getting. Furthermore, if you use both column and th, they both have to match your JSON field count or you'll get that error. That's why you're getting your error. You matched your columns definition correctly, but you've left some th tags out in your table definition. The solution is to remove the th tags entirely.

I'm going to presume that the reason that you left out some th tags is that you are under the impression that that's the way to make the column invisible. It isn't, for the reasons I've described above. The easiest way to define whether a column is visible or not (as well as define a lot of other possible attributes, listed here) is in your columns array: just set the column's visible option to false. (You could also use th tags with a class and set visibility:none in CSS, but this is simpler. Less to keep track of.)

Also, the title value on a column is the value for title in your columns array for that column. So, you need to make it look the way you want it, not put the name of your JSON field there.

Finally, with the data option, you're reading the wrong part of the documentation, which is about how to pull JSON from a URL using AJAX at the time you run dataTable(). You have the data already in your POST data, so you don't need to do that. So, read this instead. Have a look at the second example, which shows an array of objects as a data source. From what I see of your JSON string, you should just have to add an option like this:

data: myPOSTResponse,

Putting all that together:

$("#table_offerte").DataTable({
  paging: false,
  info: false,
  data: myPOSTResponse,
  columns: [
    { visible: false },  //this is the ID you don't want to see, no need to give it a title
    { title: "Nome e Cognome", className: "mdl-data-table__cell--non-numeric" },
    { title: "Tariffa Esterno" },
    { title: "Tariffa Interno" },
    { title: "Tariffa Viaggio" },
    { visible: false },  
    { visible: false },  
    { visible: false },  
    { title: "Attivo?" },
    { title: "RS?" },
    { title: "Iniziali", className: "mdl-data-table__cell--non-numeric" }
  ]
});

That should get you running, if you haven't done something else interesting. :)

Edit: as DocCobra mentions in the comments, you also have to specify the data: option at the field level here, since the array elements are objects. If they are themselves arrays, you do not.