Introduction to Querying JSON using JSONata

JSON is the defacto data format for web APIs. Usually getting the data you want out JSON means iterating through the arrays, find if the key exists and then accessing the data. It usually doesn’t stop there if you want to do some kind of aggregation. So its writing loops after loops. But what if we could have a query language which could be used to query the JSON and get to the data directly. Something like SQL or XPath.

JSONata which is an OPen Source project by IBM does exactly that. It's an expression language for querying and transforming JSON data structures. Its simple and elegant.

JSONata is a lightweight query and transformation language for JSON data. Inspired by the ‘location path’ semantics of XPath 3.1, it allows sophisticated queries to be expressed in a compact and intuitive notation.

Getting Started

Using JSconsole

You can use JSONata inside browser or in node environment. In this tutorial we will use it inside the browser environment. For experimentation we can use JSConsole chrome plugin which will give us a REPL environment with the ability to include our favorite javascript libraries. In this case it’s JSONata.

JSConsole is probably best way to experiment with JavaScript

This is my usual way of trying any new JS library as it gives a chance to mix and match different libraries.

Into an HTML Page

If you don’t like to install any plugin then include below script tag in an HTML page and then go to your browser’s Developer Tools -> Console. That will import the library into your browser environment.

<script src="https://rawgit.com/jsonata-js/jsonata/master/jsonata.js"></script>

Using Try JSONata

If you don’t have any patience and at once try JSONata then http://try.jsonata.org/. Everything is setup there for impatient you. Also added bonus is you can share your experiments with others.

Best way to try JSONata

Let’s begin with some data

We need some data to play with. Here is an awesome list of Public APIs. Most of them return JSON. Pick any or use the one on JSONata site. I am going to use BreweryDB API to beers information. You need to register to get a free API key. Or you can use the static data here just for experimentation.

To get all the beers at brewery use the API. Since they don’t support CORS we can’t do direct AJAX calls from the browser. Hence we can use this small php script to proxy. Call it brewery.php

<?php echo file_get_contents("https://api.brewerydb.com/v2/brewery/{$_GET['brewery_id']}/beers?key=YOUR_API_KEY&format=json");

This small php script is an ideal candidate for OpenWhisk server-less implementation. The

<?php
function main($args) {
    if (array_key_exists("'brewery_id'", $args)) {
        $content = file_get_contents("https://api.brewerydb.com/v2/brewery/{$args["'brewery_id'"] }/beers?key=YOUR_API_KEY&format=json");
	return $content. 
}
?>

It’s easy to deploy. You can refer our previous tutorial on OpenWhisk for details. It costs pennies to host on IBM BlueMix. Now you can call it from JQuery.

function process(returned_data){
	// Now process data. For example list all the beers with ibu
	// more than 30
	var query = jsonata(" data[$number(ibu) > 30 ].name");
	//now evaluate
	var result = query.evaluate(returned_data);

	/* result is a json. It returned
[
  "2-pt IPA",
  "Hoppy Red Head",
  "IPA"
]
	*/
}


$.ajax({
  dataType: "json",
  crossDomain: true,
  url: "http://localhost:8080/brewery.php?brewery_id=4VZysG",
  success: process
});

As you can see there are two steps involved in processing data

1. Create the query
2. Execute or Evaluate it over data

Let’s write some more queries

The query is very simple and readable. The learning curve is shallow. Response is always a JSON so further processing is easy. Even though the language is simple it’s very powerful. It supports a lot of functions. They usually start with $. In the above example you saw $number function which converts a string to number (API returns ibu as string which is a very common case). Let’s say we want an average of ibus. We can use $average function.

$average(data.$number(ibu))
//Returns 75

JSONata supports various string and numeric functions similar to standard SQL. There are logical operators, for example below we are checking the ibuMin is within range using ‘and’ , ‘<’, ‘>’ operators. We have also used $number function to convert a string to number

data[$number(style.ibuMin) > 5 and $number(style.ibuMin) < 30 ].name

Custom sorting is also very easy. You can write a function that takes an object from the array, compare the parameters that you want to sort on. In this example I want to sort array of beer styles based on uppdateDates.

$sort(data.style, function($l, $r) {
  $l.updateDate < $r.updateDate
})

JSONata also supports higher order functions like Map/Reduce if you like to do any special aggregation. For example if you want to do something on each beer category
then, you can iterate over the object and apply an anonymous function

$map(data.style.category, function($v) {    
    $v
})

Similarly you can use reduce, filter, sift functions to process the data by applying your own functions in various ways.

I have the data and query here for you to try on a scratch pad. It can be your starting point. JSONata is a great tool. It’s like jq inside your browser. A swiss army knife for processing JSON inside your browser or in node environment. Comment if you have any questions.