Multiple level filters in jq
Here's another note-to-self on using jq to shape JSON representations of OData to match what's returned using system query options. Thsi time it's all filtering at two levels.
In the Back to basics: OData - the Open Data Protocol - Part 3 - System query options live stream last Friday we looked at OData's system query options.
There was a question at the end about whether it was possible to use the
$filter system query option at multiple levels, in an
$expand context. I wrote up the question, and a detailed answer (summary: yes) with an example here: Can $filter be applied at multiple levels in an expand?.
I thought this would be another good opportunity to practise a bit of
jq this Saturday late morning, so wondered what a
jq filter would look like, one that would produce the same result as in the answer's example (showing suppliers only from the UK, and only including their products that were low in stock).
The OData URL for this request looks like this:
http://localhost:4004/northwind-model/Suppliers ?$filter=Country eq 'UK' &$expand=Products($filter=UnitsInStock le 15)
It turned out to be pretty simple. First, I grabbed the basic data:
Incidentally, here's another example of the power of OData, being able to fetch data from related resources, in the same single request (see Nonsense! Absolute nonsense! for a deliberately provocative take on how some folks are so attracted to shiny new things they ignore what is already there).
Then I loaded it into ijq, the lovely interactive frontend to
jq, and played around a bit.
Here's what I ended up with:
select(.Country == "UK")
| .Products |= map(
select(.UnitsInStock <= 15)
Breaking this down, we have:
.valuegives me the entire array of objects in the dataset, each one of which represents a supplier with all their products
maptakes the array of supplier and product data and produces a new array, having processed each array element (each supplier with their products) with the filter expression supplied
select(.Country == "UK")this is the equivalent of the
$filter=Country eq UKin the OData URL
.Products |= map(...)the result of the previous
select(i.e. each supplier that is in the UK) is then passed to this expression which uses the update assignment (
|=) to produce a modified version of the value of the
select(.UnitsInStock <= 15)the value of the
Productsproperty is an array, because the navigation property between the
Productsentity types is defined as a one-to-many. This means it's appropriate to use another
selectfilter to pick out specific elements (those with a value of 15 or less for the
UnitsInStock). This is the equivalent of the
Products($filter=UnitsInStock le 15)part of the OData URL
One thing to note here is that there's a single outer
map, the processing within which not only filters the suppliers, but subsequently filters the products of the (reduced number of) suppliers, in one pass.
Anyway, that's pretty much it for this note-to-self. I think it's time for an early afternoon beer at Browtons. Cheers.