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
| Operator | Example |
|---|---|
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), jsonpath | on jsonb columns |
arraycontains, arrayhasany (ov), arraylength | on array columns |
plfts, wfts, phfts, fts | on text and tsvector columns |
vector | k-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'.