Generically access JSON values and map to columns by position instead of name

jboldt · June 2017

This must be simple but I'm missing it when combing through the documentation and forums. I'm trying to determine how to generically populate DataTables columns and values when calling an API that may return JSON data with different key/value pairs, i.e. the names are dynamic and thus cannot be coded explicitly in the javascript code. The JSON returned looks like

[{
    "id":123,
    "variableCode": ABC, // Note that "variableCode" may vary by API call.
    "variableDescription":"Description" // Thus, access the value at this position.
}]

Something like the following would be helpful:

$("table").DataTable({
           ajax: {
               url: "../api/",
               dataSrc: ""
           },
           columns: [
               {
                   data: 0 // where "0" is the first value in the JSON results
               }
           ]
       }

I should add that when trying this technique I receive the following error: DataTables warning: table id=DataTables_Table_0 - Requested unknown parameter '0' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4

tangerine · June 2017

Please don't make duplicate posts. You are getting answers in your earlier thread.

jboldt · June 2017

Tangerine, the prior discussion was _not _ actually answered but there was a vague suggestion! Since it wasn't listed as a "question" type of thread for clarity I opened the "question" here to track whether or not it's answered. Thanks.

kthorngren · June 2017

You will need to create an ajax request outside of Datatables to get the column names. Maybe this thread will help:
https://datatables.net/forums/discussion/comment/93902/#Comment_93902

Kevin

allan · June 2017

Yup - what Kevin says. See also the FAQ on this topic:

Q. Can I define my columns in Ajax loaded JSON?

Allan

jboldt · June 2017

Are there any examples documented that show how exactly to do this and generically reference the columns? I'm having a bit of a problem figuring out how to make DataTables happy here...

allan · June 2017

Nope sorry. I should probably add one sometime.

You just need to use $.ajax to load your JSON. Then loop over the data to build up the columns array and then finally populate both the columns and data options for DataTables.

The reason I'm not a massive fan of that approach myself is that if there is no data in the table, then no columns would be defined, and no data is a perfectly valid use case.

Allan

jboldt · June 2017

Allan -

Maybe I'm asking the wrong question then. My goal is to have a single page which has a single datatables table which can be used to display variable json api results. The results are typically in the form of id, code and description but with differing column names. Ideally I would use the Editors plug-in to eliminate needing to load the restful mvc url for each result but first I'd like to figure out step one here. What's the recommended way to do this? Below is the code that I'm working on but maybe there's an better and different approach using DataTables?

<script type="text/javascript">

    var table;

    $('select').on('change', function () {

        // Retrieve the datatable table name and description selected from a drop-down
        urlMvc = $(this).find("option:selected").text();
        urlApi = this.value.trim()

        // Create strings that conform to table columns (prefix changes but suffix stays the same).
        column1 = urlApi.substring(1).toLowerCase() + "cd"
        column2 = urlApi.substring(1).toLowerCase() + "ds"

        if (typeof table !== 'undefined') {
            // This might work if I can modify the column names to reflect the new api results
            table.clear().draw();
            table.ajax.url("../api/editor/" + urlApi).load();
        }
        else {
            //This works
            table = $("#myDataTable").DataTable({
                ajax: {
                    url: "../api/editor/" + urlApi,
                    dataSrc: ""
                },
                columns: [
                    {
                        data: column1
                    },
                    {
                        data: column2
                    },
                    {
                        render: function (data, type, myApiResult) {
                            return "<a href='/" + urlMvc + "/edit/" + myApiResult.id + "'>Edit</a>"
                                + "    |     " + "<a href='/" + urlMvc + "/delete/" + myApiResult.id + "'>Delete</a>"
                                + "    |     " + "<a href='/" + urlMvc + "/details/" + myApiResult.id + "'>Details</a>"
                        }
                    }
                ]
            }
            )
        }
    });

</script>

bindrid · June 2017

How many tables?
I have created code similar to what you are doing but there may be a better way.

If the user keeps switch the value in your select box, you are going to end up getting data you have already retrieved for this use.

Instead, consider, on select box change;
1. Hide all tables that might have already been fetched
2. Check to see if that table has already been loaded. If so, make it visible.
3. If not already loaded, load it.

jboldt · June 2017

Bindrid - A few hundred. Hiding that many will surely take up resources unnecessarily!

bindrid · June 2017

take a look at http://jsbin.com/gumijev/176/edit?js,output and see if it is any help

jboldt · June 2017

Interesting! It appears that my JSON results are formatted differently, e.g.

 {
                     "id": 1,
                    "code": "2"
                     "description": "4"
 }

If I modify the controller to include the data and title tag's I think your example which sets the columns to cols will work for the generic column mapping. Is there a way to reload the table instead of destroy it so that we avoid the performance penalty?

allan · June 2017

Is there a way to reload the table instead of destroy it so that we avoid the performance penalty?

If you are loading data in the same column structure, use ajax.reload(). If you are changing the column structure, then yes, you need to destroy the table first.

Allan

jboldt · June 2017

Thanks for the examples everyone. Works as desired.

Generically access JSON values and map to columns by position instead of name

Generically access JSON values and map to columns by position instead of name

Answers

Howdy, Stranger!

Categories

DataTables

Generically access JSON values and map to columns by position instead of name

Generically access JSON values and map to columns by position instead of name

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables