Full-Text Search in Your Database — BM25, No Separate Engine

Adding search usually means adding infrastructure: an external engine, an indexing pipeline, and a sync job that drifts. In Aito v2, any Text column is full-text searchable in place — analyzed, BM25-ranked, and composable with ordinary database conditions in the same JSON query. The same index also powers prediction from text, so search and inference share one copy of the data.

The $search operator

A Lucene-subset query language: bare terms (AND), "quoted phrases", OR, NOT / -term, and ( … ) groups.

{
  "from": "products",
  "where": { "name": { "$search": "(milk OR bread) AND NOT chocolate" } },
  "select": ["name"],
  "limit": 20
}

{ "offset": 0, "total": 11, "hits": [
  { "name": "Fazer Puikula fullcorn rye bread 330g" },
  { "name": "Pirkka Finnish semi-skimmed milk 1l" },
  { "name": "Valio semi-skimmed milk 1l" } ] }

It composes with plain conditions — full-text match and structured filter in one where:

{
  "from": "products",
  "where": {
    "name": { "$search": "milk OR juice" },
    "category": "104"
  },
  "select": ["name", "category"]
}

Relevance-ranked search with highlighting

The search query type ranks by BM25 — the same scorer Lucene and Elasticsearch use — and $highlight marks the matched tokens for your UI:

{
  "from": "products",
  "search": { "field": "name", "text": "milk" },
  "select": ["name", "$score", "$highlight"],
  "limit": 3
}

$why on a search result breaks the score down per token; $matches reports the matched token positions.

From search to understanding

Because the analyzed text is a first-class column, the same tokens are prediction evidence — this is where an embedded search engine beats a bolted-on one:

{
  "from": "products",
  "where": { "name": { "$match": "semi-skimmed milk" } },
  "predict": "category",
  "select": ["$value", "$p", "$why"],
  "limit": 1
}

One system answers "which rows match" (search), "which category is this" (classification), and "why" (explanation) — over the same live data, with no sync pipeline.

Why Aito for this

  • Zero-infrastructure: per-language analyzers configured in the schema ("type": "Text", "analyzer": "English"); the index is built and merged with the data.
  • Exact, reproducible results — real BM25 scores, deterministic ties.
  • Composability: $search is a where proposition like any other — it works under predict, in population restrictions, and alongside links.

Related: Query Reference · Vector Search for semantic (embedding-based) retrieval · Sandbox

← All v2 use cases