Skip to content
BoilStream

Search query reference

reference
TL;DR

Query the generated search table with multilake_search and select supported columns. The function returns a relevance score, but its optional row limit does not select the highest-scoring matches. To rank the complete result set, apply SQL ordering and a limit outside the function. Search visibility follows index progress.

ComparisonRank the matching set before taking the final page
Rank the matching set before taking the final pageA full-text query ranks the matching set, orders it and applies the final SQL LIMIT. An early candidate limit changes the set being ranked and can change the result.Order of workMeaningFinal SQL LIMITMatch → rank → order →limitTop results from the rankedsetEarly candidate capCap → rank a subsetPotentially differentresultsRank the matching set before taking the final pageA full-text query ranks the matching set, orders it and applies the final SQL LIMIT. An early candidate limit changes the set being ranked and can change the result.Order ofworkMeaningFinal SQLLIMITMatch → rank→ order →limitTop resultsfrom theranked setEarlycandidatecapCap → rank asubsetPotentiallydifferentresults
Use the documented search syntax. A candidate cap and the final SQL LIMIT are not interchangeable.
Jump to reference ↓

Querying

Section titled “Querying”
Section titled “multilake_search()”

Use the multilake_search() table function to run full-text queries against the shadow tantivy table:

SQL
SELECT * FROM multilake_search(
  'my_catalog__stream',           -- catalog name
  'documents__tantivy_idx',       -- shadow table name
  'distributed systems'           -- search query
);

The function returns columns from the search table plus a _score relevance column. Use the supported column types below. An optional fourth parameter limits the number of results; it does not select the highest-scoring matches:

SQL
-- Return at most 10 results
SELECT * FROM multilake_search('my_catalog__stream', 'documents__tantivy_idx', 'Rust', 10);

Query syntax

Section titled “Query syntax”

Queries support field-prefixed syntax to target specific indexed columns:

SQL
-- Search the "title" field only
SELECT * FROM multilake_search('my_catalog__stream', 'documents__tantivy_idx', 'title:Rust');


-- Search "body" for a phrase
SELECT * FROM multilake_search('my_catalog__stream', 'documents__tantivy_idx', 'body:"distributed systems"');


-- Unqualified terms search across all indexed string fields
SELECT * FROM multilake_search('my_catalog__stream', 'documents__tantivy_idx', 'performance');

Example: search with relevance ranking

Section titled “Example: search with relevance ranking”
SQL
SELECT id, title, body, _score
FROM multilake_search('my_catalog__stream', 'documents__tantivy_idx', 'title:Rust')
ORDER BY _score DESC
LIMIT 10;

To rank the full result set, apply SQL ORDER BY and LIMIT as above without the function’s fourth argument.

Column type support

Section titled “Column type support”

BoilStream automatically maps Arrow column types to tantivy field types:

Input data Search behavior
String columns selected in tantivy_text_fields Tokenized for full-text queries
Other string columns Exact-match queries
Integers and floating-point values Numeric queries
Timestamps Date queries
Booleans Boolean filters
Binary, nested values and other unsupported types (including decimals and dates without a time) Not indexed; selecting these columns can fail

The tantivy_text_fields parameter controls which string columns are tokenized for full-text search versus stored as exact-match strings. For tables with unsupported types, select supported columns explicitly rather than SELECT *. The search table excludes the source table’s automatic ingestion metadata columns.