DJ Adams

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:

curl \
  'http://localhost:8000/northwind-model/Suppliers?$expand=Products' \
  > data.json

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:

.value
| map(
    select(.Country == "UK")
    | .Products |= map(
        select(.UnitsInStock <= 15)
      )
  )

Breaking this down, we have:

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.