[GIS] CartoDB json to HTML Table using Javascript or Jquery

cartojavascriptjqueryjsontable

I am trying to display the dataset inside the cartoDB as HTML table. When i give request to the postgre database by SQL API, it is returning as the format (json) below in the snapshot. I want the returning dataset to be displayed as HTML table in the browser. I had tried with the below code but it is displaying table for var myList=[{"name" : "abc", "age" : 50},
{"age" : "25", "hobby" : "swimming"},
{"name" : "xyz", "hobby" : "programming"}];

But its not displaying for the below format.

enter image description here

And code i had tried is 

<script type="text/javascript">
        data1 = "transit_point_region_1";
        json_link = 'http://development.localhost.lan:8080/api/v2/sql?q=SELECT * FROM ' + data1;
        var data = $.getJSON(json_link);
        // Builds the HTML Table out of data.
        function buildHtmlTable() {
            var columns = addAllColumnHeaders(data);
            for (var i = 0 ; i < data.length ; i++) {
                var row$ = $('<tr/>');
                for (var colIndex = 0 ; colIndex < columns.length ; colIndex++) {
                    var cellValue = data[i][columns[colIndex]];
                    if (cellValue === null) { cellValue = ""; }
                    row$.append($('<td/>').html(cellValue));
                }
                $("#excelDataTable").append(row$);
            }
        }

    function addAllColumnHeaders(data). {
        var columnSet = [];
        var headerTr$ = $('<tr/>');
        for (var i = 0 ; i < data.length ; i++) {
            var rowHash = data[i];
            for (var key in rowHash) {
                if ($.inArray(key, columnSet) === -1){
                    columnSet.push(key);
                    headerTr$.append($('<th/>').html(key));
                }
            }
        }
        $("#excelDataTable").append(headerTr$);
        return columnSet;
    }
</script>
</head>
<body onLoad="buildHtmlTable()">
    <table id="excelDataTable" border="1">
        </table>

Best Answer

There are a couple issues here. First, the JSON object shown in your screenshot has a slightly different format than the one expected by your code. Notice that at the beginning, the first key is rows, which contains all the results as an array (in square brackets). You can extract the results in the format you need by assigning data.rows to a new variable (i.e. new_data = data.rows or the like).

Here is an example fiddle (forked from the answer to this stackoverflow question, which appears to be the source of your code):

http://jsfiddle.net/nathansnider/ws5rL3vy/

It uses data in the same format as the object from your screenshot and shows that extracting the rows array allows the data to be used in the functions that follow.

The second issue is that $.getJSON is an asynchronous method and will not return data immediately. You should be using the appropriate syntax if you want your data to be accessible to the rest of your code. Typically, this is done as a callback function within the getJSON function itself. Your buildHtmlTable function should look something like this:

function buildHtmlTable() {
    $.getJSON(json_link, function (result_data) {
        var data = result_data.rows;
        var columns = addAllColumnHeaders(data);

        for (var i = 0; i < data.length; i++) {
            var row$ = $('<tr/>');
            for (var colIndex = 0; colIndex < columns.length; colIndex++) {
                var cellValue = data[i][columns[colIndex]];

                if (cellValue == null) {
                    cellValue = "";
                }

                row$.append($('<td/>').html(cellValue));
            }
            $("#excelDataTable").append(row$);
        }
    });
}

Here is a fiddle using getJSON to grab a file from Dropbox, which contains the same data as in the previous example:

http://jsfiddle.net/nathansnider/goe84e04/

Related Question