Create table from JSON object in JavaScript

Question

I am trying to create an HTML table with information I use to draw a plot. I don't want to query the database twice, and I want to create the chart and a table with the information. This is what I get from the server and that it gets charted:

var data = {
              "selector":"#charttotalday",
              "jsondata":[
                 {
                    "label":"Client1",
                    "data":[
                            [1382670000000,110157],
                            [1382756400000,199055],
                            [1382842800000,362996],
                            [1382929200000,302],
                            [1383015600000,169],
                            [1383102000000,88],
                            [1383188400000,49]
                            ],
                    "points":{
                       "fillColor":"#88bbc8"
                    }
                 },
                 {
                    "label":"Client2",
                    "data":[
                            [1382670000000,58611],
                            [1382756400000,112268],
                            [1382842800000,193060],
                            [1382929200000,115],
                            [1383015600000,45],
                            [1383102000000,65],
                            [1383188400000,18]
                            ],
                    "points":{
                       "fillColor":"#ed7a53"
                    }
                 },
                 {
                    "label":"Client3",
                    "data":[
                            [1382670000000,65534],
                            [1382756400000,118362],
                            [1382842800000,200058],
                            [1382929200000,123],
                            [1383015600000,65],
                            [1383102000000,53],
                            [1383188400000,26]
                    ],
                    "points":{
                       "fillColor":"#9FC569"
                    }
                 }
              ]
           };

Because of the way the information is organized, I can not use $.each to loop over it and create a table of the format:

                Client1  Client2  Client3
1382670000000 |  10157 |  58611 |  65534 | 
1382756400000 |  99055 |  12268 |  18362 |
1382842800000 |  62996 |  93060 |  10058 |
1382929200000 |    302 |    115 |    123 |
1383015600000 |    169 |     45 |     65 |
1383102000000 |     88 |     65 |     53 |
1383188400000 |     49 |     18 |     26 |

I am thinking that the best way would be to read the object and create a new one with the structure that I need, that can be used with $.each.

I have tried with this:

$.each(data.jsondata, function(key, value) {
    thead += '<th>' + value.label + '</th>';
    tableData[value.label] = new Object();
    $.each(value.data, function(key1, value1) {
        $.each(value1, function(key2, value2) {
            if(key2 == 0) {
                //here I have the time line, axis Y
                index = value2;
            }
            if(key2 == 1) {
                //here I have the values for the table
                tableData[value.label][index] = value2;
            }
        });
    });
});

thead += '</tr>';

But this only creates a more simple element with the information that I need, but still cant turn into what I need.

Can someone please point me in the right direction?

Solution

This will map your data to table:

 var headers=[ ""], rows={}
  $.each(data.jsondata,function(clientIdx,item){
      headers.push(item.label );
      $.each( item.data,function(i,arr){
        if( !rows[arr[0]]){
         rows[arr[0]]=[];
        }
        rows[arr[0]][clientIdx]=arr[1];
      })
  })


  var rowHtml='<tr><th>'+headers.join('</th><th>')+'</th></tr>';
  $.each(rows,function(key, arr){
    rowHtml+='<tr><td>'+key+'</td>';
    rowHtml +='<td>'+arr.join('</td><td>')+'</td></tr>';

  })

  $('#table').html(rowHtml);

DEMO

Comments

[charlietfl]

only issue will be if each date doesn't have entry for each client...haven't tested that scenario , basically array for that row will have null element(s) within it. Will still organize real dates properly I think

[Don Viegues]

Yes, you are right. The only solution I see is to loop over all the dates and create a complete list and use it as index. I dont think I can complete it during the sql query.

[charlietfl]

not sure what you are looking for in that regard... a simple test of commenting out a few entries in original array still seems to work OK in my demo...cell just appears blank. If need something printed...can loop over each row array and put a placeholder like 'none` or whatever

[Don Viegues]

On my demo they move to the left because of the missing TDs, do you have any blank at the left side? Mine are only at the right. Thx again.

[charlietfl]

what browser are you using? I only looked at it in Firefox & chrome

[Don Viegues]

Mainly Firefox, also Chrome.

[charlietfl]

I'll play with it for a few minutes

[charlietfl]

I found what I think is a good fix. I create a full array for each row first...then change values while looping through data. In Demo WIll see a new function that has var placeholder="N/A" in it. plnkr.co/edit/jv7eqRQiq32pq2nNLxT2?p=preview

[Don Viegues]

Hi charlietfl, thank you for the reply. I have not been notified, that is why I took so long to respond. I will test it tomorrow!