Skip to content
excalibase
filtering
REST APIstable

Filtering

Query syntax: ?column=operator.value. Filters on different columns combine with AND; negate any one with not.: ?column=not.operator.value.

The examples run against the public demo project. Set BASE, PROJECT_ID and TOKEN as in Connect to your project, and send Authorization: Bearer $TOKEN as below.

Operators

OperatorExample
eq?kind=eq.idea
neq?kind=neq.bug
gt, gte?id=gte.5
lt, lte?id=lt.100
like (* for %)?body=like.*Welcome*
ilike?body=ilike.welcome*
startswith, endswith?body=startswith.Welcome
match, imatch (POSIX regex)?body=match.%5EWelcome
in, notin?kind=in.(idea,bug)
is.null, is.notnull?author_name=is.null
not. prefix?author_name=not.is.null
haskey, jsoncontains (cs), jsoncontained (cd), jsonpathon jsonb columns
arraycontains, arrayhasany (ov), arraylengthon array columns
plfts, wfts, phfts, ftson text and tsvector columns
vectork-NN search on pgvector columns

URL-encode reserved characters ({, }, ^, [, ], spaces) before sending: the server refuses them raw with a 400. Which operators a column accepts depends on its type and database; the engine reports them per column.

Comparison and lists

curl -s "$BASE/$PROJECT_ID/api/v1/feedback_posts?id=gte.1&id=lte.5&select=id,kind" \
  -H "Authorization: Bearer $TOKEN"
curl -s "$BASE/$PROJECT_ID/api/v1/feedback_posts?kind=in.(idea,bug)&select=id,kind" \
  -H "Authorization: Bearer $TOKEN"

Text and null checks

curl -s "$BASE/$PROJECT_ID/api/v1/feedback_posts?body=ilike.welcome*&select=id,body" \
  -H "Authorization: Bearer $TOKEN"
curl -s "$BASE/$PROJECT_ID/api/v1/feedback_posts?author_name=not.is.null&select=id,author_name" \
  -H "Authorization: Bearer $TOKEN"

Full-text search takes the same shape on a text column: plfts for raw user input, wfts for "phrase", OR and -exclude, phfts for adjacent words, fts for raw tsquery syntax.

curl -s "$BASE/$PROJECT_ID/api/v1/feedback_posts?body=plfts.welcome&select=id" \
  -H "Authorization: Bearer $TOKEN"

AND and OR

Every filter in the query string is ANDed. OR is a tree on the or parameter, which nests inside AND:

curl -s "$BASE/$PROJECT_ID/api/v1/feedback_posts?kind=eq.idea&or=(status.eq.open,status.eq.closed)&select=id,status" \
  -H "Authorization: Bearer $TOKEN"

Select columns

curl -s "$BASE/$PROJECT_ID/api/v1/feedback_posts?select=id,kind,author_name" \
  -H "Authorization: Bearer $TOKEN"

A column the role has no permission to read is not selectable: asking for it answers 400 Unknown column.

Ordering

order=column.direction, comma-separated; the suffixes are .asc, .desc, .nullsfirst and .nullslast.

curl -s "$BASE/$PROJECT_ID/api/v1/live_poll_options?order=position.asc,label.desc&select=position,label" \
  -H "Authorization: Bearer $TOKEN"

Pagination

curl -s "$BASE/$PROJECT_ID/api/v1/live_poll_options?select=id,label&order=id.asc&limit=2&offset=2" \
  -H "Authorization: Bearer $TOKEN"

limit is capped by the server and by the role's permission (the demo project caps each table at 50 or 100 rows). Always pair limit and offset with an order. For deep pages, filter on the last key you saw instead of a large offset: ?id=gt.40&order=id.asc&limit=20, or let the API do it with first and after, where after is the last value of the ascending order column you saw:

curl -s "$BASE/$PROJECT_ID/api/v1/live_poll_options?select=id,label&order=id.asc&first=2&after=2" \
  -H "Authorization: Bearer $TOKEN"
{ "pageInfo": { "hasNextPage": false }, "data": [{ "id": "3", "label": "Booking calendar" }, { "id": "4", "label": "Recipe box" }] }

Relay-style edges and endCursor are a GraphQL feature: see the ...Connection fields.

A total needs Prefer: count=exact, and counting needs a role whose permission has allowAggregations; otherwise the request answers 403 permission_denied. The demo project allows it on its votes tables:

curl -s -i "$BASE/$PROJECT_ID/api/v1/live_poll_votes?select=id&limit=1" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Prefer: count=exact"

The response carries Content-Range: 0-0/2 and "pagination": { "total": 2, ... }. Without the header the count is skipped, which is faster on large tables.

Not in REST

  • Aggregates. Use GraphQL: see Aggregations. Related rows embed with select: see Relationships.
  • Typed enum values. REST values are strings, so a wrong enum value is a database error (400 invalid_value), not a schema error. GraphQL checks them before the query runs.

Self-hosting

The operators, order syntax and Prefer headers are the same on a standalone engine. The standalone excalibase-rest service also accepts ?orderBy=col&orderDirection=asc; that parameter is self-hosted only and a cloud project answers 400 Unknown column 'orderBy'.