How to load JSON file with date to Google Charts

Question

I can't plot data with google charts because of json format data is not properly fitted. I can't figure out how manipulate the data for get the expected format. The data come from a csv file.

I applied file_get_contents() from php, and returns:

[{"Fecha3":1566259200000,"Precio":30.0},
 {"Fecha3":1566345600000,"Precio":6.0},
 {"Fecha3":1566432000000,"Precio":4.0},
 {"Fecha3":1566518400000,"Precio":44.0},
 {"Fecha3":1566777600000,"Precio":80.0},
 {"Fecha3":1566864000000,"Precio":2.0}
] 

(shorted for explanation)

So I convert Unix date to the format that Google Charts needs.

When I applied the php code below, the output from getjson.php file is:

{"new Date(2019, 08, 20)":30,
 "new Date(2019, 08, 21)":6,
 "new Date(2019, 08, 22)":4,
 "new Date(2019, 08, 23)":44,
 "new Date(2019, 08, 26)":80,
 "new Date(2019, 08, 27)":2
} 

(shorted for explanation)

getjson.php

<?php
$fecha = new DateTime();
$strJsonFileContents = file_get_contents("datos_nego2.json");
$arr = json_decode($strJsonFileContents, true);
$result = [];
$i = 0;

// Loop to convert the unix date and sort the data
foreach($arr as $item) { 
    $uses1 = $item['Fecha3']/1000; 
    $uses2 = $item['Precio'];
    $fecha->setTimestamp($uses1);
    $datevar = "new Date(" .$fecha->format('Y, m, d') . ")"; 
    // I think months have to be months - 1
    $result[$datevar] = $uses2;
    $i++;
}

$jsonTable = json_encode($result);
echo $jsonTable;
?>

Then, I load the file is inside the javascript code:

var jsonData = $.ajax({
     url: "getjson.php",
     dataType: "json",
     async: false
}).responseText;

var data = new google.visualization.DataTable(jsonData);

data.addColumn('date', 'Fecha');
data.addColumn('number', 'Number');

var options = {
    [...]
    hAxis: {   // I put fixed dates for testing
      viewWindow: {
        min: new Date(2019, 0, 1),
        max: new Date(2019, 11, 31)
    },
    [...]

Graph is shown but data isn't. Javascript console.log(jsonData) shows:
I've read here that format should be:

{
  "cols": [
        {"id":"","label":"Topping","pattern":"","type":"string"},
        {"id":"","label":"Slices","pattern":"","type":"number"}
      ],
  "rows": [
        {"c":[{"v":"Mushrooms","f":null},{"v":3,"f":null}]},
        {"c":[{"v":"Onions","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Olives","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Zucchini","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Pepperoni","f":null},{"v":2,"f":null}]}
      ]
}

But I don't know how to transform on that way!

Possible solutions I tried but I failed:

• Convert csv original file to a json style as mentioned above.

• Add javascript code below var jsonData to manipulate it.

I've seen in others examples, different solutions:

data.addRows([
[new Date(2019, 1, 1), 0.2],    [new Date(2019, 2, 1), 0.8],
[new Date(2019, 3, 1), 0.4],    [new Date(2019, 4, 1), 0.4],
]);

I also tried but I never achieve to plot data.

I would appreciate any advice or snippet code.

Really thanks for time and consideration! :-)

Answer 1

the solution you found will work, but does require additional processing on the client.

to pass a real date to google, the json format you found must be used.
it is basically the same date string, without the new keyword.
you can find the reference here --> dates and times using the date string representation

{
  "cols": [
        {"label":"Date","type":"date"},
        {"label":"Charge","type":"number"}
      ],
  "rows": [
        {"c":[{"v":"Date(2019, 7, 20)"},{"v":20}]},
        {"c":[{"v":"Date(2019, 7, 21)"},{"v":21}]},
        {"c":[{"v":"Date(2019, 7, 22)"},{"v":22}]},
      ]
}

and yes, the month needs to be reduced by one...

here is an example of the php you could use (not tested)

<?php
$fecha = new DateTime();
$strJsonFileContents = file_get_contents("datos_nego2.json");
$arr = json_decode($strJsonFileContents, true);

// create data table, with columns
$table = [];
$table['cols'] = [
    ['label' => 'Date', 'type' => 'date'],
    ['label' => 'Charge', 'type' => 'number']
];

// Loop to convert the unix date and sort the data
$rows = [];
foreach($arr as $item) {
    $uses1 = $item['Fecha3']/1000;
    $uses2 = $item['Precio'];
    $fecha->setTimestamp($uses1);

    // month does have to be - 1
    $datevar = "Date(".$fecha->format('Y').", ".((int) $fecha->format('m') - 1).", ".$fecha->format('d').")";

    // create rows
    $result = [];
    $result[] = ['v' => (string) $datevar];
    $result[] = ['v' => (float) $uses2];
    $rows[] = ['c' => $result];
}

$table['rows'] = $rows;
$jsonTable = json_encode($table);
echo $jsonTable;
?>

then on the client side, you only need to pass the json to the data table constructor.

var data = new google.visualization.DataTable(jsonData);

you don't need to add columns or rows...

google.charts.load('current', {
  packages: ['corechart']
}).then(function () {

  $.ajax({
    url: 'getjson2.php',
    dataType: 'json'
  }).done(function (jsonData) {

    var data = new google.visualization.DataTable(jsonData);

    var options = {
      hAxis: {
        viewWindow: {
          min: new Date(2019, 0, 1),
          max: new Date(2019, 11, 31)
        }
      }
    };

    var chart = new google.visualization.LineChart(document.getElementById('chart'));

    chart.draw(data, options);

  });
});

note: async: false on ajax has been deprecated and should no longer be used.
use the done callback instead (see above snippet).

Answer 2

I've just found a solution!!!

Data has to be presented like:

[{"Fecha":"2019, 08, 20","Precio":30},{"Fecha":"2019, 08, 21","Precio":6},{"Fecha":"2019, 08, 22","Precio":4},{"Fecha":"2019, 08, 23","Precio":44},{"Fecha":"2019, 08, 26","Precio":80},{"Fecha":"2019, 08, 27","Precio":2},{"Fecha":"2019, 09, 09","Precio":48},{"Fecha":"2019, 09, 10","Precio":2}]

It is done by getjson.php file:

<?php
$fecha = new DateTime();
$strJsonFileContents = file_get_contents("datos_nego2.json");
$arr = json_decode($strJsonFileContents, true);
$result = [];
$i = 0;

foreach($arr as $item) { //foreach element in $arr
    $uses1 = $item['Fecha3']/1000; //etc
    $uses2 = $item['Precio'];
    $fecha->setTimestamp($uses1);
    $datevar = $fecha->format('Y, m, d');
    $result[$i]['Fecha'] = $datevar;
    $result[$i]['Precio'] = $uses2;
    $i++;
}

$jsonTable = json_encode($result);
echo $jsonTable;
//echo $strJsonFileContents;

?>

Then, in the javascript code:

    google.charts.load('current', {'packages':['corechart']});
    google.charts.setOnLoadCallback(drawChart);

    function drawChart() {

      var jsonData = $.ajax({
          url: "getjson2.php",
          dataType: "json",
          async: false
          }).responseText;
      var array_datos = JSON.parse(jsonData)
      var longitud_array = array_datos.length;

      var data = new google.visualization.DataTable();
      data.addColumn('date', 'Date');
      data.addColumn('number', 'Charge');

      for (var i = 0; i < longitud_array; i++) {
          console.log(array_datos[i]);
          data.addRow([
          new Date(array_datos[i].Fecha),
          parseFloat(array_datos[i].Precio),
        ]);
      }