Columns and Expressions¶
from sqlspec.builder import (
AggregateExpression,
Column,
ColumnExpression,
ConversionExpression,
ExpressionWrapper,
FunctionColumn,
FunctionExpression,
MathExpression,
StringExpression,
VectorDistance,
build_oracle_hnsw_subquery,
build_oracle_hybrid_rrf_query,
build_oracle_hybrid_rrf_sql,
extract_expression,
parse_column_expression,
parse_condition_expression,
parse_order_expression,
parse_table_expression,
to_expression,
)
Typed column objects, aggregate/math/string/conversion expression wrappers, cross-dialect vector distance expressions, Oracle 26ai Hybrid RRF helpers, and SQLGlot expression parsing helpers. See Expressions, Window Functions and Vector Search for usage examples.
Column Descriptors¶
Column
¶
Bases: _ScalarExpressionMixin
Represents a database column with Python operator support.
name = name
¶
table = table
¶
lower()
¶
SQL LOWER() function.
upper()
¶
SQL UPPER() function.
length()
¶
SQL LENGTH() function.
trim()
¶
SQL TRIM() function.
abs()
¶
SQL ABS() function.
round(decimals=0)
¶
SQL ROUND() function.
floor()
¶
SQL FLOOR() function.
ceil()
¶
SQL CEIL() function.
substring(start, length=None)
¶
SQL SUBSTRING() function.
coalesce(*values)
¶
SQL COALESCE() function.
count()
¶
SQL COUNT() function.
sum()
¶
SQL SUM() function.
avg()
¶
SQL AVG() function.
min()
¶
SQL MIN() function.
max()
¶
SQL MAX() function.
count_distinct()
¶
SQL COUNT(DISTINCT column) function.
count_all()
¶
SQL COUNT(*) function.
vector_distance(other_vector, metric='euclidean')
¶
Calculate vector distance using specified metric.
Generates dialect-specific SQL for vector distance calculation
- PostgreSQL (pgvector): Operators <->, <=>, <#>
- MySQL 9+: DISTANCE(col, vec, 'METRIC') function
- Oracle 23ai+: VECTOR_DISTANCE(col, vec, METRIC) function
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
other_vector
|
Sequence[float | int] | Any | Column | Expr
|
Vector to compare against (sequence, 1-D array, Column, or SQLGlot expression). |
required |
metric
|
str
|
Distance metric to use ( |
'euclidean'
|
Returns:
| Type | Description |
|---|---|
FunctionColumn
|
FunctionColumn expression for use in SELECT, WHERE, ORDER BY. |
cosine_similarity(other_vector)
¶
Calculate cosine similarity (1 - cosine_distance).
Convenience method that computes similarity instead of distance. Returns values in range [-1, 1] where 1 = identical vectors.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
other_vector
|
Sequence[float | int] | Any | Column | Expr
|
Vector to compare against (sequence, 1-D array, Column, or expression). |
required |
Returns:
| Type | Description |
|---|---|
FunctionColumn
|
FunctionColumn expression: 1 - cosine_distance(self, other_vector). |
ColumnExpression
¶
FunctionColumn
¶
Bases: _ScalarExpressionMixin
Represents the result of a SQL function call on a column.
Expression Wrappers and Vector Distance¶
ExpressionWrapper
¶
AggregateExpression
¶
FunctionExpression
¶
MathExpression
¶
StringExpression
¶
ConversionExpression
¶
VectorDistance(*, this, expression, metric='euclidean')
¶
Build a SQLSpec vector-distance expression.
build_oracle_hnsw_subquery(table, vector_column='embedding', vector_param=':query_vec', *, id_column='id', select_columns=('id',), metric='COSINE', top_k_param=':top_k', query_vector_bind=None, top_k_bind=None, target_accuracy=None, approximate=True, where_clause=None, rank_alias='vector_rank', distance_alias='vector_distance')
¶
Build an Oracle 26ai HNSW-preserving or exact top-K subquery using outer ROWNUM.
Oracle 26ai CBO disables VECTOR INDEX HNSW SCAN when ROW_NUMBER() OVER (...)
appears in the same query block as ORDER BY VECTOR_DISTANCE(...) FETCH APPROX FIRST :k ROWS ONLY.
This helper isolates the ORDER BY VECTOR_DISTANCE(...) fetch clause inside
an inner derived table and assigns ROWNUM in the outer query block. When
approximate=False, FETCH FIRST :k ROWS ONLY is emitted instead of
FETCH APPROX FIRST for unindexed tables or exact top-K evaluation.
build_oracle_hybrid_rrf_sql(table, vector_column='embedding', text_column='content_text', *, id_column='id', key_column=None, select_columns=None, vector_param=':query_vec', text_param=':fts_query', metric='COSINE', rrf_k=60, rrf_k_param=None, candidate_limit_param=':candidate_limit', final_limit_param=':final_limit', top_k_param=None, target_accuracy=None, approximate=True, where_clause=None)
¶
Build an Oracle 26ai Hybrid Search query fusing vector similarity and Oracle Text FTS via RRF.
build_oracle_hybrid_rrf_query(table, vector_column='embedding', text_column='content_text', *, id_column='id', key_column=None, select_columns=None, vector_param=':query_vec', text_param=':fts_query', metric='COSINE', rrf_k=60, rrf_k_param=None, candidate_limit_param=':candidate_limit', final_limit_param=':final_limit', top_k_param=None, target_accuracy=None, approximate=True, where_clause=None)
¶
Alias for :func:build_oracle_hybrid_rrf_sql.
Expression Parsing Helpers¶
to_expression(value)
¶
Convert a Python value to a raw SQLGlot expression.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
value
|
Any
|
Python value or SQLGlot expression to convert. |
required |
Returns:
| Type | Description |
|---|---|
Expr
|
Raw SQLGlot expression. |
extract_expression(value)
¶
Extract SQLGlot expression from value, handling wrapper types.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
value
|
Any
|
String, SQLGlot expression, or wrapper type. |
required |
Returns:
| Type | Description |
|---|---|
Expr
|
Raw SQLGlot expression. |
parse_column_expression(column_input, builder=None, *, dialect=None, allow_alias=True)
¶
Parse a column input that might be a complex expression.
Handles cases like
- Simple column names: "name" -> Column(this=name)
- Qualified names: "users.name" -> Column(table=users, this=name)
- Aliased columns: "name AS user_name" -> Alias(this=Column(name), alias=user_name)
- Function calls: "MAX(price)" -> Max(this=Column(price))
- Complex expressions: "CASE WHEN ... END" -> Case(...)
- Custom Column objects from our builder
- SQL objects with raw SQL expressions
- Query builders and query nodes, as scalar subqueries when a builder is given
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
column_input
|
str | Expr | Any
|
String, SQLGlot expression, SQL object, Column object, or query builder |
required |
builder
|
Any | None
|
Optional builder instance for parameter merging |
None
|
dialect
|
DialectType | None
|
Optional SQL dialect for parsing string expressions |
None
|
allow_alias
|
bool
|
Whether top-level |
True
|
Returns:
| Type | Description |
|---|---|
Expr
|
exp.Expr: Parsed SQLGlot expression |
Raises:
| Type | Description |
|---|---|
SQLBuilderError
|
If the input contains forbidden statement, subquery, or predicate nodes. |
parse_condition_expression(condition_input, builder=None)
¶
Parse a condition that might be complex SQL.
Handles cases like
- Simple conditions: "name = 'John'" -> EQ(Column(name), Literal('John'))
- Tuple format: ("name", "John") -> EQ(Column(name), Literal('John'))
- Complex conditions: "age > 18 AND status = 'active'" -> And(GT(...), EQ(...))
- Function conditions: "LENGTH(name) > 5" -> GT(Length(Column(name)), Literal(5))
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
condition_input
|
str | Expr | tuple[str, Any]
|
String, tuple, or SQLGlot expression for condition |
required |
builder
|
Any
|
Optional builder instance for parameter binding |
None
|
Returns:
| Type | Description |
|---|---|
Expr
|
exp.Expr: Parsed SQLGlot expression (usually a comparison or logical op) |
Raises:
| Type | Description |
|---|---|
SQLBuilderError
|
If the condition is empty, multi-statement, or contains DDL/DML/Command nodes. |
parse_order_expression(order_input, *, dialect=None)
¶
Parse an ORDER BY expression that might include direction.
Handles cases like
- Simple column: "name" -> Column(this=name)
- With direction: "name DESC" -> Ordered(this=Column(name), desc=True)
- Qualified: "users.name ASC" -> Ordered(this=Column(table=users, this=name), desc=False)
- Function: "COUNT(*) DESC" -> Ordered(this=Count(this=Star), desc=True)
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
order_input
|
str | Expr
|
String or SQLGlot expression for ORDER BY |
required |
dialect
|
DialectType | None
|
Optional SQL dialect for parsing |
None
|
Returns:
| Type | Description |
|---|---|
Expr
|
exp.Expr: Parsed SQLGlot expression (usually Ordered or Column) |
Raises:
| Type | Description |
|---|---|
SQLBuilderError
|
If the input contains forbidden statement, subquery, or predicate nodes. |
parse_table_expression(table_input, explicit_alias=None, dialect=None)
¶
Parse table text into a table expression.
The accepted form is a dotted table name followed by an optional alias written name alias
or name AS alias. The text is read once with the tokenizer of dialect, so BigQuery's
```project.dataset.table``` becomes its qualified parts.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
table_input
|
str
|
The table text. |
required |
explicit_alias
|
str | None
|
Optional alias supplied separately from the table text. |
None
|
dialect
|
DialectType | None
|
Dialect used to read the text. |
None
|
Returns:
| Type | Description |
|---|---|
Expr
|
The parsed table expression. |
Raises:
| Type | Description |
|---|---|
SQLBuilderError
|
If the text is not a table name with an optional alias. |