Skip to content

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", "cosine", "inner_product", or "euclidean_squared").

'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

Base class for column expressions that can be combined with operators.

expression

Get the underlying SQLGlot expression.

sqlglot_expression

Get the underlying SQLGlot expression.

FunctionColumn

Bases: _ScalarExpressionMixin

Represents the result of a SQL function call on a column.

Expression Wrappers and Vector Distance

ExpressionWrapper

Base wrapper for SQLGlot expressions.

expression

Get the underlying SQLGlot expression.

sqlglot_expression

Get the underlying SQLGlot expression.

as_(alias)

Create an aliased expression.

AggregateExpression

Bases: ExpressionWrapper

Aggregate functions like COUNT, SUM, AVG.

FunctionExpression

Bases: ExpressionWrapper

General SQL functions.

MathExpression

Bases: ExpressionWrapper

Mathematical functions like ROUND.

StringExpression

Bases: ExpressionWrapper

String functions like UPPER, LOWER, LENGTH.

ConversionExpression

Bases: ExpressionWrapper

Conversion functions like CAST, COALESCE.

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 AS aliases are permitted on string or AST inputs

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.