Queries

Every table in the Data Model generates a REST query surface. This page covers the read patterns you’ll use most. The examples assume a students table with fields like firstName, email, age, isActive, createdAt, and a city relationship.

Single record

GET /api/rest/<table>/<id> returns one record by primary key.

curl -X GET "https://your-gateway.example.com/gw/api/rest/students/287cff0a-345b-4cca-9e9a-75a2161238fd" \
  -H "Authorization: Bearer archie_YOUR_API_KEY" \
  -H "X-Project-ID: your-project-id"

{
  "data": {
    "id": "287cff0a-345b-4cca-9e9a-75a2161238fd",
    "firstName": "James",
    "email": "james.smith@example.com",
    "age": 22,
    "isActive": true,
    "createdAt": "2025-12-01T10:30:00.000Z"
  }
}

A missing record returns 404 with a Problem Details body — see Error handling. Tables with composite primary keys don’t expose /<id> routes. Use the filtered list or the advanced query endpoint instead.

Record list

GET /api/rest/<table> returns up to 100 records by default, wrapped in a data array with pagination metadata.

curl -X GET "https://your-gateway.example.com/gw/api/rest/students" \
  -H "Authorization: Bearer archie_YOUR_API_KEY" \
  -H "X-Project-ID: your-project-id"

{
  "data": [
    { "id": "287cff0a-...", "firstName": "James", "email": "james.smith@example.com" }
  ],
  "meta": {
    "pagination": {
      "limit": 100,
      "offset": 0,
      "hasNextPage": false,
      "hasPreviousPage": false
    }
  }
}

To get the total record count, send Prefer: count=exact (slower) or count=estimated (fast, approximate).

Filtered list

Filters are query parameters in the form ?fieldName=operator.value.

curl -X GET "https://your-gateway.example.com/gw/api/rest/students?isActive=equals.true&age=gte.21" \
  -H "Authorization: Bearer archie_YOUR_API_KEY" \
  -H "X-Project-ID: your-project-id"

Multiple parameters AND together by default.

Comparison operators

Operator	Example
`equals`	`?status=equals.active`
`not_equals`	`?status=not_equals.archived`
`gt` / `gte`	`?age=gte.18`
`lt` / `lte`	`?price=lt.100`
`contains`	`?name=contains.john`
`starts_with` / `ends_with`	`?email=starts_with.admin`
`in`	`?status=in.(active,pending)`
`not_in`	`?status=not_in.(archived,deleted)`
`between`	`?age=between.18,30`
`is_empty` / `is_not_empty`	`?bio=is_empty.true`

Date helpers

Operator	Example
`today`	`?createdAt=today.true`
`yesterday`	`?createdAt=yesterday.true`
`this_week`	`?createdAt=this_week.true`
`this_month`	`?createdAt=this_month.true`
`last_days`	`?createdAt=last_days.7`
`last_weeks`	`?createdAt=last_weeks.2`
`last_months`	`?createdAt=last_months.3`

Logical groups

or=(...), and=(...), and not=(...) compose conditions. They can nest.

curl -X GET "https://your-gateway.example.com/gw/api/rest/students?or=(status.equals.active,role.equals.admin)" \
  -H "Authorization: Bearer archie_YOUR_API_KEY" \
  -H "X-Project-ID: your-project-id"

curl -X GET "https://your-gateway.example.com/gw/api/rest/students?or=(status.equals.active,and(role.equals.admin,age.gte.21))" \
  -H "Authorization: Bearer archie_YOUR_API_KEY" \
  -H "X-Project-ID: your-project-id"

Filtering across relations

Use dot notation to filter a parent by a related table’s field.

curl -X GET "https://your-gateway.example.com/gw/api/rest/students?city.state=equals.Illinois" \
  -H "Authorization: Bearer archie_YOUR_API_KEY" \
  -H "X-Project-ID: your-project-id"

Field name conflicts

If a field name collides with a reserved query parameter (select, sort, limit), prefix with col.:

curl -X GET "https://your-gateway.example.com/gw/api/rest/products?col.select=equals.premium"

Sorted list

?sort=field sorts ascending. Prefix with - for descending. Comma-separate for multi-column sort.

curl -X GET "https://your-gateway.example.com/gw/api/rest/students?sort=-age,firstName" \
  -H "Authorization: Bearer archie_YOUR_API_KEY" \
  -H "X-Project-ID: your-project-id"

The dot-style alternative (sort=createdAt.desc,firstName.asc) is also supported.

Paginated list

Two pagination styles, both work on the same endpoint.

Offset-based

Parameter	Alias	Default	Max
`limit`	`first`	100	1000
`offset`	`skip`	0	—

curl -X GET "https://your-gateway.example.com/gw/api/rest/students?limit=20&offset=40" \
  -H "Authorization: Bearer archie_YOUR_API_KEY" \
  -H "X-Project-ID: your-project-id" \
  -H "Prefer: count=exact"

hasNextPage and hasPreviousPage in the response tell you when to stop.

Cursor-based

For stable pagination on frequently-changing tables, use cursors. Cursor values are opaque, base64-encoded strings — read them from the response, don’t construct them yourself.

curl -X GET "https://your-gateway.example.com/gw/api/rest/students?first=20" \
  -H "Authorization: Bearer archie_YOUR_API_KEY" \
  -H "X-Project-ID: your-project-id"

curl -X GET "https://your-gateway.example.com/gw/api/rest/students?first=20&after=eyJpZCI6Ijk3..." \
  -H "Authorization: Bearer archie_YOUR_API_KEY" \
  -H "X-Project-ID: your-project-id"

Field selection

?select=field1,field2 returns only the listed fields.

curl -X GET "https://your-gateway.example.com/gw/api/rest/students?select=id,firstName,email" \
  -H "Authorization: Bearer archie_YOUR_API_KEY" \
  -H "X-Project-ID: your-project-id"

Parentheses embed fields from related tables — equivalent to a SQL JOIN or a nested GraphQL query.

curl -X GET "https://your-gateway.example.com/gw/api/rest/students?select=id,firstName,city(nameCity,state)" \
  -H "Authorization: Bearer archie_YOUR_API_KEY" \
  -H "X-Project-ID: your-project-id"

You can nest up to 3 levels deep, with a maximum of 10 relations per query. Exceeding either limit returns 400 Bad Request.

Field aliases

Use originalName:alias to rename fields in the response.

curl -X GET "https://your-gateway.example.com/gw/api/rest/students?select=firstName:name,email:contactEmail,city:location(nameCity:cityName)"

Aggregation

GET /api/rest/<table>/_aggregate computes counts, sums, averages, and other aggregates.

curl -X GET "https://your-gateway.example.com/gw/api/rest/orders/_aggregate?fn=count,sum:total,avg:total" \
  -H "Authorization: Bearer archie_YOUR_API_KEY" \
  -H "X-Project-ID: your-project-id"

{
  "data": {
    "aggregates": [
      { "count": 156, "sum_total": 24780.50, "avg_total": 158.85 }
    ]
  }
}

Function	Example
`count`	`fn=count`
`sum`	`fn=sum:total`
`avg`	`fn=avg:total`
`min`	`fn=min:total`
`max`	`fn=max:total`
`count_distinct`	`fn=count_distinct:status`

Group by

curl -X GET "https://your-gateway.example.com/gw/api/rest/orders/_aggregate?fn=count,avg:total&groupBy=status,country" \
  -H "Authorization: Bearer archie_YOUR_API_KEY" \
  -H "X-Project-ID: your-project-id"

Filter groups with `having`

curl -X GET "https://your-gateway.example.com/gw/api/rest/orders/_aggregate?fn=count&groupBy=status&having=count.gt.10" \
  -H "Authorization: Bearer archie_YOUR_API_KEY" \
  -H "X-Project-ID: your-project-id"

Sort and filter aggregates

sort=-count sorts groups by an aggregate alias. Filters from a regular list query (createdAt=this_month.true) apply before aggregation.

Advanced query endpoint

For deeply nested filter logic that’s awkward to express as URL params, POST /api/rest/<table>/_query accepts a structured JSON body. It’s a POST for ergonomics — but the operation is read-only, no data is modified.

curl -X POST "https://your-gateway.example.com/gw/api/rest/students/_query" \
  -H "Authorization: Bearer archie_YOUR_API_KEY" \
  -H "X-Project-ID: your-project-id" \
  -H "Content-Type: application/json" \
  -d '{
    "filter": [
      { "field": "isActive", "operator": "equals", "value": true },
      { "field": "age", "operator": "between", "value": [18, 30] }
    ],
    "sort": [
      { "field": "age", "direction": "DESC" }
    ],
    "pagination": { "first": 10, "skip": 0 },
    "select": [
      { "field": "id" },
      { "field": "firstName" },
      { "field": "city", "children": [{ "field": "nameCity" }] }
    ]
  }'

Use the advanced endpoint when:

Use case	Why
Deeply nested AND/OR/NOT logic	Easier to express in JSON than URL params.
Dynamic queries built in code	Constructing JSON is cleaner than URL string concatenation.
Filters exceed URL length limits	Long URL filters can hit gateway/proxy limits; bodies don’t.

For simple lookups, the GET form is still better — it’s cacheable and bookmarkable.

Permissions

All query endpoints enforce the per-role permissions in Role-Based Access. Records and fields a role can’t read are silently filtered out of responses.

FAQ

What's the maximum number of records per request?

1000 with limit=1000. The default is 100. For larger result sets, paginate.

Why doesn't `totalCount` appear in my response?

By default it’s omitted for performance. Send Prefer: count=exact for the precise total or count=estimated for a fast approximation.

Should I use offset or cursor pagination?

Offset is simpler — works for jump-to-page UIs. Cursor is more stable when records are being inserted or deleted between page fetches. Use cursor for infinite-scroll feeds; use offset for paginated tables.

When should I use `_query` vs URL params?

URL params for simple, browser-friendly, cacheable queries. _query for complex nested logic, dynamic query construction, or filters that exceed URL length limits.

How do I select fields from a relation that's many-to-many?

Embed the join table — for example, ?select=id,name,enrollments(course(name)) — and the parentheses traverse the relationship. Up to 3 levels deep.

Home

Blueprint

Design

Frontend

Backend

Assets

Single record

Record list

Filtered list

Comparison operators

Date helpers

Logical groups

Filtering across relations

Field name conflicts

Sorted list

Paginated list

Offset-based

Cursor-based

Field selection

Field aliases

Aggregation

Group by

Filter groups with `having`

Sort and filter aggregates

Advanced query endpoint

Permissions

FAQ

​Single record

​Record list

​Filtered list

​Comparison operators

​Date helpers

​Logical groups

​Filtering across relations

​Field name conflicts

​Sorted list

​Paginated list

​Offset-based

​Cursor-based

​Field selection

​Embedding related records

​Field aliases

​Aggregation

​Group by

​Filter groups with having

​Sort and filter aggregates

​Advanced query endpoint

​Permissions

​FAQ

Single record

Record list

Filtered list

Comparison operators

Date helpers

Logical groups

Filtering across relations

Field name conflicts

Sorted list

Paginated list

Offset-based

Cursor-based

Field selection

Embedding related records

Field aliases

Aggregation

Group by

Filter groups with `having`

Sort and filter aggregates

Advanced query endpoint

Permissions

FAQ