Filtering

Filter generated-API lists with column=value and the eq, neq, gt, gte, lt, lte, like and in operators - syntax, combination rules and what is not supported.

Filters are query parameters on a list route (GET /api/<schema>/<table>). The examples assume the setup from Authentication.

Syntax

Parameter	SQL	Example
`column=value`	`=`	`artist_id=1`
`column[eq]=value`	`=`	`artist_id[eq]=1`
`column[neq]=value`	`<>`	`title[neq]=Let There Be Rock`
`column[gt]=value`	`>`	`milliseconds[gt]=1500000`
`column[gte]=value`	`>=`	`total[gte]=20`
`column[lt]=value`	`<`	`unit_price[lt]=1`
`column[lte]=value`	`<=`	`total[lte]=25`
`column[like]=pattern`	`LIKE`	`name[like]=Led%` (`%` any text, `_` one character)
`column[in]=a,b,c`	`IN`	`artist_id[in]=1,8,22`

Several filters are combined with AND. There is no OR.
Values are converted to the column's type, so milliseconds[gt]=long on an integer column is a 422.
like compares text as the database does: case-sensitive on PostgreSQL, usually case-insensitive on MySQL and SQL Server (depends on the collation).
URL-encode values: % is %25, a space %20 or +.
With curl, pass -g (--globoff) so the [ and ] in parameter names are sent literally, or encode them as %5B / %5D.

Examples

Albums by one artist (implicit equals):

curlJavaScriptPythonC#

curl -s "$RESTWARD_API_URL/api/public/album?artist_id=1" \
  -H "X-API-Key: $RESTWARD_API_KEY"

const url = new URL("/api/public/album", process.env.RESTWARD_API_URL);
url.searchParams.set("artist_id", "1");
const albums = await (await fetch(url, { headers })).json();

albums = session.get(f"{api}/api/public/album", params={"artist_id": 1}).json()

var albums = await http.GetFromJsonAsync<List<Dictionary<string, JsonElement>>>("/api/public/album?artist_id=1");

JSON

[
  { "album_id": 1, "title": "For Those About To Rock We Salute You", "artist_id": 1 },
  { "album_id": 4, "title": "Let There Be Rock", "artist_id": 1 }
]

The three longest tracks over 25 minutes (comparison + sort):

curlJavaScriptPythonC#

curl -sg "$RESTWARD_API_URL/api/public/track?milliseconds[gt]=1500000&sort=-milliseconds&limit=3" \
  -H "X-API-Key: $RESTWARD_API_KEY"

const url = new URL("/api/public/track", process.env.RESTWARD_API_URL);
url.search = new URLSearchParams({ "milliseconds[gt]": "1500000", sort: "-milliseconds", limit: "3" });
const tracks = await (await fetch(url, { headers })).json();

tracks = session.get(
    f"{api}/api/public/track",
    params={"milliseconds[gt]": 1500000, "sort": "-milliseconds", "limit": 3},
).json()

var tracks = await http.GetFromJsonAsync<List<Dictionary<string, JsonElement>>>(
    "/api/public/track?milliseconds%5Bgt%5D=1500000&sort=-milliseconds&limit=3");

JSON

[
  {
    "track_id": 2820, "name": "Occupation / Precipice", "album_id": 227, "media_type_id": 3,
    "genre_id": 19, "composer": null, "milliseconds": 5286953, "bytes": 1054423946, "unit_price": 1.99
  },
  {
    "track_id": 3224, "name": "Through a Looking Glass", "album_id": 229, "media_type_id": 3,
    "genre_id": 21, "composer": null, "milliseconds": 5088838, "bytes": 1059546140, "unit_price": 1.99
  },
  {
    "track_id": 3244, "name": "Greetings from Earth, Pt. 1", "album_id": 253, "media_type_id": 3,
    "genre_id": 20, "composer": null, "milliseconds": 2960293, "bytes": 536824558, "unit_price": 1.99
  }
]

A range on one column (two operators, AND):

Shell

curl -sg "$RESTWARD_API_URL/api/public/invoice?total[gte]=20&total[lte]=25&sort=invoice_id&limit=3" \
  -H "X-API-Key: $RESTWARD_API_KEY"

JSON

[
  { "invoice_id": 96, "customer_id": 45, "invoice_date": "2022-02-18T00:00:00", "billing_city": "Budapest", "total": 21.86, "...": "…" },
  { "invoice_id": 194, "customer_id": 46, "invoice_date": "2023-04-28T00:00:00", "billing_city": "Dublin", "total": 21.86, "...": "…" },
  { "invoice_id": 299, "customer_id": 26, "invoice_date": "2024-08-05T00:00:00", "billing_city": "Fort Worth", "total": 23.86, "...": "…" }
]

Pattern match and set membership:

Shell

curl -sg "$RESTWARD_API_URL/api/public/artist?name[like]=Led%25" -H "X-API-Key: $RESTWARD_API_KEY"
# [{"artist_id":22,"name":"Led Zeppelin"}]

curl -sg "$RESTWARD_API_URL/api/public/artist?artist_id[in]=1,8,22" -H "X-API-Key: $RESTWARD_API_KEY"
# [{"artist_id":1,"name":"AC/DC"},{"artist_id":8,"name":"Audioslave"},{"artist_id":22,"name":"Led Zeppelin"}]

On PostgreSQL, name[like]=led%25 returns []: the match is case-sensitive.

Mistakes are reported

An unknown column or operator is a 400 whose parameter field names the offending query parameter, so a typo never silently returns unfiltered rows:

Shell

curl -sg "$RESTWARD_API_URL/api/public/artist?genre=Rock" -H "X-API-Key: $RESTWARD_API_KEY"
# 400 {"error":"Unknown filter column 'genre' on 'artist'.","parameter":"genre"}

curl -sg "$RESTWARD_API_URL/api/public/artist?name[ilike]=led%25" -H "X-API-Key: $RESTWARD_API_KEY"
# 400 {"error":"Unknown filter operator 'ilike'. Supported: eq, neq, gt, gte, lt, lte, like, in.","parameter":"name[ilike]"}

Every query parameter other than limit, offset, sort, fields and count is read as a filter, so do not add other parameters (such as cache busters) to list requests. Column names are in the API's OpenAPI document.

Not supported yet

OR conditions, or filters across related tables
Testing for NULL (column= compares with an empty string)
Case-insensitive matching on PostgreSQL (ilike)