Using JSON object with arrays not working with Datatables

Question

I'm struggling using my JSON data with datatables. Sorry for the formatting, but this is what I get in developer tools:

Object
pharmacy:Array[3]
0:"Walmart"
1:"Safeway"
2:"Kroger Pharmacy"
length:3
__proto__:Array[0]
price: Array[3]
0:58.14
1:65.45
2:66.76
length:3
__proto__:Array[0]
...

I'm getting that using this:

$.ajax({
    url: 'test.php',
    type: 'POST',
    dataType: 'JSON',
    data: {
      drug: picked_drug,
        },
    success: function(data) {
        var all_data = JSON.parse(data);
        final_data = all_data.data.price_detail;
        console.log(final_data); //this outputs object above.
}
});

Then I'm rendering the table like this:

var drugtable = $("#drug_datatable").DataTable({
            "data": final_data,              
            "paging": true,
            "dom": '<"top">Bt<"bottom"><"clear">',
            "pageLength": 25,
            "order": [
                [0, "desc"]
            ],
            "columns": [
            {
                "data": "pharmacy",
        "searchable": false,
        "width": "20%",
        "className": "lang_body_2",
        "title": "Attribute"
    }, 
    {
                "data": "price",
        "searchable": false,
        "width": "20%",
        "className": "lang_body_2",
        "title": "Attribute"
    }, 
              ],
        });

The problem is that my table returns "No data available in table". I'm sure there's a problem with how my object is formatted, but I can't figure out what to change.

Thanks!

Answer 1

I cannot actually test this code since I cannot fetch the data from the source but based on our discustion, this I my best bet.

<script type="text/javascript">

    // this flattens the data passed back by ajax
    function flattenData(list) {
        // now flatening for datatables
        var keys = Object.keys(list);
        var listLen = list[keys[0]].length;
        var fd= [];
        // create the list for data table
        for (var i = 0; i < listLen; ++i) {

            var item = {};
            for (var j = 0; j < keys.length; ++j) {
                item[keys[j]] = list[keys[j]][i];
            }
            fd[final_data.length] = item;
        }
        return fd;
    }


    // function is called from the success callback function to create the DataTable
    function createTable(tableData) {

        var drugtable = $("#drug_datatable").DataTable({
            "data": tableData,
            columns: [{ data: "pharmacy" }, { data: "price" }],
        });
    }

    $(function ($) {

        $.ajax({
            url: 'test.php',
            type: 'POST',
            dataType: 'JSON',
            data: {
                drug: picked_drug,
            },
            success: function (data) {
                var all_data = JSON.parse(data);
                var final_data = flattenData(all_data.data.price_detail);
                createTable(final_data)
                console.log(final_data); //this outputs object above.
            }
        });

    })
</script>

Answer 2

Is your data structure right? Your data looks like ["Safeway", "Kroger Pharmacy"], price:[58.14, 65.45, 66.76]}

You probably want [{pharmacy:"Walmart",price:58.14 }, {pharmacy:"Safeway", price:65.45}, {pharmacy:"Kroger Pharmacy", price:66.76} ]

this to flatten the data.

    // simulated the return from ajax as i understand your data
    function stuff () {
        this.pharmacy = ["Walmart","Safeway", "Kroger Pharmacy"];
        this.price = [58.14, 65.45, 66.76]
    }
    var list = new stuff();

    // now flatening for datatables
    var keys = Object.keys(list);
    var listLen = list[keys[0]].length;
    var final_data = [];
    // create the list for data table
    for (var i = 0; i < listLen; ++i) {

        var item = {};
        for (var j = 0; j < keys.length; ++j) {
            item[keys[j]] = list[keys[j]][i];
        }
        final_data[final_data.length] = item;
    }

I tested the data with this:

            var drugtable = $("#drug_datatable").DataTable({
            "data": final_data,
            columns: [{ data: "pharmacy" }, {data:"price"}]
        });