javascript - Converting JSON data for google chart datatable

Question

I am trying to convert this json into the correct format for a google chart:

var jsonData =  {"Battery Voltage, (A)":
                    {"2017-11-11T00:00:00.000Z":12.3
                    ,"2017-11-11T00:01:00.000Z":12.35
                    ,"2017-11-11T00:02:00.000Z":12.6
                    ,"2017-11-11T00:03:00.000Z":12.7
                    ,"2017-11-11T00:04:00.000Z":12.8}
                ,"Battery Current, (A)":
                    {"2017-11-11T00:00:00.000Z":1.3
                    ,"2017-11-11T00:01:00.000Z":1.4
                    ,"2017-11-11T00:02:00.000Z":1.5
                    ,"2017-11-11T00:03:00.000Z":1.6
                    ,"2017-11-11T00:04:00.000Z":1.7}};

Alternatively, I can get the data like this:

var jsonData_2 =
{"2017-11-16T00:00:00.000Z":
          {"Charge Current, (A)":0.001373312,"Battery Voltage, (V)":12.9267109178}
,"2017-11-16T00:01:00.000Z":
          {"Charge Current, (A)":0.001373312,"Battery Voltage, (V)":12.9267109178}
,"2017-11-16T00:02:00.000Z":
          {"Charge Current, (A)":0.001373312,"Battery Voltage, (V)":12.9267109178}
,"2017-11-16T00:03:00.000Z":
          {"Charge Current, (A)":0.001373312,"Battery Voltage, (V)":12.9267109178}
,"2017-11-16T00:04:00.000Z":
          {"Charge Current, (A)":0.001373312,"Battery Voltage, (V)":12.9267109178}};

I am trying to convert this json into the correct format for a google line chart:

[
          ['Datetime', 'Battery Current, (A)', 'Battery Voltage, (A)'],
          ['2017-11-11 01:00',  1.3,      12.3],
          ['2017-11-11 02:00',  1.4,      12.35],
          ['2017-11-11 03:00',  1.5,      12.6],
          ['2017-11-11 04:00',  1.6,      12.7]
        ]

I can use this code for a single column:

var chartData = [];
Object.keys(jsonData).forEach(function (column) {
  chartData.push(['Datetime', column]);
  Object.keys(jsonData[column]).forEach(function (dateValue) {
    chartData.push([new Date(dateValue), jsonData[column][dateValue]]);
  });
});

But I'm not sure how to amend it for two?

Answer 1

first, build an array of the column headings...

// build chart columns
var chartCols = ['Datetime'];
Object.keys(jsonData).forEach(function (column) {
  chartCols.push(column);
});

then a unique list of the dates...

// build list of date
var dateValues = [];
Object.keys(jsonData).forEach(function (column) {
  Object.keys(jsonData[column]).forEach(function (dateValue) {
    if (dateValues.indexOf(dateValue) === -1) {
      dateValues.push(dateValue);
    }
  });
});

then you can use each date to find the value in the respective column,
if it exists...

// build chart data
var chartData = [chartCols];
dateValues.forEach(function (dateValue) {
  var row = [new Date(dateValue)];
  Object.keys(jsonData).forEach(function (column) {
    row.push(jsonData[column][dateValue] || null);
  });
  chartData.push(row);
});

google.charts.load('current', {
  packages: ['table']
}).then(function () {
  var jsonData = {
    "Battery Voltage, (A)":
      {"2017-11-11T00:00:00.000Z":12.3
      ,"2017-11-11T00:01:00.000Z":12.35
      ,"2017-11-11T00:02:00.000Z":12.6
      ,"2017-11-11T00:03:00.000Z":12.7
      ,"2017-11-11T00:04:00.000Z":12.8}
    ,"Battery Current, (A)":
      {"2017-11-11T00:00:00.000Z":1.3
      ,"2017-11-11T00:01:00.000Z":1.4
      ,"2017-11-11T00:02:00.000Z":1.5
      ,"2017-11-11T00:03:00.000Z":1.6
      ,"2017-11-11T00:04:00.000Z":1.7}
  };

  // build chart columns
  var chartCols = ['Datetime'];
  Object.keys(jsonData).forEach(function (column) {
    chartCols.push(column);
  });

  // build list of date
  var dateValues = [];
  Object.keys(jsonData).forEach(function (column) {
    Object.keys(jsonData[column]).forEach(function (dateValue) {
      if (dateValues.indexOf(dateValue) === -1) {
        dateValues.push(dateValue);
      }
    });
  });

  // build chart data
  var chartData = [chartCols];
  dateValues.forEach(function (dateValue) {
    var row = [new Date(dateValue)];
    Object.keys(jsonData).forEach(function (column) {
      row.push(jsonData[column][dateValue] || null);
    });
    chartData.push(row);
  });

  var table = new google.visualization.Table(document.getElementById('table_div'));
  table.draw(google.visualization.arrayToDataTable(chartData));
});

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="table_div"></div>