JSONata is a declarative functional language for querying and transforming JSON data. It was inspired by the path semantics of XPath. Whereas other languages that are inspired by XPath also incorporate SQL-like elements1, JSONata stays true to its XPath roots and augments the path semantics of XPath with additional features to increase its power.
JSONata is powerful but it can take a while to get accustomed to its terse syntax. In this article I’ll discuss three aspects of JSONata that will help you understand some of the subtleties of the language.
- Sequences and Path Operators
- Object Constructors
- Contexts and Context Variable Binding
Sequences and Path Operators
Since XPath 2.0, every value in XPath is a sequence of zero or more items. An atomic value, such as a boolean, number, or string, is just a special case of a sequence of length one. JSONata uses the same data model as XPath.
Since JSONata is a functional language, and its main data construct is a sequence, one would expect the well-known map, filter, and reduce functional operators to be available for sequences. This is indeed the case, but these operators often hide in plain sight.
For example, with the JSON below, assume we want to find the number of products in each category that are priced greater than 5.
{ "products": [ { "name": "broiler", "category": "kitchen", "price": 100, "cost": 70 }, { "name": "toaster", "category": "kitchen", "price": 30, "cost": 10 }, { "name": "blender", "category": "kitchen", "price": 50, "cost": 25 }, { "name": "socks", "category": "clothes", "price": 5, "cost": 2 }, { "name": "shirt", "category": "clothes", "price": 10, "cost": 3 } ] }
Here is a JSONata query that will return the desired results.
$.products[price > 5]{category: $count(name)}
{ "kitchen": 3, "clothes": 1 }
You can see this JSONata query in action here.
Let’s break down the above query into four elements:
$
.products
[price > 5]
{category: $count(name)}
The first element, $
, is a built-in variable. When at the beginning of a JSONata expression, it represents the entire input document.
The second element, .products
, is composed of the map operator (.
) along with a function to be applied to each element of the input sequence. In this case the function is the products
path expression, and the input sequence is the entire document.
The third element, [price > 5]
, is the filter operator ([...]
) along with a predicate expression to determine which elements of the input sequence should be returned.
The fourth element, {category: $count(name)}
, is the reduce operator ({...}
) along with an object to hold the groupings, determined by the key expressions, and the aggregated values.
For those familiar with functional programming, being able to parse JSONata path expressions as compositions of the usual functional operators will help you understand the results that are returned.
Object Constructors
One of the features of JSONata that might initially be confusing to those first using it are the object constructors. There are two types of object constructors: one produces a single object and the other produces an array of objects. We’ve seen the first one in the previous section:
$.products[price > 5]{category: $count(name)}
{ "kitchen": 3, "clothes": 1 }
The second one looks similar:
$.products[price > 5].{category: $count(name)}
[ { "kitchen": 1 }, { "kitchen": 1 }, { "kitchen": 1 }, { "clothes": 1 } ]
As can be seen, one object constructor is of the form pathExpr{...}
while the other is of the form pathExpr.{...}
. Note the dot (.
) between the path expression and the object expression.
Now that we’ve learned to parse these expressions as compositions of functional operators, we can make sense of the resulting output. The first object constructor is simply the reduce operator ({...}
), as we’ve discussed previously, while the second object constructor is the map operator (.
) followed by a function that returns an object for each element of the input sequence. This explains why the output of the second object constructor is an array.
Contexts and Context Variable Binding
Previously we explained that $
is a built-in variable that represents the input document when at the beginning of a JSONata expression. In general, the variable $
refers to the context item, which is the sequence currently being evaluated.
The context item is implied at the beginning of a path expression, but can also be explicitly referenced. For example, the following two queries are equivalent:
products[price > 5]{category: $count(name)}
$.products[$.price > 5]{$.category: $count($.name)}
After applying the map (.
) operator, the context item changes. However, there is one exception, and that is when using a context variable binding. The context variable binding feature is unique to JSONata, and was introduced to support join semantics in a path expression. We show an example below.
Assume our data looks as follows:
{ "sales": [ { "product": "broiler", "store number": 1, "quantity": 20 }, { "product": "toaster", "store number": 2, "quantity": 100 }, { "product": "toaster", "store number": 2, "quantity": 50 }, { "product": "toaster", "store number": 3, "quantity": 50 }, { "product": "blender", "store number": 3, "quantity": 100 }, { "product": "blender", "store number": 3, "quantity": 150 }, { "product": "socks", "store number": 1, "quantity": 500 }, { "product": "socks", "store number": 2, "quantity": 10 }, { "product": "shirt", "store number": 3, "quantity": 10 } ], "stores": [ { "store number": 1, "state": "CA" }, { "store number": 2, "state": "CA" }, { "store number": 3, "state": "MA" }, { "store number": 4, "state": "MA" } ] }
We want to join the sales data with the store data to find out how many products were sold in each state. Here’s the JSONata query to achieve this, which you can also see here.
$.stores@$st.sales@$sl[$sl.`store number` = $st.`store number`]{ $st.state: $sum($sl.quantity) }
{ "CA": 680, "MA": 310 }
At the beginning of our JSONata expression, the context item is the entire input document. We use the map operator followed by a path expression with a context variable binding (.stores@$st
) to bind the variable $st
to the result of mapping the stores
path expression to the input document. Since we use a context variable binding, the context item does not change to the result of the stores
path expression, but instead remains as the input document. The next map operator followed by a path expression (.sales$sl
) is again applied to the input document as the context item.
After the $st
and $sl
variables are bound to the result of the stores
and sales
path expressions, respectively, the filter expression [$sl.`store number` = $st.`store number`]
performs the join, and the reduce expression { $st.state: $sum($sl.quantity) }
aggregates the results.
As demonstrated, the reason that the context item does not change when using a context variable binding is to more easily support joins between sibling nodes in the JSON document.
Sample Queries
The rest of this article shows various sample queries using JSONata. These examples are adapted from an enumeration of various use cases for a different JSON transformation language that was also inspired by XPath. You can see these examples implemented in the alternative language here. The JSONata examples tend to be more concise.
Join
The JSON below is based on a social media site that allows users to interact with their friends.
[ { "name": "Sarah", "age": 13, "gender": "female", "friends": [ "Jim", "Mary", "Jennifer" ] }, { "name": "Jim", "age": 13, "gender": "male", "friends": [ "Sarah" ] } ]
The following query performs a join on Sarah’s friend list to return the object representing each of her friends:
$[name in $$[name = "Sarah"].friends]
{ "name": "Jim", "age": 13, "gender": "male", "friends": [ "Sarah" ] }
See https://try.jsonata.org/1A5n67svD.
Grouping Queries for JSON
The JSON below contains data about sales, products, and stores.
{ "sales": [ { "product": "broiler", "store number": 1, "quantity": 20 }, { "product": "toaster", "store number": 2, "quantity": 100 }, { "product": "toaster", "store number": 2, "quantity": 50 }, { "product": "toaster", "store number": 3, "quantity": 50 }, { "product": "blender", "store number": 3, "quantity": 100 }, { "product": "blender", "store number": 3, "quantity": 150 }, { "product": "socks", "store number": 1, "quantity": 500 }, { "product": "socks", "store number": 2, "quantity": 10 }, { "product": "shirt", "store number": 3, "quantity": 10 } ], "products": [ { "name": "broiler", "category": "kitchen", "price": 100, "cost": 70 }, { "name": "toaster", "category": "kitchen", "price": 30, "cost": 10 }, { "name": "blender", "category": "kitchen", "price": 50, "cost": 25 }, { "name": "socks", "category": "clothes", "price": 5, "cost": 2 }, { "name": "shirt", "category": "clothes", "price": 10, "cost": 3 } ], "stores": [ { "store number": 1, "state": "CA" }, { "store number": 2, "state": "CA" }, { "store number": 3, "state": "MA" }, { "store number": 4, "state": "MA" } ] }
We want to group sales by product, across stores.
sales{ product: [$sum(quantity)]}
{ "broiler": 20, "toaster": 200, "blender":250, "socks": 510, "shirt": 10 }
See https://try.jsonata.org/cGRl7Xi1T.
Now let’s do a more complex grouping query, showing sales by category within each state. The following query groups by state, then by category, then lists individual products and the sales associated with each.
( /* First join all the rows */ $join := stores@$st .sales@$sl[$sl.`store number` = $st.`store number`] .products@$p[$sl.product = $p.name].{ "state": $st.state, "category": $p.category, "product": $sl.product, "quantity": $sl.quantity }; /* Next perform the grouping */ $join{state: {category: {product: $sum(quantity)}}} )
{ "CA": { "kitchen": { "broiler": 20, "toaster": 150 }, "clothes": { "socks": 510 } }, "MA": { "kitchen": { "toaster": 50, "blender": 250 }, "clothes": { "shirt": 10 } } }
See https://try.jsonata.org/-0LDeg4hI.
JSON to JSON Transformations
The following query takes satellite data, and summarizes which satellites are visible. The data for the query is a simplified version of a Stellarium file that contains this information.
{ "creator": "Satellites plugin version 0.6.4", "satellites": { "AAU CUBESAT": { "tle1": "1 27846U 03031G 10322.04074654 .00000056 00000-0 45693-4 0 8768", "visible": false }, "AJISAI (EGS)": { "tle1": "1 16908U 86061A 10321.84797408 -.00000083 00000-0 10000-3 0 3696", "visible": true }, "AKARI (ASTRO-F)": { "tle1": "1 28939U 06005A 10321.96319841 .00000176 00000-0 48808-4 0 4294", "visible": true } } }
We want to query this data to return a summary. The following is a JSONata query that returns the desired result.
( $sats := $each(satellites, function($v, $k) {$merge([{"name": $k}, $v])}); { "visible": [$sats[visible].name], "invisible": [$sats[$not(visible)].name] } )
{ "visible": [ "AJISAI (EGS)", "AKARI (ASTRO-F)" ], "invisible": [ "AAU CUBESAT" ] }
See https://try.jsonata.org/45R_q6dVJ.
JSON Updates
Suppose an application receives an order that contains a credit card number, and needs to put the user on probation.
Data for the users:
[ { "name": "Deadbeat Jim", "address": "1 E 161st St, Bronx, NY 10451", "risk tolerance": "high" }, { "name": "Rich Jim", "address": "123 Main St, Azusa, CA 91010", "risk tolerance": "low" } ]
The following query adds "status": "credit card declined"
to the user’s record.
[$.$merge([$, name = "Deadbeat Jim" ? {"status": "credit card declined"} : {}])]
After the update is finished, the user’s record looks like this:
[ { "name": "Deadbeat Jim", "address": "1 E 161st St, Bronx, NY 10451", "risk tolerance": "high", "status": "credit card declined" }, { "name": "Rich Jim", "address": "123 Main St, Azusa, CA 91010", "risk tolerance": "low" } ]
See https://try.jsonata.org/btnJJFsBV.
Data Transformations
Many applications need to modify data before forwarding it to another source. Suppose an application make videos available using feeds from Youtube. The following data comes from one such feed:
{ "encoding": "UTF-8", "feed": { "author": [ { "name": { "$t": "YouTube" }, "uri": { "$t": "http://www.youtube.com/" } } ], "category": [ { "scheme": "http://schemas.google.com/g/2005#kind", "term": "http://gdata.youtube.com/schemas/2007#video" } ], "entry": [ { "app$control": { "yt$state": { "$t": "Syndication of this video was restricted by its owner.", "name": "restricted", "reasonCode": "limitedSyndication" } }, "author": [ { "name": { "$t": "beyonceVEVO" }, "uri": { "$t": "http://gdata.youtube.com/feeds/api/users/beyoncevevo" } } ] } ] } }
The following query creates a modified copy of the feed by removing all entries that restrict syndication.
{ "encoding": encoding, "feed": { "author": feed.author, "category": feed.category, "entry": [feed.entry[app$control.yt$state.name != "restricted"]] } }
{ "encoding": "UTF-8", "feed": { "author": [ { "name": { "$t": "YouTube" }, "uri": { "$t": "http://www.youtube.com/" } } ], "category": [ { "scheme": "http://schemas.google.com/g/2005#kind", "term": "http://gdata.youtube.com/schemas/2007#video" } ], "entry": [] } }