Skip to content

sql-reference.mdx

Latest commit

 

History

History
120 lines (94 loc) · 5.02 KB

sql-reference.mdx

File metadata and controls

120 lines (94 loc) · 5.02 KB
Raw
title SQL reference for Infino queries and search functions
icon database
description Query Infino with SQL via query_sql, covering the supported dialect, the bm25_search, vector_search, and hybrid_search table functions, and how to compose them.

query_sql runs SQL across every table in the catalog. It is read-only: use it to filter, aggregate, and join your rows, and to run Infino's search table functions (BM25, vector, hybrid, and exact/token match) as relations you compose with SQL. Mutate data with append / update / delete (see Tables), not SQL.

rows = db.query_sql("SELECT body FROM docs WHERE source = 'help-center'")
const rows = db.querySql("SELECT body FROM docs WHERE source = 'help-center'");
let rows = db.query_sql("SELECT body FROM docs WHERE source = 'help-center'")?;

Dialect

Infino executes SQL on Apache DataFusion. The dialect is ANSI-style and Postgres-leaning. You get SELECT, WHERE, JOIN (across tables), CTEs (WITH), GROUP BY with aggregates, window functions, ORDER BY, LIMIT, and the usual scalar/aggregate functions.

Search table functions

Call a search function in the FROM clause as a relation. Every one takes a leading table-name argument (a string literal naming the catalog table to search) and returns that table's _id, its scalar columns, and a score column. The ranked functions return rows best first. Their scores point in different directions, so when you re-sort after a join or filter, match the function:

  • bm25_search and hybrid_search: higher is better, so ORDER BY score DESC. A hybrid score is the reciprocal rank fusion of the two rankings, not a BM25 or distance value.
  • vector_search: score is the distance under the index's metric (cosine distance for a cosine index), so lower is closer: ORDER BY score ASC.
Function Signature Returns
bm25_search bm25_search('table', 'column', 'query', k [, 'mode']) ranked BM25 hits
bm25_search_prefix bm25_search_prefix('table', 'column', 'prefix', k) ranked prefix (autocomplete) hits
vector_search vector_search('table', 'column', vector, k) ranked vector-kNN hits
hybrid_search hybrid_search('table', 'text_col', 'query', 'vector_col', vector, k) BM25 + vector, fused with RRF
token_match token_match('table', 'column', 'query' [, 'mode']) unranked rows containing the token(s)
exact_match exact_match('table', 'column', 'value') unranked rows where column equals value
  • mode (BM25, token): 'or' (default, match any term) or 'and' (require all terms).
  • vector: the query embedding, as a comma-separated string literal ('0.12,0.04,-0.31,...') or a SQL array literal ([0.12, 0.04, -0.31, ...]).
  • token_match / exact_match are unranked: score is present (for schema uniformity) but constant 0.0, and order is unspecified, so use ORDER BY / LIMIT. Both read the full-text index, so column must be one you declared as an FTS column; naming an unindexed column fails with unknown FTS column. To match on a column without an index, filter it with plain SQL (WHERE doc_id = '42').

Examples

-- BM25 keyword search
SELECT _id, body, score
FROM bm25_search('docs', 'body', 'cancel subscription', 10)
ORDER BY score DESC;

-- require all terms (AND)
SELECT _id, score FROM bm25_search('docs', 'body', 'error timeout', 10, 'and');

-- vector kNN, query vector as a string literal (score is a distance: lower is closer)
SELECT _id, score
FROM vector_search('docs', 'embedding', '0.12,0.04,-0.31, ...', 10)
ORDER BY score ASC;

-- hybrid (BM25 + vector, fused with reciprocal-rank fusion)
SELECT _id, body, score
FROM hybrid_search('docs', 'body', 'cancel subscription', 'embedding', '0.12,0.04, ...', 10)
ORDER BY score DESC;

-- exact id lookup (unranked)
SELECT _id, body FROM exact_match('docs', 'doc_id', '42');

Composing search with SQL

Each function returns a relation, so you can filter, join, and aggregate over its results:

-- post-filter BM25 hits by a scalar column
SELECT s._id, s.score
FROM bm25_search('docs', 'body', 'billing', 50) AS s
WHERE s.source = 'help-center'
ORDER BY s.score DESC
LIMIT 10;

-- join search hits to another table
SELECT h._id, u.name
FROM hybrid_search('docs', 'body', 'refund', 'embedding', '0.1,0.2, ...', 20) AS h
JOIN users AS u ON u.id = h.author_id;
SQL is read-only. `INSERT` / `UPDATE` / `DELETE` / `CREATE` are not available; create tables and mutate rows through the API (`create_table`, `append`, `update`, `delete`). For a pushdown text pre-filter on vector search (rather than a SQL post-filter), see the `filter` option in the [Search guide](/guides/search#vector-search).

See also