Skip to content

4. Filtering, sorting, pagination

  • Combine several filters in the query string and know which values each one accepts.
  • Sort with sort=field / sort=-field.
  • Understand offset pagination (page/limit) and its limits; read meta and links.
  • Write a loop that downloads all pages by following links.next, in Python and TypeScript.
  • Recognize parameter errors (400, 422) before they turn into bugs.

A collection can have thousands of elements: the client says which ones it wants with query parameters. On /api/v1/books these are available:

Parameter What it does Example
q text contained in the title (LIKE %q%, case-insensitive) q=trieste
author_id books by an author (foreign key) author_id=4
category category slug, subcategories included category=narrativa also takes gialli (crime), fantascienza (sci-fi), storici (historical)
tag books with that tag (N-N relation) tag=premio
min_price, max_price price range (inclusive) min_price=10&max_price=20
year_from, year_to publication year range year_from=2000
language language code language=en
in_stock true/false in_stock=true
format value of attributes.format, a JSON column format=tascabile

Filters combine with AND: category=gialli&max_price=15 = crime and under €15. (For OR and complex conditions there is POST /query, chapter 11.)

Values reach the server as strings: it is the server that converts them (min_price=10 → number) and validates them. If you send min_price=dieci you get 422.

sort=price ascending, sort=-price descending: the minus sign before the name is a widespread convention (JSON:API, many REST APIs). Fields allowed here: id, title, price, year, pages, created_at, rating. With equal values the server sorts by id, so the order is stable from one page to the next (without this rule, an element could appear on two pages or on none).

With 80 books fine, with 80,000 no: responses are split into pages.

  • limit = elements per page (default 20 here, maximum 100).
  • page = which page, starting from 1.
  • The server computes OFFSET (page-1)*limit in the SQL query: this is offset pagination. Simple, but with huge offsets it becomes slow and, if someone inserts an element in the meantime, pages “slide”. The alternative — cursor pagination (?after=<id>) — is touched on in chapter 14.

The response tells you where you are and how to carry on:

"meta": { "page": 1, "limit": 3, "total": 4, "pages": 2 },
"links": { "self": "…page=1…", "next": "…page=2…", "prev": null }

links.next is null on the last page: that is the loop’s exit condition. And it already contains all the filters: the client does not have to rebuild anything.

The most expensive crime novels, three per page:

terminal
curl -sS -G https://corso-api-json.giuseppebosi.com/api/v1/books \
--data-urlencode 'category=gialli' \
--data-urlencode 'sort=-price' \
--data-urlencode 'limit=3' | jq '{meta, links, titoli: [.items[] | "\(.id) \(.title) \(.price)"]}'
-G
sends the --data-urlencode values in the query string (GET method)
category=gialli
filter on the category (slug), recursive over subcategories
sort=-price
descending price
limit=3
three per page; no page given = 1
| jq …
builds an object with meta, links and a list of "id title price" strings (in jq \(…) interpolates inside strings)
{
"meta": { "page": 1, "limit": 3, "total": 4, "pages": 2 },
"links": {
"self": "https://corso-api-json.giuseppebosi.com/api/v1/books?category=gialli&sort=-price&page=1&limit=3",
"next": "https://corso-api-json.giuseppebosi.com/api/v1/books?category=gialli&sort=-price&page=2&limit=3",
"prev": null
},
"titoli": [ "48 La casa sul Porto Vecchio 21.77", "1 Il silenzio di Ustica 18.71", "67 Nebbia su Lecce 11.18" ]
}

Four crime novels in all, two pages: you get the second with --data-urlencode 'page=2' or simply with curl -sS "$(…links.next…)".

A filter on a JSON column combined with others:

terminal
curl -sS 'https://corso-api-json.giuseppebosi.com/api/v1/books?format=tascabile&in_stock=true&min_price=10&max_price=20&limit=2' \
| jq '.meta.total, (.items[] | [.id, .title, .price, .attributes.format])'
'…?format=…&in_stock=…'
here the query string is written by hand: no value has spaces or special characters, but the URL still goes in quotes because of &
format=tascabile
filters attributes.format: the server uses json_extract(attributes, '$.format') in SQL
in_stock=true
boolean: true/false, 1/0 are accepted
| jq …
prints the total and, for each book, an array with four fields
11
[14,"Le avventure di Orvieto",10.56,"tascabile"]
[21,"Il drago di Alba",18.98,"tascabile"]

And parameter errors:

terminal
curl -sS 'https://corso-api-json.giuseppebosi.com/api/v1/books?sort=boh'
curl -sS 'https://corso-api-json.giuseppebosi.com/api/v1/books?limit=500'
sort=boh
nonexistent sort field → 400 bad_sort, and in details.allowed the list of valid ones
limit=500
over the maximum → 422 validation_error, with details[].loc pointing to ["query","limit"]
{"error":{"code":"bad_sort","message":"sort non valido: boh","details":{"allowed":["created_at","id","pages","price","rating","title","year"]}}}
{"error":{"code":"validation_error","message":"Richiesta non valida: controlla i campi in details","details":[{"loc":["query","limit"],"msg":"Input should be less than or equal to 100","type":"less_than_equal"}]}}

Python and TypeScript: downloading every page

Section titled “Python and TypeScript: downloading every page”

The pattern is always the same: start from the URL with the filters, then follow links.next until it is null. Watch one detail: the parameters go only in the first request, because next already contains them.

# python3 tutte_le_pagine.py
import httpx
url = "https://corso-api-json.giuseppebosi.com/api/v1/books"
params = {"category": "saggistica", "sort": "-price", "limit": 10}
tutti = []
with httpx.Client() as client: # un Client riusa la connessione tra le richieste
while url:
r = client.get(url, params=params)
r.raise_for_status()
dati = r.json()
tutti.extend(dati["items"])
print(f'pagina {dati["meta"]["page"]}/{dati["meta"]["pages"]}: {len(dati["items"])} libri')
url, params = dati["links"]["next"], None # next contiene già filtri e limit
print(len(tutti), "libri in totale; il più caro:", tutti[0]["title"], tutti[0]["price"])

What you should see:

pagina 1/4: 10 libri
pagina 2/4: 10 libri
pagina 3/4: 10 libri
pagina 4/4: 4 libri
34 libri in totale; il più caro: Ustica e il Novecento 34.36

Output (identical in both languages):

pagina 1/4: 10 libri
pagina 2/4: 10 libri
pagina 3/4: 10 libri
pagina 4/4: 4 libri
34 libri in totale; il più caro: Ustica e il Novecento 34.36
GET/api/v1/books?category=narrativa&year_from=2015&sort=year&limit=5— fiction (and subcategories) since 2015, by year
GET/api/v1/books?tag=premio&sort=-rating&limit=5— books tagged «premio», by average rating
GET/api/v1/books?min_price=abc— a wrong value: look at details

Try it online, nothing to install:

Open the notebook in Google Colab Open the TypeScript project in StackBlitz

The GET request straight in your browser: https://corso-api-json.giuseppebosi.com/api/v1/books?category=narrativa&year_from=2015&sort=year&limit=5

Python on Google Colab (a free Google account is needed to run the cells); TypeScript on StackBlitz (it opens and starts by itself in the browser). If the practice API is switched off, turn it on from /en/api-attiva/.

  1. How many books in English are in the catalog, and which is the cheapest? (A single curl command, with jq or python3 -m json.tool.)
  2. Write a function tutti(url, params) that returns the full list of elements of any collection, and use it to count the orders with status=delivered (endpoint /api/v1/orders, same meta/links).
Solution
  1. curl -sS 'https://corso-api-json.giuseppebosi.com/api/v1/books?language=en&sort=price&limit=1' | jq '.meta.total, .items[0].title, .items[0].price'
import httpx
def tutti(url, params=None):
out = []
with httpx.Client() as c:
while url:
d = c.get(url, params=params).raise_for_status().json()
out += d["items"]
url, params = d["links"]["next"], None
return out
consegnati = tutti("https://corso-api-json.giuseppebosi.com/api/v1/orders", {"status": "delivered", "limit": 100})
print(len(consegnati))

What you should see:

48
  • Passing params again on every page together with links.next: the parameters get duplicated or overwritten (page=2 repeated). Pass the parameters only the first time.
  • Counting len(items) instead of reading meta.total: items is only the current page.
  • Ignoring meta.pages and doing for page in range(1, 1000): you ask for empty pages and burn the rate limit. Use links.next.
  • sort=price desc: the syntax is -price. And the space, by the way, would need encoding.
  • Sorting on the client after downloading only one page: you sort 20 elements out of 80.

AND filters in the query string (also on relations and JSON columns), sort=-field, page/limit with a maximum of 100, and links.next to walk through everything. In the next chapter we follow foreign keys: from an order to its books, to their authors, in a single request.