Extracting data from a JSON call to a Postgres table for use in Highcharts (without PHP)

Question

I am using JSON to retrieve data from a Postgres table for use in a Highcharts column chart. I have five time-series in the database, each with five elements, and only want to use two key:value pairs from each time-series in a chart.

The output needs to be in this format:

An array of objects with named values. In this case the objects are point configuration objects as seen below.

Range series values are given by low and high.

Example:

data: [{
          name: 'Point 1',
          color: '#00FF00',
          y: 0
      }, {
          name: 'Point 2',
          color: '#FF00FF',
          y: 5
      }]

Note that line series and derived types like spline and area, require data to be sorted by X because it interpolates mouse coordinates for the tooltip. Column and scatter series, where each point has its own mouse event, does not require sorting.

That's taken from the Highcharts series / data api: http://api.highcharts.com/highcharts#series.data

Here's my db schema:

enable_extension "plpgsql"

create_table "series", force: true do |t|
  t.string   "acronym"
  t.string   "name"
  t.string   "current"
  t.string   "previous"
  t.string   "pc1"
  t.datetime "created_at"
  t.datetime "updated_at"
end
end

Here's the json call:

url = "http://local server 3000/series";
$.getJSON(url, function (series) {
    console.log(series);
    }
    options.series[0].data = series;
    var chart = new Highcharts.Chart(options);

});

console.log shows an array of objects being returned:

[Object, Object, Object, Object, Object]
0: Object
acronym: "1"
current: "3.4"
id: 1
name: "a"
pc1: "25"
previous: "2.4"
url: "http://localhost:3000/series/1.json"
__proto__: Object
1: Object
2: Object
3: Object
4: Object
length: 5
__proto__: Array[0]

I "opened" the first object in order to show how the data is being returned. Notice that the key:value pairs are not followed by a comma (which Highcharts asks for).

In the current chart, a column chart, I only want to display the series "name", e.g., "a", and the value of the corresponding key / value pair "pc1". (They're actually going to be economic time series, e.g., CPI, and its year-over-year percent change.) I plan on creating other charts with, for example, the current and previous values, for a particular time series.

I'm new to programming and do not know how to "extract", if you will, the key:value pairs into the Highcharts format.

I tried pushing the data into a new array with no luck:

var data = [];
data.push(series[0].name + "," series[0].pc1); 

I tried a for loop with no luck:

for (var i = 0, l = series.length; i < l; i++) {
var key = series[i].name;
var value = series[i].pc1;
var data = [ [key + ":" + value], };

In most attempts, I could build an array, but not a nested one of objects and / or one with the comma between x and y values.

I also tried splice(); however, could only remove entire objects.

So, how do I get output that looks like:

data: [{
name: 'a',
pc1: '25',
}, {
name: 'b',
pc1: '15',
}]

I see some very complex code in the answers relating to PHP and MySQL; however, there's nothing with JSON alone (that I could find).

A related question: Am I approaching the problem correctly. Or should I, perhaps, create a new database table for each chart so that only the data I want to display is saved and retrieved.

Thank you in advance.

Answer 1

I would definitely not recommend you create a new database table for each chart. That is not necessary. You can tailor your SQL queries from that table to suit your chart data needs.

One issue is that your y-value is currently a string, which is not what you want in a graphing application. That should be cast to an integer in your SQL query.

The data form is an array of hashes. To initialize the array and add one such element, you could do something like this:

data = [];
data.push({'name': 'a', 'pc1': 25});

To see the data in pretty-printed form, you can verify it in the console with this code:

print(JSON.stringify(data));

So, basically you can build up the array of hashes in the for loop (or via a mechanism such as jQuery's $.each).

That method is essentially hand-rolling your own JSON, though, which is kind of tedious and can be error-prone in some cases. An easier method would probably be to have your backend convert the result of the Postgres query to JSON directly.

Ruby (which I assume is what you're using in the backend based on your schema definition code) has a JSON module (http://www.ruby-doc.org/stdlib-2.0.0/libdoc/json/rdoc/JSON.html) that can do this.

Note that for this to work, your Postgres library will need to return the data as an array of hashes, so it has the column names as well (just an array of arrays will not work). This is doable through libraries such as psycopg2 (Python) and DBI (Perl), and is likely similarly available in the Ruby library you're using also.