Full-text search

Full-text search finds words and phrases in text. Choose ranked search when order matters, or boolean and phrase modes when you need a specific match.

There are two setup paths: a full-text index attached to a table, and explicit document-index requests. This page explains both. Keep the same document ID when linking a search result back to its source record.

Table indexes follow your writesCopy Anchor Link

Current engine releases maintain table full-text indexes from row inserts, updates and deletes. Verify the installed build and index compatibility on your deployment. Affected older indexes need planned recreation across the 18 October compatibility boundary. The examples below cover the separate document-index API.

Plan and verify an index upgrade →

The explicit document-index API below uses its own runtime endpoint. You index text under (table, field, doc_id), then search. The doc_id is what links search hits back to your rows - use the row's primary key.

:table and :field are opaque path segments - they are not validated against any schema. They must match exactly between the index call and the query call. Index under shop.products and query shop_products and you get a silent empty result, not an error. The default search mode is boolean - omit mode and you get AND-of-terms.

Existing table indexesCopy Anchor Link

Table full-text indexes are maintained on row writes. The explicit document-index API below is a separate workflow. Full-text and geo indexes created before 18 October 2026 require a one-time recreation, as described in the 19 October release notes. Validate indexed results after rebuilding; recreating an index is operational work, not a documentation-only change.

Client examples below describe the referenced interfaces. Use the SDK directory to check which packages are published; source examples are not installation instructions for an unreleased client.

1. Index a text.Copy Anchor Link

Tell OriginChainDB to make a piece of text searchable. Re-indexing the same doc_id replaces the old text in the same write - no stale matches.

curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.products/description" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "doc_id": "sku-9281",
    "text":   "Lightweight road runner with a carbon plate, designed for marathon pace."
  }'

HTTP/1.1 201 Created

(empty body)

A successful index returns 201 Created with no body. There is no token count or confirmation JSON - check the status code, not the body.

• Indexing only one of several fields. If you want users to search "Wireless headphones" and match products whose title or description contains those words, you need to either concatenate both fields into one text before indexing, or index each field separately and union the results.
• Forgetting to re-index on updates. When using this explicit document-index endpoint, re-submit the entry when its source changes. Table full-text indexes are maintained automatically on row writes; this instruction applies to entries your application indexes separately.

2. BM25 - ranked search.Copy Anchor Link

Return the top-k documents ranked by relevance to the query. This is what most users mean when they say "search". Rare query words count more than common ones; documents where the query words appear more often (relative to length) rank higher.

curl "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.products/description?q=carbon+marathon&mode=bm25&k=10" \
  -H "Authorization: Bearer $OC_TOKEN"

[
  { "doc_id": "sku-9281", "score": 9.42 },
  { "doc_id": "sku-3140", "score": 4.18 }
]

Plain BM25 returns a bare array of { doc_id, score }, best first - no { "mode": ..., "hits": [...] } wrapper. The wrapper object only appears when you add highlight=true or facets= (then you get { "hits": [...], "facets": {...} }); explain=true returns a separate scoring-breakdown object.

Query params — all BM25-only; ignored in boolean / phrase.

highlight=true requires the doc text to have been stored first via POST /fts/:t/:f/doc. All of k, fuzzy, highlight, facets, and explain are silently ignored in boolean and phrase modes.

3. Boolean AND - every word must match.Copy Anchor Link

Return every document that contains all the query words, in any order, with no ranking. Fast token-presence check - use when you don't need relevance scoring.

curl "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.products/description?q=carbon+marathon&mode=boolean" \
  -H "Authorization: Bearer $OC_TOKEN"

["sku-9281", "sku-3140"]

A bare array of doc_id strings (sorted lexicographically) - no score field and no wrapper object. If you need ordering by relevance, use BM25.

4. Phrase - exact word order.Copy Anchor Link

Match documents that contain the query words contiguously, in the exact order given. Use for branded phrases ("New York Times"), product model numbers, log message templates.

curl "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.products/description?q=carbon+plate&mode=phrase" \
  -H "Authorization: Bearer $OC_TOKEN"

["sku-9281"]

Same shape as boolean - a bare array of doc_id strings. Phrase just narrows which docs qualify.

5. End-to-end - index, search, enrich.Copy Anchor Link

The following sequence writes three documents, searches them and requests highlighted matches. These are explicit engine endpoint examples; set the host, tenant and credential for that interface first. Check the instance capability before using ranked search, phrase search or the JSON query DSL.

Step 1 — index three docs.

# 1) Index three docs. Each POST returns 201 with an EMPTY body.
curl -sS -o /dev/null -w "%{http_code}\n" \
  -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.products/description" \
  -H "Authorization: Bearer $OC_TOKEN" -H "Content-Type: application/json" \
  -d '{ "doc_id": "p001", "text": "Wireless over-ear headphones with active noise cancellation" }'
# → 201

curl -sS -o /dev/null -w "%{http_code}\n" \
  -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.products/description" \
  -H "Authorization: Bearer $OC_TOKEN" -H "Content-Type: application/json" \
  -d '{ "doc_id": "p002", "text": "Wired earbuds, no noise cancellation" }'
# → 201

curl -sS -o /dev/null -w "%{http_code}\n" \
  -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.products/description" \
  -H "Authorization: Bearer $OC_TOKEN" -H "Content-Type: application/json" \
  -d '{ "doc_id": "p003", "text": "USB-C charging cable, 2 metres" }'
# → 201

Step 2 — boolean query (default mode).

# 2) Boolean query (the DEFAULT mode). Bare array of doc_id strings.
curl -sS -G "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.products/description" \
  -H "Authorization: Bearer $OC_TOKEN" \
  --data-urlencode "q=wireless noise"
# → ["p001"]      (only p001 has BOTH "wireless" AND "noise")

Step 3 — bm25 ranked query.

# 3) BM25 ranked query. Bare array of { doc_id, score }, best first.
curl -sS -G "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.products/description" \
  -H "Authorization: Bearer $OC_TOKEN" \
  --data-urlencode "q=noise cancellation" \
  --data-urlencode "mode=bm25" \
  --data-urlencode "k=10"
# → [ { "doc_id": "p001", "score": 6.31 }, { "doc_id": "p002", "score": 2.04 } ]

Step 4 — enrich with highlights.

# 4) Enrich with highlights. First store the doc text (highlights read it),
#    THEN ask for highlight=true. Now the response is an OBJECT, not an array.
curl -sS -o /dev/null -w "%{http_code}\n" \
  -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.products/description/doc" \
  -H "Authorization: Bearer $OC_TOKEN" -H "Content-Type: application/json" \
  -d '{ "doc_id": "p001", "text": "Wireless over-ear headphones with active noise cancellation" }'
# → 201

curl -sS -G "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.products/description" \
  -H "Authorization: Bearer $OC_TOKEN" \
  --data-urlencode "q=noise cancellation" \
  --data-urlencode "mode=bm25" \
  --data-urlencode "highlight=true"
# → {
#     "hits": [
#       { "doc_id": "p001", "score": 6.31,
#         "highlights": { "description": ["…active <em>noise</em> <em>cancellation</em>"] } }
#     ]
#   }

6. Analyzer + languages.Copy Anchor Link

Stemming, lemmatization, diacritic folding, and stopword removal are implemented in the engine but not yet selectable through the HTTP API. There is no language or analyzer query parameter today. The analyzer the API actually uses is Unicode tokenize + lowercase, and nothing else. Concretely: q=runs will not match a document that says "running", and q=cafe will not match "café". Plan your indexing around exact tokens. The one transform that is live is synonyms (see below).

What runs todayCopy Anchor Link

• Unicode tokenize. Text is split into words by the Unicode word-boundary rules (UAX #29), so it works across scripts.
• Lowercase. Every token is lowercased, so Wireless and wireless match. This is the only normalisation applied.
• Synonyms. If you install a per-(table, field) synonym map via POST /fts/:t/:f/synonyms, members of a class are treated as equivalent at both index time and BM25 query time. This is the one customer-controlled analyzer feature that is wired through. See FTS runtime calls.

Built in the engine, not yet exposed (roadmap)Copy Anchor Link

The following analyzer stages exist in the engine but cannot be turned on from the API yet. They are listed so you know what is coming, not what you can call today.

Languages the engine has stemmers for (not yet selectable).

ArabicDanishDutchEnglishFinnishFrenchGermanHungarianItalianNorwegianPortugueseRomanianRussianSpanishSwedishTamilTurkishHindi

There is no per-field analyzer knob you can set today. The runtime calls that are live - plain / JSON-aware index, doc store, synonyms, stopword override - are documented on this page and in FTS runtime calls.

7. Examples.Copy Anchor Link

Search — a single term.Copy Anchor Link

Search is a GET on the same path you indexed to, with the query in ?q=. mode=bm25 is what you want when you care about ranking.

curl "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.orders/notes?q=delivery&mode=bm25&k=10" \
  -H "Authorization: Bearer $OC_TOKEN"

Response — mode=bm25.

[
  { "doc_id": "01JTRX9KQ3YH8K2WMX0F5JZAB7", "score": 9.4213 },
  { "doc_id": "01JTRX9KQ3YH8K2WMX0F5JZAB9", "score": 6.1077 }
]

A bare array of { doc_id, score }, highest score first. k caps the list and defaults to 10.

This endpoint returns four different shapes depending on the parameters. boolean and phrase give you a bare array of doc_id strings; bm25 gives an array of objects; adding highlight or facets wraps it all in an object with a hits key; and explain=true returns a scoring report instead. Inspect the response shape before reading its fields; the request options determine which one is returned.

What the query syntax really is.Copy Anchor Link

This is worth being blunt about, because it is easy to assume otherwise: ?q= is not a query language. There is no parser. The string is tokenized into words — Unicode word segmentation, then lowercased — and every token becomes a term. All punctuation is discarded.

The practical consequence is that operators you might type do not work, and fail silently rather than erroring. rush AND delivery searches for three terms — rush, and, delivery. Quotes around a phrase are dropped.

How multiple terms combine — it depends on the mode.Copy Anchor Link

This is the important distinction, and it is not configurable:

Boolean — every term required.

# mode=boolean is the DEFAULT when ?mode= is omitted.
# Every term must be present. Returns a bare array of doc_ids, unranked.
curl "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.orders/notes?q=rush+delivery" \
  -H "Authorization: Bearer $OC_TOKEN"

["01JTRX9KQ3YH8K2WMX0F5JZAB7", "01JTRX9KQ3YH8K2WMX0F5JZAC1"]

An unknown mode falls back to boolean, silently.

?mode=ranked, ?mode=BM25 or any typo is not an error — it takes the default branch and runs a boolean AND. If you get back an array of bare strings when you expected scores, check the spelling of mode first. The accepted values are lowercase boolean, bm25 and phrase.

The dashboard's search box is different.

The console workbench accepts a one-line table:field terms shorthand in Search mode. That colon syntax is parsed by the console, which then calls the endpoint documented here — it is not something the engine understands. Don't put shop.orders:notes into ?q=. See Run queries from the dashboard.

Phrase queries.Copy Anchor Link

mode=phrase requires the terms to appear adjacent and in the order given. The quotes you would type in another search engine are not syntax here — the mode is the switch.

# Terms must appear adjacent, in this order. Quotes are NOT syntax -
# they would simply be discarded by the tokenizer. Use mode=phrase.
curl "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.orders/notes?q=signed+by+recipient&mode=phrase" \
  -H "Authorization: Bearer $OC_TOKEN"

Phrase results come back as an unranked array of doc_id strings — position matching selects the documents, but this mode does not score them. If you need ranking as well as adjacency, use the phrase clause inside the DSL, which selects on positions and then ranks with BM25.

Fuzzy matching.Copy Anchor Link

Two ways in, both bm25-only: a whole-query budget via ?fuzzy=N, or a per-term ~N suffix. Putting a ~ anywhere in q switches the query onto the fuzzy path automatically.

# Whole-query budget via ?fuzzy= (bm25 only, 0-3)
curl "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.orders/notes?q=delivary&mode=bm25&fuzzy=1" \
  -H "Authorization: Bearer $OC_TOKEN"

# Or per-term inline with ~N. A bare ~ means distance 2.
curl "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.orders/notes?q=delivary~1+recipient&mode=bm25" \
  -H "Authorization: Bearer $OC_TOKEN"

• Edit distance is capped at 3. Higher is a 400: fuzzy edit_distance 5 exceeds MAX_EDIT_DISTANCE 3.
• A bare term~ with no number means distance 2. term~0 is an exact match.
• Each term expands to at most 50 dictionary candidates.
• Distance 1 catches most real typos. Distance 2 and 3 expand aggressively and will pull in unrelated words — measure before shipping either.
• The HTTP endpoint accepts fuzzy syntax in the query. Check the installed client version before using a convenience parameter.

Ranking, scoring and explain.Copy Anchor Link

mode=bm25 scores each document as the sum of its query terms' contributions. Each contribution combines three things:

• Inverse document frequency. A term in few documents is worth more than one in many. This is why a rare word dominates a query.
• Term frequency, with diminishing returns. The fifth occurrence adds much less than the second. k1 controls how fast that saturates.
• Length normalisation. A hit in a short document counts for more than the same hit buried in a long one. b controls how strongly.

Defaults are k1 = 1.2 and b = 0.75 — the standard Lucene values. On the ?q= surface they are fixed; they can only be overridden through the DSL's params object. Scores are relative within one result set — never compare a score across two different queries.

The explain parameter.Copy Anchor Link

Add explain=true to a bm25 query to get the full arithmetic instead of the hits. This is a query parameter on the search route — there is no separate explain endpoint.

curl "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.orders/notes?q=rush+delivery&mode=bm25&k=5&explain=true" \
  -H "Authorization: Bearer $OC_TOKEN"

{
  "query_terms": ["rush", "delivery"],
  "n_total":     1204,
  "avgdl":       11.6,
  "k1":          1.2,
  "b":           0.75,
  "hits": [
    {
      "doc_id": "01JTRX9KQ3YH8K2WMX0F5JZAB7",
      "score":  9.4213,
      "terms": [
        { "term": "rush",     "df": 42,  "idf": 3.361, "tf": 1.0,
          "doc_len": 5, "contribution": 5.9102 },
        { "term": "delivery", "df": 310, "idf": 1.362, "tf": 1.0,
          "doc_len": 5, "contribution": 3.5111 }
      ]
    }
  ]
}

n_total is the corpus size and avgdl the average document length — the two corpus statistics the formula needs. Per hit, terms is sorted by contribution descending, so the first entry is the term that actually won the document its place. Length normalisation is folded into contribution; doc_len is the raw token count.

• Explain works only with mode=bm25, and overrides highlight and facets if you pass them together.
• It always reports the default k1 and b — you cannot explain a custom-tuned score, and the DSL endpoint has no explain of its own.
• Explain takes the exhaustive scoring path, so it is slower than the equivalent ranked query. It is a debugging tool, not a production one.
• Call the HTTP endpoint directly when your installed client does not expose explain.

Highlights and facets.Copy Anchor Link

Both features need the document's text stored, which the plain index call does not do. Send it to the /doc variant instead, along with any facet values you want to aggregate on.

# Store the doc text plus per-facet values. Required before highlight=true
# or facets= will return anything.
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.orders/notes/doc" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "doc_id": "01JTRX9KQ3YH8K2WMX0F5JZAB7",
    "text":   "rush delivery, signed by recipient",
    "facets": { "status": ["paid"], "channel": ["web"] }
  }'

Then ask for them at query time:

curl "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.orders/notes?q=rush&mode=bm25&k=5&highlight=true&facets=status,channel" \
  -H "Authorization: Bearer $OC_TOKEN"

Response — note the object wrapper.

{
  "hits": [
    {
      "doc_id": "01JTRX9KQ3YH8K2WMX0F5JZAB7",
      "score":  9.4213,
      "highlights": { "notes": ["<em>rush</em> delivery, signed by recipient"] }
    }
  ],
  "facets": {
    "status":  [ { "value": "paid", "count": 12 }, { "value": "refunded", "count": 3 } ],
    "channel": [ { "value": "web",  "count": 11 }, { "value": "app",      "count": 4 } ]
  }
}

res = db.fts.search(
    "shop.orders", "notes", "rush",
    mode="bm25", k=5, highlight=True, facets=["status", "channel"],
)
for hit in res.hits:
    print(hit.doc_id, hit.score, hit.highlights)
for value, bucket in res.facets.items():
    print(value, [(b.value, b.count) for b in bucket])

• Highlights come back as raw <em> markup around matched terms. Escape or sanitise before rendering.
• Facets aggregate; they do not filter. facets=status tells you the distribution across the hit set — it does not narrow it. For real filtering use the DSL.
• At most 1000 distinct values are tracked per facet field.
• Use the HTTP request shown above when your installed client does not expose highlights, facets or the /doc write.

Filters and multi-field — the JSON DSL.Copy Anchor Link

Everything the ?q= surface can't do — boolean composition, negation, filters, prefix and wildcard matching, multi-field search with weights, custom k1/b — lives in a JSON query DSL at POST /fts/:table/_search. Note the path takes a table only; fields are named inside the query.

curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.orders/_search" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "doc_values_field": "notes",
    "query": {
      "bool": {
        "must":     [ { "match":  { "field": "notes", "query": "rush delivery" } } ],
        "should":   [ { "term":   { "field": "notes", "value": "signed", "boost": 2.0 } } ],
        "must_not": [ { "term":   { "field": "notes", "value": "cancelled" } } ],
        "filter":   [ { "numeric_range": { "field": "amount_cents", "gte": 10000 } } ]
      }
    },
    "top_k":  20,
    "params": { "k1": 1.2, "b": 0.75 }
  }'

{
  "total": 431,
  "hits": [
    { "doc_id": "01JTRX9KQ3YH8K2WMX0F5JZAB7", "score": 14.8802 },
    { "doc_id": "01JTRX9KQ3YH8K2WMX0F5JZAC1", "score": 11.2044 }
  ]
}

total is the match count before top_k truncation, so you can render "showing 20 of 431" without a second query. top_k defaults to 10.

Fields are a named key, not a dynamic one.

If you know Elasticsearch, this is the one difference that will trip you up. Where ES writes {"term": {"notes": "rush"}}, this DSL writes {"term": {"field": "notes", "value": "rush"}}. The field is always an explicit field key.

Clause types.Copy Anchor Link

match_all, match_none, term, terms, match, phrase, prefix, wildcard, regex, range, numeric_range, fuzzy, exists, multi_match, bool, constant_score and function_score.

• bool scores must plus any matching should. filter gates without contributing score; must_not excludes.
• match defaults to OR across its terms; pass "operator": "and" to require them all.
• Every clause takes a boost, and boosts multiply down the tree — a boost: 2.0 clause inside a boost: 3.0 bool contributes 6×.
• minimum_should_match accepts an integer, a negative integer ("all but N"), or a percentage string like "75%".
• regex supports literals, character classes, repetition, alternation and grouping — but not back-references, look-around or named groups.

Multi-field search with weights.Copy Anchor Link

# Search several indexed fields at once, with per-field weights.
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.orders/_search" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "query": {
      "multi_match": {
        "fields": [
          { "field": "notes",    "boost": 2.0 },
          { "field": "customer", "boost": 1.0 }
        ],
        "query": "rush delivery",
        "type":  "best_fields",
        "tie_breaker": 0.3
      }
    },
    "top_k": 20
  }'

best_fields (the default) takes the best-scoring field plus tie_breaker × each other match; most_fields sums them all. tie_breaker defaults to 0.0 — pure winner-takes-all — and must be within [0, 1]. Every field you name must be independently indexed, or the request 404s.

Numeric filters need doc_values_field.

numeric_range, field_value_factor and decay functions read per-document values that only exist if you wrote them as facets through the /doc endpoint. You must also name the source with a top-level doc_values_field. Omit it and the request is refused with a 400 rather than quietly matching nothing — a deliberate choice, since a silent empty result is indistinguishable from "no matches".

No SQL integration.

Full-text search is not reachable from POST /sql. There is no MATCH() function and no way to put a text predicate in a WHERE clause. To combine the two, search first and then query the returned doc_ids with SQL.

Two sibling endpoints share the DSL: POST /fts/:table/_aggs for aggregations and POST /fts/:table/_suggest for prefix suggestions. Because of these routes, _search, _aggs and _suggest are reserved and cannot be used as field names. Use the HTTP reference for the exact request body, whether you send it directly or through a client.

Limits and gotchas.Copy Anchor Link

There is no pagination.

No offset, no from, no cursor. You get the top k and that is all. To show page two, raise k and slice client-side — and remember boolean and phrase mode ignore k entirely and return every match, which is why a broad boolean query on a large corpus can trip the result-size cap and return 413.

Top_k bounds the response, not the work.

A small k does not make a broad query cheap. The engine scores every matching document and ranks afterwards, so match_all or a very common single term over a large corpus is expensive however few results you ask for. A block-max optimisation skips provably-losing blocks for ranked queries, but it declines to engage in several cases — including whenever you request explain — and falls back to exhaustive scoring.

An unindexed field is an empty result on ?q=, a 404 on the DSL.

The two surfaces disagree here. _search refuses an unindexed field with a 404 explaining that an unindexed field is refused "rather than answered with an empty result you could not tell from 'nothing matched'". The ?q= route has no such check and returns an empty array, so a typo in the path looks exactly like a genuine miss.

Measure memory use and query latency with your own document lengths, fields and query mix. An index that fits a small sample may not fit the full corpus. Load a representative sample, record resident memory and latency, then repeat at the expected production size.

What needs the capability enabled.

Some full-text operations require the Full-Text Pro engine capability. Check the instance capabilities and enabled configuration before using ranked search, phrase search or the JSON DSL. If the response names a missing capability, confirm availability for that deployment instead of retrying the same request.

Caps refuse rather than truncate.

When a query exceeds an expansion or clause limit the engine returns 400 with a message naming the limit and suggesting a fix — it never silently returns a partial result set. A truncated expansion would drop matching documents without telling you, so the refusal is deliberate.

Use the older-index recreation plan for inventory, backup/restore proof, rehearsal and upgrade verification.