Afterquery: the json post-processor and graphing tool

What does it do?

Afterquery is a pure-client-side javascript tool that downloads jsonp-formatted data from a given URL, applies a configurable series of transformations, and then renders the result as either a data table or a Google Visualizations (gviz) chart.

Although the javascript file happens to be hosted on a server, your data never gets uploaded; your browser handles all the processing steps internally. Also, the permissions to download the jsonp data depends on the cookies in your browser, so you can safely retrieve protected content without granting authorization to an external server.

You can then embed it in an iframe to produce easy dashboards like this:

(That's actually a live graph generated by afterquery just now.)

I wrote afterquery because I found that there are lots of cool tools for extracting data from super huge databases (so-called "big data") but those tools can take between a few seconds and a few minutes to run. Once you have a smallish dataset produced by running your "big data" query tools, it would be nice to be able to shrink, summarize, and rotate the "not big data" as efficiently as possible. That's this.

How to use

First, you need to find a service that produces data in "rows and columns" jsonp format. That is, the structure of the object is [row1,row2,...,rown] where each row is [col1,col2,col3,...,coln].

Then you construct a afterquery URL with the jsonp URL you want to retrieve from, followed by the set of transforms you want to apply. You can just type it into your web browser's URL bar while you experiment. Then, once you have the query the way you want it, paste it into a web page inside an <iframe> tag (like the graph above) or just make a hyperlink to a full-screen chart.

The basic structure is:<transform1>&<transform2>&<transform3>...

(The default with no transforms is to just show the data in a handy table view without any changes.)

Available transforms:

Example 1

Here is some raw data (source) produced by an analytics program:

(Tip: in the tables and charts below, click the "Edit" link in the upper-right corner to see how the query works.)

Afterquery can render it as a table like this:

Or pre-sort the table for you like this:

Or filter it by date:

Or summarize the results (like a "group by" in SQL):

Or summarize and display only a subset of columns:

Or do a pivot table (converting rows into columns):

Or filter, then pivot, and then make a chart!

Example 2

Here's another dataset:

We can use two consecutive grouping operations to first get a list of serial numbers for each version, and then get the count of serial numbers per version (link):

Hmm, those version numbers are ugly because some of them have extra debug information after them. Let's trim it out using a regex:

And make a pivot table to easily show the pattern over time:

Trim out some outliers:

And graph it:

Or graph a subset of the data:

Or maybe show the top 4 versions:

Chart gallery

Where can I get a jsonp data source?

There are lots of them out on the web. If your favourite database query or reporting engine doesn't support jsonp, ask them to add it!

Need help? Want to contribute?

Email Probably there'll be a mailing list eventually.

The complete source code is contained in render.js.