spiceai/docs

spiceai/

docs

Help Login

trunk

Edit on GitHub

Fork

/docs/website/versioned_docs/version-1.6.x/reference/sql/search.md

spiceai/docs | Spice Cloud Platform

trunk

Edit on GitHub

Fork

/docs/website/versioned_docs/version-1.6.x/reference/sql/search.md

spiceai/docs/README.md

title: 'Search in SQL' sidebar_label: 'Search' description: 'Reference for search functions and filtering in Spice SQL.' sidebar_position: 20

This section documents search capabilities in Spice SQL, including vector search, full-text search, and lexical filtering methods. These features help retrieve relevant data using semantic similarity, keyword matching, and pattern-based filtering.

Table of Contents
Vector Search (vector_search)
- Usage
  - Example
Full-Text Search (text_search)
- Usage
  - Example
Lexical Search: LIKE, =, and Regex

Vector Search (`vector_search`)

Vector search retrieves records by semantic similarity using embeddings. It is ideal for finding related content even when exact keywords differ.

Usage

table: Dataset name (required)
query: Search text (required)
col: Column name (optional if only one embedding column)
limit: Maximum results (optional)
include_score: Include relevance scores (optional, default TRUE)

Example

See Vector-Based Search for configuration and advanced usage.

Full-Text Search (`text_search`)

Full-text search uses BM25 scoring to retrieve records matching keywords in indexed columns.

Usage

table: Dataset name (required)
query: Keyword or phrase (required)
col: Column to search (required if multiple indexed columns)
limit: Maximum results (optional)
include_score: Include relevance scores (optional, default TRUE)

Example

See Full-Text Search for configuration and details.

Lexical Search: LIKE, =, and Regex

Spice SQL supports traditional filtering for exact and pattern-based matches:

LIKE (Pattern Matching)

% matches any sequence of characters.
_ matches a single character.

= (Keyword/Exact Match)

Returns rows where the column exactly matches the value.

Regex Filtering

Spice SQL does not support the ~ or !~ operators for regular expression matching. Instead, use scalar functions such as regexp_like, regexp_match, and regexp_replace for regex-based filtering. For details and examples, see the Scalar Functions documentation.

Example

For more on hybrid and advanced search, see Search Functionality and Vector-Based Search.

spiceai/docs/README.md

title: 'Search in SQL' sidebar_label: 'Search' description: 'Reference for search functions and filtering in Spice SQL.' sidebar_position: 20

Table of Contents
Vector Search (vector_search)
- Usage
  - Example
Full-Text Search (text_search)
- Usage
  - Example
Lexical Search: LIKE, =, and Regex

Vector Search (`vector_search`)

Vector search retrieves records by semantic similarity using embeddings. It is ideal for finding related content even when exact keywords differ.

Usage

table: Dataset name (required)
query: Search text (required)
col: Column name (optional if only one embedding column)
limit: Maximum results (optional)
include_score: Include relevance scores (optional, default TRUE)

Example

See Vector-Based Search for configuration and advanced usage.

Full-Text Search (`text_search`)

Full-text search uses BM25 scoring to retrieve records matching keywords in indexed columns.

Usage

table: Dataset name (required)
query: Keyword or phrase (required)
col: Column to search (required if multiple indexed columns)
limit: Maximum results (optional)
include_score: Include relevance scores (optional, default TRUE)

Example

See Full-Text Search for configuration and details.

Lexical Search: LIKE, =, and Regex

Spice SQL supports traditional filtering for exact and pattern-based matches:

LIKE (Pattern Matching)

% matches any sequence of characters.
_ matches a single character.

= (Keyword/Exact Match)

Returns rows where the column exactly matches the value.

Regex Filtering

Example

For more on hybrid and advanced search, see Search Functionality and Vector-Based Search.

SELECT id, score
FROM vector_search(table, 'search query')
ORDER BY score DESC
LIMIT 5;

SELECT id, score
FROM vector_search(table, 'search query')
ORDER BY score DESC
LIMIT 5;

SELECT review_id, rating, customer_id, body, score
FROM vector_search(reviews, 'issues with same day shipping')
WHERE created_at >= to_unixtime(now() - INTERVAL '7 days')
ORDER BY score DESC
LIMIT 2;

SELECT review_id, rating, customer_id, body, score
FROM vector_search(reviews, 'issues with same day shipping')
WHERE created_at >= to_unixtime(now() - INTERVAL '7 days')
ORDER BY score DESC
LIMIT 2;

SELECT id, score
FROM text_search(table, 'search terms', col)
ORDER BY score DESC
LIMIT 5;

SELECT id, score
FROM text_search(table, 'search terms', col)
ORDER BY score DESC
LIMIT 5;

SELECT id, title, score
FROM text_search(doc.pulls, 'search keywords', body)
ORDER BY score DESC
LIMIT 5;

SELECT id, title, score
FROM text_search(doc.pulls, 'search keywords', body)
ORDER BY score DESC
LIMIT 5;

SELECT * FROM my_table WHERE column LIKE '%substring%';

SELECT * FROM my_table WHERE column LIKE '%substring%';

SELECT * FROM my_table WHERE column = 'exact value';

SELECT * FROM my_table WHERE column = 'exact value';

SELECT * FROM my_table WHERE regexp_like(column, '^spice.*ai$');

SELECT * FROM my_table WHERE regexp_like(column, '^spice.*ai$');

title: 'Search in SQL' sidebar_label: 'Search' description: 'Reference for search functions and filtering in Spice SQL.' sidebar_position: 20

Table of Contents

Vector Search (vector_search)

Usage

Example

Full-Text Search (text_search)

Usage

Example

Lexical Search: LIKE, =, and Regex

LIKE (Pattern Matching)

= (Keyword/Exact Match)

Regex Filtering

Example

title: 'Search in SQL' sidebar_label: 'Search' description: 'Reference for search functions and filtering in Spice SQL.' sidebar_position: 20

Table of Contents

Vector Search (vector_search)

Usage

Example

Full-Text Search (text_search)

Usage

Example

Lexical Search: LIKE, =, and Regex

LIKE (Pattern Matching)

= (Keyword/Exact Match)

Regex Filtering

Example

Vector Search (`vector_search`)

Full-Text Search (`text_search`)

Vector Search (`vector_search`)

Full-Text Search (`text_search`)