• Create and manage data views • Rapidly build data-driven interfaces
About
Tabular data is often represented in JavaScript as an array of arrays. Each index of the outer array corresponds to a row in the table, which itself is an array of the column values. Typically this data originates from a database and some function exists to arrange it in this manner at run time. Many circumstances dictate the filtering or re-ordering of this data in response to some event on the page. Usually this requires sending new parameters to the database server so the processing can be done in SQL and then returned back to the page.

While SQL is ideal for processing large datasets, the time spent sending information back and forth can exceed any performance benefits over client-side processing in JavaScript for small to medium datasets. In this situation we want to query our data directly in JavaScript but still employ concise and easy-to-use SQL syntax. JSource is built for just this purpose. It has no external dependencies and works cross-browser.
Reference files
Include references to jsource.css and JSource.js
 
<link rel="stylesheet" href="jsource.css" type="text/css" media="all" />

<script type="text/javascript" src="JSource.js"></script>
Collect Data
Declare an array of column names.

Gather the tabular data into an array of arrays.

Each inner array represents a table row where the column order matches the array of column names. 
var names = ["Region","City","Season","Particulate","Avg_Temp","Pollen"];

var data = [
  [ "East", "Boston", "Winter", 290, 30, 130 ],
  [ "East", "Boston", "Summer", 230, 60, 540 ],
  [ "East", "New York", "Winter", 300, 20, 100 ],
  [ "East", "New York", "Summer", 240, 70, 430 ],
  [ "East", "Philadelphia", "Winter", 280, 25, 120 ],
  [ "East", "Philadelphia", "Summer", 220, 75, 440 ],
  [ "South", "Miami", "Winter", 260, 65, 160 ],
  [ "South", "Miami", "Summer", 200, 85, 360 ],
  [ "South", "Atlanta", "Winter", 310, 50, 160 ],
  [ "South", "Atlanta", "Summer", 240, 90, 410 ],
  [ "West", "Seattle", "Winter", 250, 30, 130 ],
  [ "West", "Seattle", "Summer", 210, 70, 510 ]
];
Instantiate JSource
Create a new JSource instance, passing in column names and data.

Optionally, the 'printTable' method may be called at any time to print an HTML table representation of the current data to a DOM element with the supplied ID.

You are now ready to perform SQL-like operations on the original dataset. 
var view = new JSource( names, data );

view.printTable( { canvas:"viewDiv" } );
Process Data
Use 'select', 'where', 'groupBy', or 'orderBy' methods to process data.

Each of these methods returns a new JSource object which allows them to be used separately or chained together. You can create JSource views on top of JSource views.

The 'groupBy' method takes an additional argument which is an array of objects specifying the aggregation rules for each measure column. If an aggregation rule is not supplied the default is 'sum'.

Available aggregation rules are 'sum', 'max', 'min', 'avg', and 'count'. 
var newView = view
  .select( "Region, Season, City, Pollen, Particulate" )
  .where( "Season = Winter and ( City = 'New York' or Pollen > 130 )" )
  .orderBy( "Pollen DESC" );

newView.printTable( { canvas:"newViewDiv" } );



var newView2 = newView
  .select( "Region, Pollen, Particulate" )
  .groupBy( "Region", [ 
	  { column: "Pollen", agg: "avg" }, 
	  { column: "Particulate", agg: "avg" } 
  ]);
	
newView2.printTable( { canvas:"newView2Div" } );
Access New Data
Column names are accessible in the JSource.columns property.

Data is accessible in the JSource.data property. 
console.log( newView.columns );
// outputs Region,Season,City,Pollen,Particulate
					
console.log( newView.data[0] );
// outputs South,Winter,Miami,160,260
					
console.log( newView.data[1] );
// outputs South,Winter,Atlanta,160,310
					
console.log( newView.data[2] );
// outputs East,Winter,New York,100,300
Form Controls
The 'addControl' method may be used to attach HTML form elements to a JSource object. These controls do not affect the original JSource object. The resultant JSource object is available in the callback function.

The 'addControl' method accepts an object literal as an argument:

var opts = { 
 column:'col_name',
 canvas:'dom_id',
 grouping:true,
 callback:myFunction
};

Where 'column' is the column to be filtered, 'canvas' is the ID of the HTML container for the form element, 'grouping' adds and/or and grouping elements, and 'callback' is an optional function to be called when the form element is changed.

The callback is where we want to capture the results and do something with them. Within the callback function, 'this' will refer to the JSource object resulting from the filter values.

Table sorting can be enabled by passing 'true' as the second argument to the 'printTable' method. A callback may also be supplied to execute after each sorting action. 
function callback(){
  this.printTable( { canvas:"exViewDiv", sortable:true } );
  document.getElementById( "rowCount" ).innerHTML = this.data.length + " rows";   
}

view.printTable( { canvas:"exViewDiv", sortable:true, callback:callback } );

view.addControls( 
  { column:"Region", canvas:"exViewControls", grouping:true, callback:callback },
  { column:"City", canvas:"exViewControls", grouping:true, callback:callback },
  { column:"Season", canvas:"exViewControls", grouping:true, callback:callback } 
);
Get Creative
Here we combine JSource with the Google Chart API to create a simple dashboard.

We'll create some Form Controls to filter the data, use the table-sorting option to order the data, and add a select list to control which column to group the x-axis values by.

The variable 'chartView' will hold our master dataset for the dashboard.

The Google API has a 'setOnLoadCallback' method which will call the createChart function after the library has loaded.

We'll also need a function to group the data by the column in the Group By select list and another function to merge the JSource column and data arrays into a single array as the Google API requires.

Updates will be performed in the callbacks for the Form Controls and table-sorting methods.

In the Form Controls callback we need to make sure the sort order of the chart data matches with the table since the table-sorting method has not modified the chartView to which the Form Controls are attached.

The 'getActiveSort' function will return an array with the index of the sorted column in the table and the sort order (asc/desc).

For simplicity we can use the onchange method attached to the first form control to update everything after changing the Group By select list.

More elaborate examples:
   Demo 1 (757 rows)
   Demo 2 (1898 rows)
Group By 


var chartView = view.select( "Region, City, Season, Particulate, Pollen" );
var x = document.getElementById( "xSelect" );
var chart;
var opts = {           
  title: "Air Quality",
  titleTextStyle: { fontSize:16, bold:false },
  legend: { position:"top", alignment:"end" },
  chartArea: { left:40, width:"90%" },
  colors: [ "#337B9E", "#A5BC4E" ],
  animation:{   
    duration: 1000,
    easing: "out"
  }
};

google.load("visualization", "1", {packages:["corechart"]});       
google.setOnLoadCallback(createChart);

function createChart() {   
  var target = document.getElementById( "chartViewDiv" );
  chart = new google.visualization.ColumnChart( target );         
  chart.draw( mergeData( groupView( chartView ) ), opts );       
  x.onchange();
}

function groupView(src){
  var col = x.value;
  var newView = src
    .select( col + ", Particulate, Pollen" )
    .groupBy( col, [ 
      { column : "Particulate", agg : "avg" }, 
      { column : "Pollen", agg : "avg" } 
    ] );
  return newView;
}

function mergeData(src){
  var d = src.data.slice();
  d.splice( 0, 0, src.columns );
  var data = google.visualization.arrayToDataTable( d );
  return data;
}   

function controlCallback(){
  chart.clearChart();
  groupView(this).printTable({ 
    canvas:"chartViewTable", 
    sortable:true, 
    callback:sortCallback 
  });
  if ( this.data.length ){
    var srt = this.getActiveSort( "chartViewTable" );
    var th = document.getElementById( "chartViewTable" ).getElementsByTagName( "th" );
    var s = th[ srt[0] ];
    var col = ( typeof s.innerText == "undefined" ) ? s.textContent : s.innerText;
    var c = col.substring( 0, col.length - 1 );
    var r = groupView( this ).orderBy( c + " " + srt[1] );      
    chart.draw( mergeData( r ), opts );
  }
}

function sortCallback(){
  chart.clearChart();
  chart.draw( mergeData( this ), opts );
}

chartView.addControls( 
  { column:"Region", canvas:"chartViewControls", grouping:true, callback:controlCallback },
  { column:"City", canvas:"chartViewControls", grouping:true, callback:controlCallback },
  { column:"Season", canvas:"chartViewControls", grouping:true, callback:controlCallback } 
);

x.onchange = function(){
  var cntrls = document.getElementById( "chartViewControls" );
  cntrls.getElementsByTagName( "select" )[0].onchange();
}