Relations & Search in URLs

This page covers the advanced URL query parameters for relation loading, text search, vector search, and aggregation. These work on top of the basic query syntax.

Relation Loading ($with)

Load related data inline using $with. Relations must be declared with @db.rel.to, @db.rel.from, or @db.rel.via in your .as schema. See Relations — Foreign Keys for how to define them.

Basic Usage

Load a single relation:

bash

curl "http://localhost:3000/todos/query?\$with=author"

Response:

json

[
  {
    "id": 1,
    "title": "Buy milk",
    "authorId": 5,
    "author": { "id": 5, "name": "Alice" }
  }
]

Multiple Relations

Comma-separate relation names:

bash

curl "http://localhost:3000/todos/query?\$with=author,comments"

Response:

json

[
  {
    "id": 1,
    "title": "Buy milk",
    "authorId": 5,
    "author": { "id": 5, "name": "Alice" },
    "comments": [{ "id": 10, "text": "Great task!", "todoId": 1 }]
  }
]

With Controls

Apply sorting, limits, or projection to a loaded relation using parentheses:

bash

curl "http://localhost:3000/todos/query?\$with=comments(\$limit=5&\$sort=-createdAt)"

Response:

json

[
  {
    "id": 1,
    "title": "Buy milk",
    "comments": [
      { "id": 15, "text": "Latest comment", "createdAt": 1710000000 },
      { "id": 14, "text": "Earlier comment", "createdAt": 1709900000 }
    ]
  }
]

Inside parentheses, you can use any control parameter: $sort, $limit, $skip, $select.

Nested Relations

Load relations of relations:

bash

curl "http://localhost:3000/todos/query?\$with=comments(\$with=author)"

Response:

json

[
  {
    "id": 1,
    "title": "Buy milk",
    "comments": [
      {
        "id": 10,
        "text": "Great task!",
        "author": { "id": 5, "name": "Alice" }
      }
    ]
  }
]

Filtered Relations

Combine filters, controls, and nesting inside parentheses:

bash

curl "http://localhost:3000/todos/query?\$with=comments(status=approved&\$sort=-createdAt&\$limit=3&\$with=author(\$select=name))"

All filter operators and control parameters work inside relation sub-queries — including relational predicates on the related table's own relations ($with=comments(author=$some(role=staff)), since 0.1.147). A sub-filter narrows the loaded rows; to narrow the parent rows by them, put the predicate in the parent filter instead (comments=$some(status=approved)).

Validation

The controller validates relation names against your schema:

Unknown relation — returns 400 with a list of available relations. Since 0.1.143 this runs at every level: a nested $with ($with=author($with=org)) is checked against the relations of its own target table, and the list names that level's relations. A dotted name ($with=author.org, since 0.1.165 loaded as author($with=org)) is rewritten into that nesting right after parsing, so it is checked, sealed and scoped level by level exactly like the nested form — and an unknown segment answers with that segment's name (Unknown relation "org"). A relation hasField hides (checked at its full path, e.g. author.org) answers exactly like a nonexistent one. The check runs before the sub-query fields, so an unknown nested relation is never reported as Unknown field "author.nope". A permission layer that rejects a relation itself should throw unknownRelationError(name, visibleRelations) from @atscript/moost-db — the single source of this wording.
FK field used as relation — $with=projectId returns 400 (use the navigation property name instead, e.g., $with=project)

FK fields auto-included

When using $select alongside $with, foreign key fields needed for joining are automatically included — even if not explicitly listed or explicitly excluded. This ensures relations resolve correctly.

For the programmatic equivalent, see Relations — Loading.

Text Search ($search, $index, $fuzzy, $rank)

Perform full-text search on fields annotated with @db.index.fulltext or similar search indexes.

Fallback without native search

When the adapter reports no native search capability (db-memory always; MongoDB without Atlas Search indexes), $search falls back to a case-insensitive literal substring match OR'd across the fields annotated @db.column.searchable:

atscript

export interface Job {
    @db.column.searchable
    jobName: string
    @db.column.searchable
    description: string
}

The term is escaped by the framework (no user-supplied regex), merged into the request filter with $and, and applied uniformly on query (including $count), pages, and aggregate reads. Where native search is configured it wins and the annotation is not consulted. /meta reports searchable: true either way, so UI search boxes appear without overrides. Encrypted, adapter-unfilterable, and @db.writeOnly fields never participate.

Integer fields. @db.column.searchable also works on integer fields (number.int and its sizes, number + @expect.int, @db.default.increment). The term matches when it is a substring of the number's decimal text — 2946 finds 29461277, 2946 and -2946; -29 finds -2946; abc matches no integer row. Like the string match it is a full scan. Floats, decimals and timestamps are refused at compile time.

atscript

export interface Ticket {
    @db.column.searchable
    title: string
    @db.column.searchable
    refNo: number.int
}

A field the request cannot see (hasField) never participates, integer or not. For exact whole-number matching use @db.index.fulltext instead; numeric IDs need no applySearchFallback override.

$count follows the same population as the rows: GET /query?$search=x&$count=true counts the native search matches (and a $vector search counts its nearest neighbours up to $limit), not the whole table.

Basic Search

Search across all fulltext-indexed fields:

bash

curl "http://localhost:3000/articles/query?\$search=mongodb+tutorial"

Response:

json

[
  { "id": 3, "title": "MongoDB Tutorial for Beginners", "body": "..." },
  { "id": 7, "title": "Advanced MongoDB Patterns", "body": "..." }
]

Named Search Index

Target a specific search index with $index:

bash

curl "http://localhost:3000/products/query?\$search=wireless+headphones&\$index=product_search"

On MongoDB this is also how you pick a search variant — when the same field is indexed both ways (e.g. word match vs typeahead), $index selects which behavior runs. See MongoDB → Search Variants.

Fuzzy Tolerance (MongoDB Atlas)

$fuzzy overrides the index's declared typo tolerance for a single request — 1 or 2 edits, or 0 to disable:

bash

curl "http://localhost:3000/users/query?\$search=mngo&\$fuzzy=1"

The default comes from the index annotation (@db.mongo.search.static/.dynamic); see MongoDB → Fuzzy Search.

Unranked Search

Since 0.1.175. $rank=false skips relevance ranking: rows come in $sort order, or in primary-key order without one. Use it when your app orders the matches itself:

bash

curl "http://localhost:3000/articles/query?\$search=mongodb&\$rank=false&\$select=id"

A request with $vector refuses it (400). See Text Search — Unranked search.

Combining with Filters

Search and filter can be used together:

bash

curl "http://localhost:3000/articles/query?\$search=mongodb&status=published&\$sort=-createdAt"

Paginated Search

Search works with both /query and /pages:

bash

curl "http://localhost:3000/articles/pages?\$search=typescript&\$page=1&\$size=10"

Adapter support

Full-text search support varies by adapter. MongoDB supports Atlas Search with named indexes. SQLite provides FTS5-based search. Check your adapter's documentation for details.

For annotations and programmatic API, see Text Search.

Vector Search ($vector, $threshold, $search)

Perform similarity search on fields annotated with @db.search.vector.

How It Works

Vector search via URL requires three components:

$vector — the name of the vector field to search on
$search — the text query (converted to an embedding by the controller)
$threshold (optional) — minimum similarity threshold

The controller calls the computeEmbedding() hook to convert the search text into a vector, then passes it to the adapter's vector search.

Basic Usage

bash

curl "http://localhost:3000/articles/query?\$vector=embedding&\$search=machine+learning+basics"

Response:

json

[
  { "id": 12, "title": "Introduction to ML", "score": 0.95 },
  { "id": 8, "title": "Deep Learning Guide", "score": 0.87 }
]

With Threshold

Set a minimum similarity score:

bash

curl "http://localhost:3000/articles/query?\$vector=embedding&\$search=machine+learning&\$threshold=0.8"

Only results with similarity >= 0.8 are returned.

Combining with Filters

bash

curl "http://localhost:3000/articles/query?\$vector=embedding&\$search=machine+learning&status=published&\$limit=10"

The computeEmbedding Hook

Vector search via HTTP requires the computeEmbedding() hook to convert the search text into a vector. Without this override, vector search returns HTTP 501 Not Implemented. See Customization — computeEmbedding for the full implementation guide.

For annotations and programmatic API, see Vector Search.

Geo Search ($center, $maxDistance, $minDistance)

Tables with a @db.index.geo field expose a dedicated GET /geo endpoint for distance-ranked queries (available on every adapter):

bash

curl "http://localhost:3000/listings/geo?\$center=-122.42,37.77&\$maxDistance=50000&status=ACTIVE"
curl "http://localhost:3000/listings/geo?\$center=-122.42,37.77&\$page=1&\$size=20"

$center (required) is lng,lat; $maxDistance/$minDistance are meters. Rows come back distance-ordered, each carrying $distance (meters); with $page/$size the response uses the /pages envelope. Standard filters, $select, and $with compose normally. See Geo Search for the annotation, the programmatic geoSearch() API, the $geoWithin filter operator, and per-adapter support.

Aggregation ($groupBy)

Group records and compute aggregate values using $groupBy. The rules and semantics are those of Grouped Queries; this section covers the URL form.

Basic Usage

Group by a field and select aggregate functions:

bash

curl "http://localhost:3000/orders/query?\$groupBy=status&\$select=status,count(*):total"

Response:

json

[
  { "status": "pending", "total": 12 },
  { "status": "shipped", "total": 45 },
  { "status": "delivered", "total": 128 }
]

Aggregate functions in $select use the syntax fn(field):alias. Without :alias the key is fn_field — count(*) returns count_star.

With Filters

Apply filters before aggregation:

bash

curl "http://localhost:3000/orders/query?createdAt>1700000000&\$groupBy=region&\$select=region,sum(amount):total,count(*):orders"

Response:

json

[
  { "region": "US", "total": 15420.5, "orders": 87 },
  { "region": "EU", "total": 8930.0, "orders": 42 }
]

Without `$groupBy` — totals over the filtered set

Aggregates in $select without a $groupBy compute one row over every row the filter matches (since 0.1.155 — before, /query rejected the request with a generic 400):

bash

curl "http://localhost:3000/orders/query?status=paid&\$select=sum(amount):revenue,count(*):orders,avg(amount):avgOrder"

json

[{ "revenue": 24350.5, "orders": 129, "avgOrder": 188.76 }]

Over no matching row the one row has counts 0 and every other aggregate null. It is the ungrouped query of the core ($groupBy: []): every aggregate rule applies — a plain field in $select is a 400 (must also appear in $groupBy), $having filters the one row, $count returns [{ "count": 1 }] (or 0 when $having removes it). It passes the same gates as a grouped query: hidden fields, @db.writeOnly fields, the row overlay (transformFilter), and a validateControls override, which sees controls.$groupBy as [] (a check such as if (controls.$groupBy) blocks both; prepareRequest, which runs before, sees no $groupBy). $with and $vector are rejected as with $groupBy. Works on views too. Arithmetic (sum(price*qty), expr(total/n) over aggregate aliases) and first / last with $rowOrder work here too (since 0.1.168; before, a 400 … only valid in grouped queries); arithmetic or a calendar bucket with no aggregate in $select stays a 400.

/pages serves rows only: an aggregate there — $groupBy, or an aggregate in $select — is a 400 Aggregate queries are only valid on /query (since 0.1.155; before, the controls check rejected both with a generic message).

With Sorting

Sort aggregated results:

bash

curl "http://localhost:3000/orders/query?\$groupBy=status&\$select=status,sum(amount):total&\$sort=-total&\$limit=5"

Aggregate Functions

The standard SQL aggregate functions are available: count(*), count(field), sum(field), avg(field), min(field), max(field), and countDistinct(field) (distinct non-null values; /meta.aggregateFns lists what the adapter supports). Any other function name is rejected with 400 (since 0.1.135), and so is countDistinct(*). See Grouped Queries — Aggregate functions for their null handling.

Arithmetic and first / last

Compute with numbers in the same $select (since 0.1.148) — sum(<arith>):alias over a per-row expression, expr(<arith>):alias over the aliases of other entries — and read a representative row with first(field) / last(field) ordered by $rowOrder:

bash

curl "http://localhost:3000/issues/query?status=open&\$groupBy=ticketId&\$select=ticketId,count(*):open,sum(estimate):est,sum(price*qty):revenue,expr(est/open):avgEst,first(id):oldestId,first(title):oldestTitle&\$rowOrder=raisedAt&\$sort=-avgEst&\$having=avgEst>=2"

Every operand is checked like any other aggregate source — a field the caller cannot see answers Unknown field, a write-only field is refused, and the numeric rule (number only, not decimal or timestamp) is a 400. /meta advertises aggregateExpressions, the numeric operands (fields[path].numeric) and first / last in aggregateFns. Semantics, rules and errors: Grouped Queries — Arithmetic expressions and Representative row; URL grammar: Computed entries.

Distinct combinations and conditions

countDistinct(a,b) counts distinct combinations of several fields — a row with any null field is not counted — and fn(field|<filter>):alias aggregates only the rows matching the filter after | (since 0.1.168):

bash

curl "http://localhost:3000/orders/query?\$groupBy=region&\$select=region,countDistinct(customerId,productId):pairs,count(*|status=paid):paid,sum(amount|status=paid&qty>1):bulkRevenue&\$sort=-paid"

The text after the first | is a filter in the URL filter syntax; :alias is required with it. /meta advertises distinctFieldLists and conditionalAggregates. Semantics and errors: Distinct combinations and Conditional aggregates.

Two-level aggregates ($from)

$from=( … ) holds an inner grouped query; the rest of the query aggregates its rows (since 0.1.168):

bash

curl "http://localhost:3000/orders/query?status=paid&\$from=(\$groupBy=region,customerId&\$select=sum(amount):spent,count(*):orders)&\$groupBy=region&\$select=region,count(*):customers,avg(spent):avgSpent&\$sort=-avgSpent"

json

[
  { "region": "EU", "customers": 42, "avgSpent": 212.4 },
  { "region": "US", "customers": 87, "avgSpent": 177.2 }
]

Inside the parentheses go $groupBy (required), $select, $having, $rowOrder and $nulls, &-separated. Filter terms are not allowed there: the request filter narrows the rows the inner level groups.
$from is rejected on /pages, inside $with, in query targets and on value-help endpoints; on an adapter without support it is a 400 AGG_NESTED_NOT_SUPPORTED. /meta.nestedAggregates says whether the endpoint takes it.

Semantics and errors: Two-Level Aggregates.

Calendar Buckets

Group a timestamp field by hour, day, week, month, quarter or year in a time zone with bucket(field,unit[,tz][,weekStart]):alias in $select, and list the alias in $groupBy (since 0.1.132):

bash

curl "http://localhost:3000/tickets/query?\$select=bucket(openedAt,week,'Europe/Berlin',sun):week,count(*):n&\$groupBy=week&\$sort=week"

json

[
  { "week": "2026-03-01", "n": 12 },
  { "week": "2026-03-08", "n": 9 }
]

Each label is the YYYY-MM-DD local date the period starts on — for hour the local date and hour, YYYY-MM-DDTHH:00 (quote it in $having: $having=h>='2026-03-29T05:00'). /meta advertises the supported units (bucketUnits) and the fields that accept a bucket (fields[path].bucketable). See Calendar Buckets for labels, time zones, eligible fields, gap filling and the 400 / 501 errors.

Limitations

Restrictions

Cannot combine with $with — using $groupBy and $with together returns 400
Only aggregate controls are accepted (plus $actions, which grouped rows ignore); any other is a 400 Unknown aggregate control "$x" (since 0.1.168)
Plain fields in $select must also appear in $groupBy
Rows with a null or missing grouped value form one group on every adapter (since 0.1.132 — MongoDB used to split them into two groups)
$having keys must be aggregate aliases or $groupBy fields — $having=region>1 on a non-grouped column returns 400 with $having key "region" must be an aggregate alias or a $groupBy field (since 0.1.128)
Grouped keys of a flattened nested object come back nested — $groupBy=stats.views returns { "stats": { "views": 10 }, "cnt": 2 } on every adapter (since 0.1.128)
$count on an aggregate query returns [{ "count": N }] where N is the number of groups that survive $having — $groupBy=category&$select=category,sum(amount):total&$having=total>200&$count counts the categories over 200 (since 0.1.129; before, SQL adapters ignored $having in the count and MongoDB returned 0 for an alias-based $having)

Nested Writes via HTTP

POST, PUT, and PATCH request bodies can contain nested relation data. The controller processes nested objects through the deep operations pipeline.

Example — insert a project with tasks:

bash

curl -X POST http://localhost:3000/projects/ \
  -H "Content-Type: application/json" \
  -d '{
    "title": "New Project",
    "tasks": [
      { "title": "Task 1", "status": "todo" },
      { "title": "Task 2", "status": "todo" }
    ]
  }'

The controller inserts the project first, then creates the related tasks with the correct foreign key values.

For full details on how nested data is processed across foreign keys, see Relations — Deep Operations.

Next Steps

Customization — Hooks for search, access control, and extending controllers
Relations — Loading — Programmatic relation loading
Text Search — Search annotations and programmatic API
Vector Search — Vector search annotations and programmatic API

Relations & Search in URLs ​

Relation Loading ($with) ​

Basic Usage ​

Multiple Relations ​

With Controls ​

Nested Relations ​

Filtered Relations ​

Validation ​

Text Search ($search, $index, $fuzzy, $rank) ​

Fallback without native search ​

Basic Search ​

Named Search Index ​

Fuzzy Tolerance (MongoDB Atlas) ​

Unranked Search ​

Combining with Filters ​

Paginated Search ​

Vector Search ($vector, $threshold, $search) ​

How It Works ​

Basic Usage ​

With Threshold ​

Combining with Filters ​

The computeEmbedding Hook ​

Geo Search ($center, $maxDistance, $minDistance) ​

Aggregation ($groupBy) ​

Basic Usage ​

With Filters ​

Without $groupBy — totals over the filtered set ​

With Sorting ​

Aggregate Functions ​

Arithmetic and first / last ​

Distinct combinations and conditions ​

Two-level aggregates ($from) ​

Calendar Buckets ​

Limitations ​

Nested Writes via HTTP ​

Next Steps ​

Relations & Search in URLs

Relation Loading ($with)

Basic Usage

Multiple Relations

With Controls

Nested Relations

Filtered Relations

Validation

Text Search ($search, $index, $fuzzy, $rank)

Fallback without native search

Basic Search

Named Search Index

Fuzzy Tolerance (MongoDB Atlas)

Unranked Search

Combining with Filters

Paginated Search

Vector Search ($vector, $threshold, $search)

How It Works

Basic Usage

With Threshold

Combining with Filters

The computeEmbedding Hook

Geo Search ($center, $maxDistance, $minDistance)

Aggregation ($groupBy)

Basic Usage

With Filters

Without `$groupBy` — totals over the filtered set

With Sorting

Aggregate Functions

Arithmetic and first / last

Distinct combinations and conditions

Two-level aggregates ($from)

Calendar Buckets

Limitations

Nested Writes via HTTP

Next Steps