JSON format for Google chart stacked column

Question

I have data like below:

           store 1   Store 2
store_id   walk-ins  walk-ins
morning      20        25
noon         35        40
night        50        55

There are 20 stores to chart stacking the values of each row.

Google Charts docs tells me the data array looks like this:

var data = google.visualization.arrayToDataTable([
        ['Stores', 'Store 1', 'Store 2', 'Store 3', 'Store 4', ... ],
        ['morning', 10, 24, 20, 32, 18, 5, ...],
        ['noon', 16, 22, 23, 30, 16, 9, ...],
        ['night', 28, 19, 29, 30, 12, 13, ...],
      ]);

I am getting the data via MySQL script / server PHP script. What should the JSON look like? The json_encode($data) from MySQL query returns as follows;

[{"store_name":"Store 1","Time":"Morning","count":"17"}, ...]

but the chart does not load and gives me a message "Table has no columns".

I load JSON as follows:

var url =  '/url/updatedata.php?var=querytype';
    jQuery.getJSON( url, function(Json) {

    // Create and populate the data table.
    var data = new google.visualization.DataTable(Json);
....

What is the structure of the JSON for a stacked column chart?

Thanks!

Answer 1

It turned out that mapping of your data happened to be a quite complex task.

For example, you have this json data:

var json = [
    {"store_name":"Store 1","Time":"Morning","count":"10"}, 
    {"store_name":"Store 1","Time":"Noon","count":"16"},  
    {"store_name":"Store 1","Time":"Night","count":"28"},    
    {"store_name":"Store 2","Time":"Morning","count":"24"}, 
    {"store_name":"Store 2","Time":"Noon","count":"22"},  
    {"store_name":"Store 2","Time":"Night","count":"19"}
];

I use underscore.js for the following code. It can be included like this:

<script type="text/javascript" src="http://underscorejs.org/underscore-min.js"></script>

Mapping code:

var header = _.chain(json).pluck("store_name").sort().uniq(true).value();
header.unshift("Stores");

var rows = _.chain(json)
.groupBy(function(item) { return item.Time; })
.map(function(group, key) { 
    var result = [key];
    _.each(group, function(item) { 
        result[_.indexOf(header, item.store_name)] = parseInt(item.count); 
    });
    return result; 
})
.value();

var jsonData = [header].concat(rows);

// draw chart
var data = google.visualization.arrayToDataTable(jsonData);
chart.draw(data, options);

The final jsonData variable looks so:

[["Stores", "Store 1", "Store 2"], 
["Morning", 10, 24], 
["Noon", 16, 22], 
["Night", 28, 19]]

Here is a link to jsFiddle with your chart.

Answer 2

I know this is an old post, but in case if any one needs it, here is the format for Jsondata for using google's stacked column from server code.

{
  "cols": [
    {
      "id": "",
      "label": "title",
      "pattern": "",
      "type": "string"
    },
    {
      "id": "",
      "label": "A",
      "pattern": "",
      "type": "number"
    },
    {
      "type": "string",
      "role": "annotation",
      "p": {
        "role": "annotation"
      }
    },
    {
      "id": "",
      "label": "B",
      "pattern": "",
      "type": "number"
    },
    {
      "type": "string",
      "role": "annotation",
      "p": {
        "role": "annotation"
      }
    },
    {
      "id": "",
      "label": "C",
      "pattern": "",
      "type": "number"
    },
    {
      "type": "string",
      "role": "annotation",
      "p": {
        "role": "annotation"
      }
    },
    {
      "id": "",
      "label": "D",
      "pattern": "",
      "type": "number"
    },
    {
      "type": "string",
      "role": "annotation",
      "p": {
        "role": "annotation"
      }
    }
  ],
  "rows": [
    {
      "c": [
        {
          "v": "Categories",
          "f": null
        },
        {
          "v": "10",
          "f": null
        },
        {
          "v": "10",
          "f": null
        },
        {
          "v": "20",
          "f": null
        },
        {
          "v": "20",
          "f": null
        },
        {
          "v": "30",
          "f": null
        },
        {
          "v": "30",
          "f": null
        },
        {
          "v": "50",
          "f": null
        },
        {
          "v": "50",
          "f": null
        }
      ]
    }
  ]
}