Model / text · All operations

Search text by relevance

Full-text search ranked by relevance.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today, with the fts extension.

Shape of the result

fewer rows, same columns

Level

needs a DuckDB extension

The DuckDB step

Full-text search ranked by BM25

It reads the orders table the proofs share. Create it first:

SQL

CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL

INSTALL fts; LOAD fts;
CREATE TABLE docs AS SELECT order_id, customer || ' ordered a ' || product || ' in the ' || coalesce(region, 'unknown') || ' region' AS body FROM orders;
PRAGMA create_fts_index('docs', 'order_id', 'body');
CREATE TEMP TABLE result AS
SELECT * FROM (SELECT order_id, body, fts_main_docs.match_bm25(order_id, 'widget north') AS score FROM docs) WHERE score IS NOT NULL;
SELECT order_id, body, round(score, 4) AS score FROM result ORDER BY score DESC LIMIT 3;
SELECT CASE WHEN ((SELECT arg_max(order_id, score) = 1 AND count(*) FILTER (WHERE body NOT LIKE '%widget%' AND body NOT LIKE '%north%') = 0 FROM result)) THEN 'as expected' ELSE error('the order that is both a widget and in the north did not rank first') END AS verdict;

It needs the fts extension.
The statement checks its own result and raises an error when it is wrong: “the order that is both a widget and in the north did not rank first”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Duckle (1)

Full-Text Searchread 2026-09-25
BM25 keyword search over text columns with a full-text index

KNIME Analytics Platform (1)

KNIME Indexing and Searching (extension)read 2026-09-27
Extension outside the base install: 4 current nodes: Index Query; Index Reader; Index Writer; Table Indexer

subtoken (1)

match_bm25read 2026-09-27
Macro, created per index, that scores each document id against a query string by Okapi BM25

Background

In full-text search, a search engine examines the words in stored documents to find matches for a user's query.^[1] Okapi BM25 is a ranking function used by search engines to estimate the relevance of documents to a given search query.^[2] In information retrieval, relevance denotes how well a retrieved document or set of documents meets the information need of the user.^[3]

When to use it

Reach for a relevance search when the question is which records best answer a few words, such as the support notes or product descriptions about a topic, rather than which records hold an exact string. (Meridian's view) When the number of documents or queries is large, full-text search is typically divided into two tasks, indexing and searching, and queries run against the index rather than the original documents.^[1] With a small number of documents, a full-text-search engine can scan the contents of each document directly for every query, a strategy known as serial scanning.^[1]

Precision measures the proportion of returned results that are relevant, and recall the proportion of relevant results that a search returns.^[1] In full-text search there is generally a trade-off between precision and recall: increasing precision can reduce overall recall, while increasing recall may lower precision.^[1] Full-text search can retrieve many documents that are not relevant to the intended query, and the retrieval of irrelevant documents is often caused by the ambiguity of natural language.^[1] Because there are often multiple ways to refer to an entity or concept, full-text search may fail to retrieve an item if the exact term is not used in the query.^[1] Read the top of a ranking before trusting it, and try the other words a writer might have used for the same thing. (Meridian's view)

DuckDB's Full-Text Search extension adds a PRAGMA, create_fts_index, that builds an index over a table from a document identifier column and the text columns to be indexed.^[4] In DuckDB, create_fts_index by default ignores all escaped and non-alphabetic lowercase characters, through its ignore pattern.^[4] Check the ignore pattern before searching text whose meaning sits in digits, such as part numbers or postcodes. (Meridian's view) By default, a DuckDB full-text index does not update automatically when its input table changes.^[4] A DuckDB full-text index created with incremental = 1 is maintained with triggers after inserts and deletes on the input table, which requires the document identifier column to be declared NOT NULL or PRIMARY KEY.^[4] A DuckDB full-text index created without incremental maintenance is recreated to refresh it.^[4] Rebuild or incrementally maintain the index whenever the table changes, so the scores describe the rows as they stand. (Meridian's view)

The method

An inverted index stores a mapping from content, such as words, to its locations in a document or a set of documents, and its purpose is to allow fast full-text searches at the cost of more processing when a document is added.^[5] A full-text indexer records each term found in a document and may note its position within the text.^[1] Common words such as "the" or "and", known as stop words, are omitted from a full-text index because they add little value to search results.^[1] Some indexers also perform stemming, which reduces words to their base form, so that "drives", "drove" and "driven" may all be indexed under "drive".^[1] Full-text-search systems typically include filtering to increase precision and stemming to increase recall.^[1]

Okapi BM25 is based on the probabilistic retrieval framework developed in the 1970s and 1980s by Stephen E. Robertson, Karen Spärck Jones and others.^[2] The fuller name, Okapi BM25, includes the name of the first system to use the function, the Okapi information retrieval system, implemented at City University, London in the 1980s and 1990s.^[2] BM25 is a bag-of-words retrieval function that ranks a set of documents based on the query terms appearing in each document, regardless of their proximity within the document.^[2] The BM25 score of a document sums, over the query's terms, each term's inverse document frequency (IDF) weight times a factor built from the term's count in the document, the document's length in words and the average document length in the collection.^[2] The IDF weight of a query term measures the rarity of documents containing it, and is usually computed from the total number of documents in the collection and the number that contain the term.^[2] Karen Spärck Jones conceived inverse document frequency in 1972 as a statistical interpretation of term specificity.^[6] BM25's free parameters k1 and b are usually chosen, in the absence of an advanced optimisation, with k1 between 1.2 and 2.0 and b = 0.75.^[2] At the extreme values of b, BM25 turns into ranking functions known as BM11, for b = 1, and BM15, for b = 0.^[2] BM25F is a modification of BM25 in which a document is considered to be composed of several fields, such as headlines, main text and anchor text, with possibly different degrees of importance.^[2]

In DuckDB, create_fts_index by default stems words with the porter stemmer, removes a pre-defined list of 571 English stopwords, converts text to lowercase and removes accents.^[4] DuckDB's create_fts_index builds the index under a newly created schema named after the input table, so an index on main.table_name lives in the schema fts_main_table_name.^[4] When DuckDB builds a full-text index, it creates a retrieval macro, match_bm25, that can be used to search the index, taking a document identifier and a query string.^[4] DuckDB's match_bm25 takes the Okapi BM25 parameter k1 as its k argument, defaulting to 1.2, and b, defaulting to 0.75.^[4] DuckDB's match_bm25 has a conjunctive option which, when set, retrieves a document only if all terms in the query string are present.^[4]

Compared with the alternatives

In information retrieval, term frequency–inverse document frequency (tf–idf) is a measure of importance of a word to a document in a collection, adjusted for the fact that some words appear more frequently in general.^[6] One of the simplest ranking functions is computed by summing the tf–idf for each query term, and many more sophisticated ranking functions are variants of this simple model.^[6] BM25 and its newer variants, such as BM25F, represent tf–idf-like retrieval functions used in document retrieval.^[2]

In DuckDB, the LIKE expression returns true if the string matches the supplied pattern, and a pattern with no percent signs or underscores makes LIKE act like the equals operator.^[7] In full-text search, Boolean queries can increase precision: the AND operator retrieves only documents containing all specified terms, and NOT excludes documents containing a term.^[1] Use LIKE or a Boolean query for exact codes and fragments, and a relevance search for questions put in words. (Meridian's view)

Sources

[1]Full-text search - Wikipediasecondary source
[2]Okapi BM25 - Wikipediasecondary source
[3]Relevance (information retrieval) - Wikipediasecondary source
[4]Full-Text Search Extension – DuckDBdocumentation
[5]Inverted index - Wikipediasecondary source
[6]tf–idf - Wikipediasecondary source
[7]Pattern Matching – DuckDBdocumentation