# Mapping Google Datatable JSON format to Sencha GXT ListStores for charting

By now, if you’ve been keeping up with my blog, you’ll notice that we make extensive use of charting in our application. We decided to go with Sencha GXT charting because we were under time pressure to finish our product for a release. However, we weren’t so crunched for time that I decided to model the data in an unintelligent way. Since charts essentially graphically display tabular data, I wanted to ensure that if we were to move to a different graphing package (which we are, btw) in the future, our data was set up for easy consumption.

After tooling around a bit, I decided on google datatable JSON formatting for our response data from queries made to our server. Google datatable formatting essentially just mimics tabular data in JSON formatting. It’s also easily consumable by google or angular charts. Having used angular charts in the past, I knew that the data format would map nicely to charts. However, the immediate problem still loomed, which is how to convert from google datatable JSON format to consumable ListStores for consumption by GXT charts?

Solution: In the end, I ended up writing an adapter with specific implementations for consuming our backend charting data. I needed to write a Sencha/GXT specific one. Here’s an example of the data we receive from our REST query.

```//Note: some data omitted for readability
{
"offset": 0,
"totalCount": 1,
"items": [
{
"title": "Registration Summary",
"data": {
"cols": [
{
"id": "NotRegistered",
"label": "Not Registered",
"type": "number"
},
{
"id": "Registered",
"label": "Registered",
"type": "number"
}
],
"rows": [
{
"l": "9608",
"c": [
{
"v": "5",
"f": null
},
{
"v": "4",
"f": null
}
]
},
{
"l": "9641",
"c": [
{
"v": "2",
"f": null
},
{
"v": "5",
"f": null
}
]
},
{
"l": "9650SIP",
"c": [
{
"v": "3",
"f": null
},
{
"v": "0",
"f": null
}
]
}
]
}
}
]
}
```

And here’s an example of how it’s converted into a Sencha ListStore:

```[
{
v1=4,
label0=NotRegistered,
v0=5,
label1=Registered,
id1=Registered,
type1=number,
id0=NotRegistered,
type0=number,
f1=null,
l=9608,
f0=null
},
{
v1=5,
label0=NotRegistered,
v0=2,
label1=Registered,
id1=Registered,
type1=number,
id0=NotRegistered,
type0=number,
f1=null,
l=9641,
f0=null
},
{
v1=0,
label0=NotRegistered,
v0=3,
label1=Registered,
id1=Registered,
type1=number,
id0=NotRegistered,
type0=number,
f1=null,
l=9650SIP,
f0=null
}
...
]
```

Step 1: Define the ChartModel I/F to map to the JSON datatable format.

Here’s an example of a ChartModel java object that maps to JSON object.

```public abstract class ChartModel {

private static ChartFactory factory = GWT.create(ChartFactory.class);
private static JsonListReader<ChartObjects, ChartObject> reader = new JsonListReader<ChartObjects, ChartObject>(factory, ChartObjects.class );

/**
* These constants are fixed per the value retrieved from JSON
*/
public static final String POINT = "point";
public static final String COL_LABEL = "label";
public static final String COL_TYPE = "type";
public static final String COL_ID = "id";
public static final String CELL_VALUE = "v";
public static final String CELL_FORMATTED_VALUE = "f";
public static final String ROW_LABEL = "l";

/*
* Define the data model
*/

public interface ChartObjects extends ModelList<ChartObject>{}

public interface ChartObject {
String getTitle();
ChartData getData();
}

public interface ChartData {
List<ChartCol> getCols();	//represents # series
List<ChartRow> getRows();	//an array of array of cells, i.e. all the data
}

public interface ChartCol {
String getId();
String getLabel();
String getType();
}

public interface ChartRow {
List<ChartCell> getC();	//an array of chart cells, one per column
String getL();	//label for this group of chart cells (e.g. a timestamp)
}

public interface ChartCell {
String getV();	//get value
String getF();	//get formatted value
}

public abstract ListStore<DataPoint> getListStore(ChartObject chartObject) throws Exception;
...
}
```

You can see that I also added an abstract getListStore method that requires any consumer of the ChartObject to define how the ChartObject gets modeled to the ListStore.

Step 2. Create an Adapter that extends the ChartModel and implements the function getListStore.

Here’s an example of a StackedBarChart model (the models turned out to be different for multiple series in a stacked bar chart and for example, a pie chart. Line charts and stacked bar charts turned out to be the same.

```
public class StackedBarChartModel extends ChartModel {
@Override
public ListStore<DataPoint> getListStore(ChartObject chartObject) throws Exception {
}
}
```

You can see that this method instantiates a new adapter instance for GXT charts, passes in the chart object (modeled to the datatable JSON format) and returns the appropriate chart series (in this case a BarSeries GXT object).

Step 3. Map the chart object to a ListStore.

```public class GXTChartModelAdapterImpl implements GXTChartModelAdapter {

@Override
public ListStore<DataPoint> getSeries(ChartType type) throws Exception {
switch(type) {
case LINE:
return getLineSeriesListStore();
case STACKED_BAR:
return getStackedBarSeriesListStore();
case BAR:
return getLineSeriesListStore();
case PIE:
return getPieSeriesListStore();
default:
throw new Exception("Unknown data series type.");
}
}

/**
* Returns a ListStore for a BarSeries GXT object to add to a chart
* @return
*/
private ListStore<DataPoint> getStackedBarSeriesListStore() {
ListStore<DataPoint> _listStore = new ListStore<DataPoint>(new ModelKeyProvider<DataPoint>() {
@Override
public String getKey(DataPoint item) {
return String.valueOf(item.getKey());
}
});

int numPoints = this.getNumPoints();  //The number of rows in the rows obj in ChartObject
int numSeries = this.getNumSeries();  //The number of rows in the col obj in ChartObject

for (int i = 0; i < numPoints; i++) {
//This key must be unique per DataPoint in the store
DataPoint d = new DataPoint(indexStr(ChartModel.POINT,Random.nextInt()));

for (int index = 0; index < numSeries; index++) {
d.put(indexStr(ChartModel.COL_LABEL, index), chartObject.getData().getCols().get(index).getLabel());
d.put(indexStr(ChartModel.COL_TYPE, index), chartObject.getData().getCols().get(index).getType());
d.put(indexStr(ChartModel.COL_ID, index), chartObject.getData().getCols().get(index).getId());
}

ChartRow row = chartObject.getData().getRows().get(i);	//get the i-th point
d.put(ChartModel.ROW_LABEL, row.getL());
for (int j = 0; j < numSeries; j++) {
if (row.getC().get(j) != null) {	//if ith-point is not blank
d.put(indexStr(ChartModel.CELL_VALUE, j), row.getC().get(j).getV());
d.put(indexStr(ChartModel.CELL_FORMATTED_VALUE, j), row.getC().get(j).getF());
} else {	//otherwise, assume 0 value
d.put(indexStr(ChartModel.CELL_VALUE, j), "0");
d.put(indexStr(ChartModel.CELL_FORMATTED_VALUE, j), "");
}
}
}

return _listStore;
}
...
}
```

A few things to note here.

• A DataPoint is basically just a map of keys and value pairs. Since the DataPoint object is specific to the ListStore, the implementation is in the interface GXTChartModelAdapter. To do this, I used the instructions here: https://www.sencha.com/blog/building-gxt-charts/
• Next, you can see that for each bar I have (not each stack), I create a new DataPoint object that contains a the corresponding value of the specific row in the ChartObject.rows object. I know that each column corresponds to a stacked bar, so I increment the “v” value by 1 and use this as the key / value pair to the DataPoint.
• Finally, because the number of columns defines the number of stacks I expect in each bar, if there isn’t a value (because our backend didn’t provide one), I assume the value is 0. This enforces that the v(n) in the first bar will correspond to v(n) in any subsequent bars.
• I added an “l” value to each row in the ChartObject.rows obj which corresponds to a label for the entire stacked bar. I know that in the datatables JSON format you can specify the first row in each row obj to be a String and correspond to the label for each bar. However, it’s harder to enforce this type of thing in java and we expected numeric data back each time.

Step 4. Pass in the data into a Sencha GXT Chart!

Now the easy part. Essentially the data is transformed from the datatables format to a ListStore with each DataPoint key: v0 – vN corresponding to a stack in a bar.

Thus when instantiating the chart, this is all I had to do:

```private BarSeries<DataPoint> createStackedBar() {

BarSeries<DataPoint> series = new BarSeries<DataPoint>();
series.setYAxisPosition(Position.LEFT);
series.setColumn(true);
series.setStacked(true);

//numSeries is the number of bars you want stacked, it conforms to the chartObject.cols().length
for (int i = 0; i < numSeries; i++) {
MapValueProvider valueProvider = new MapValueProvider(ChartModel.CELL_VALUE + i);
{"ft_lang_label":"__label__en","ft_lang_prob":0.603159,"math_prob":0.81014013,"size":9045,"snap":"2022-40-2023-06","text_gpt3_token_len":2253,"char_repetition_ratio":0.14334698,"word_repetition_ratio":0.023754789,"special_character_ratio":0.25605306,"punctuation_ratio":0.19235365,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9800848,"pos_list":[0,1,2],"im_url_duplicate_count":[null,7,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2022-10-01T02:42:11Z\",\"WARC-Record-ID\":\"<urn:uuid:261edb41-ad23-4db8-ad14-11be2afececb>\",\"Content-Length\":\"112773\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:f9fc6e10-56e5-4b02-af9f-f61562d5b22d>\",\"WARC-Concurrent-To\":\"<urn:uuid:3d1a0777-d679-4037-885d-00e93983df34>\",\"WARC-IP-Address\":\"192.0.78.25\",\"WARC-Target-URI\":\"https://codeandcodes.com/2014/04/10/mapping-google-datatable-json-format-to-sencha-gxt-liststores-for-charting/\",\"WARC-Payload-Digest\":\"sha1:2MSNGAPBUE2AWE2FI3FTFFDYCUOQKQDE\",\"WARC-Block-Digest\":\"sha1:O5II5VRFRMHOCHL3AE4FBTLBE6NR23XD\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2022/CC-MAIN-2022-40/CC-MAIN-2022-40_segments_1664030335514.65_warc_CC-MAIN-20221001003954-20221001033954-00123.warc.gz\"}"}