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!

You should map your json array to such data table yourself by using the `map` function like `Json.map(function(item) { return ['morning', 10, 16, 28]; })` — vortexwolf, Apr 18 '14 at 13:11
Also read documentation how the `google.visualization.DataTable` constructor should be used: https://developers.google.com/chart/interactive/docs/reference — vortexwolf, Apr 18 '14 at 13:13

score 5 · Answer 1 · answered Nov 04 '15 at 15:11

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
        }
      ]
    }
  ]
}

can i know the full form of "p","v","f"? – Aravindh Gopi Oct 08 '16 at 08:06 — Aravindh Gopi, Oct 08 '16 at 08:06

score 2 · Accepted Answer · answered Apr 18 '14 at 14:29

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.

Good, eventhough it have been better to format the JSON from PHP since OP query the db. — meda, Apr 18 '14 at 18:14
Hi vortex, you rock!! But I am looking for the data charted by store not by time. I will play with your fiddle to achieve this. MANY THANKS! — TheRealPapa, Apr 18 '14 at 21:04
@TheRealPapa What you want to do is called `transpose of a matrix`, a simple algorithm. Or you can replace in my code all occurences of store_name by Time and accordingly Time by store_name. — vortexwolf, Apr 19 '14 at 09:23
Hi @vortex, I did already so THANK YOU! You really helped out (twice!). I have another q here if you want to make it a "hat trick" (Aussie thing!). I am now trying to get google.visualization.Table working! http://stackoverflow.com/questions/23168129/google-arraytodatatable-invalid-row-type-for-row-0. THANKS!!! — TheRealPapa, Apr 19 '14 at 09:26

JSON format for Google chart stacked column

2 Answers2

Linked