Built-in Functions

KalamDB provides built-in functions for ID generation, user context, and timestamps.

KalamDB query execution also supports DataFusion SQL functions. For advanced query-time function usage, see:

The functions on this page are KalamDB-specific functions you will use most often.

ID Generation

SNOWFLAKE_ID()

Generate a unique 64-bit snowflake ID (time-ordered, globally unique):

SQL

1SELECT SNOWFLAKE_ID();2-- Returns: 7302451094528000001

Commonly used as the default for primary keys:

SQL

1CREATE TABLE app.messages (2  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),3  content TEXT NOT NULL4);

UUID_V7()

Generate a UUIDv7 (time-ordered UUID):

SQL

1SELECT UUID_V7();2-- Returns: 0193a5e0-7a1b-7000-8000-000000000001

ULID()

Generate a ULID (Universally Unique Lexicographically Sortable Identifier):

SQL

1SELECT ULID();2-- Returns: 01HQJK5M5R3YJQWXHN4QWXYN01

Context Functions

CURRENT_USER()

Returns the username of the currently authenticated user:

SQL

1SELECT CURRENT_USER();2-- Returns: 'alice'

Useful for audit columns:

SQL

1CREATE TABLE app.audit_log (2  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),3  action TEXT NOT NULL,4  performed_by TEXT DEFAULT CURRENT_USER(),5  created_at TIMESTAMP DEFAULT NOW()6);

Date & Time

NOW()

Returns the current timestamp:

SQL

1SELECT NOW();2-- Returns: 2026-02-18T10:30:00.000Z

Commonly used as a default for timestamp columns:

SQL

1CREATE TABLE app.events (2  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),3  event_type TEXT NOT NULL,4  created_at TIMESTAMP DEFAULT NOW()5);

Vector Search

COSINE_DISTANCE()

Use COSINE_DISTANCE(embedding_column, '[...]') to rank rows by vector similarity. This is the primary KalamDB pattern for EMBEDDING(n) columns: pass the stored column as the first argument and a JSON array literal as the query vector.

SQL

1SELECT id, title2FROM rag.documents_vectors3ORDER BY COSINE_DISTANCE(doc_embedding, '[1.0,0.0,0.0]')4LIMIT 5;

Typical flow:

Store embeddings in an EMBEDDING(n) column.
Create a cosine index with ALTER TABLE ... CREATE INDEX ... USING COSINE.
Use ORDER BY COSINE_DISTANCE(...) LIMIT k to fetch nearest matches.

Notes:

The query vector must match the column dimension.
Lower distance means a closer match.
JSON query literals (for example '[1.0,0.0,0.0]') use KalamDB’s vector-search path and return FLOAT.
When both arguments are array/list values, KalamDB delegates to DataFusion 54’s native cosine_distance and returns DOUBLE.
This is the main SQL entry point for vector search in KalamDB today.

JSON Operators and Functions

KalamDB registers DataFusion’s JSON function package in every SQL session. That gives JSON columns the common PostgreSQL-style query syntax most users expect for extraction and existence checks.

SQL

1SELECT2  doc->'profile' AS profile_json,3  doc->>'status' AS status_text,4  doc->'items'->0 AS first_item,5  doc ? 'customer_id' AS has_customer_id6FROM app.orders7WHERE doc->>'status' = 'paid';

Supported PostgreSQL-style operators in KalamDB SQL:

Syntax	Equivalent helper	Result
`json_col -> 'key'` or `json_col -> 0`	`json_get(...)`	JSON value
`json_col ->> 'key'` or `json_col ->> 0`	`json_as_text(...)`	text
`json_col ? 'key'`	`json_contains(...)`	`BOOLEAN`

Available JSON helpers from the registered DataFusion package:

Function	Use
`json_get`	Return a JSON value at a path
`json_as_text`	Return a value at a path as text
`json_get_json`	Return nested JSON as raw JSON text
`json_get_str`	Return a string value
`json_get_int`	Return an integer value
`json_get_float`	Return a floating-point value
`json_get_bool`	Return a boolean value
`json_get_array`	Return an array value
`json_contains`	Test whether a key or index exists
`json_length`	Return the length of an array or object
`json_object_keys`	Return the keys of a JSON object as an array
`json_from_scalar`	Convert a scalar into a JSON value for composed expressions

Scope note:

KalamDB plans PostgreSQL-style ->, ->>, and ? operators natively through DataFusion 54 and datafusion-functions-json. No SQL rewrite pass is applied for these operators.
Do not assume full PostgreSQL jsonb operator parity for operators such as #>, #>>, or @> yet.

DataFusion 54 Query Extensions

KalamDB runs on DataFusion 54 and enables additional query-time SQL beyond the KalamDB-specific functions above.

Lambda array functions

Use SQL lambda syntax with higher-order array functions:

SQL

1SELECT array_transform([1, 2, 3, 4, 5], x -> x * 10) AS scaled;2 3SELECT array_transform(4  array_filter([1, 2, 3, 4, 5], x -> x > 2),5  x -> x * 106) AS filtered_scaled;7 8SELECT array_any_match([1, 2, 3], x -> x > 2) AS has_large;

These work in general SELECT queries. They are not supported in SUBSCRIBE TO / live-query SQL, which keeps a narrower surface.

Lateral joins

DataFusion 54 adds basic lateral join support for correlated subqueries in the FROM clause:

SQL

1SELECT u.id, expanded.value2FROM app.users AS u3CROSS JOIN LATERAL (4  SELECT array_transform(u.tags, t -> upper(t)) AS value5) AS expanded;

Comparison and subquery semantics

DataFusion 54 also changes a few comparison behaviors worth knowing when porting SQL:

Numeric columns compared to string literals (for example amount > '100') are coerced numerically, not lexicographically.
Uncorrelated scalar subqueries that return more than one row fail at execution with a cardinality error.

For the full upstream function catalog, use the DataFusion SQL docs .

Function Summary

Function	Return Type	Description
`SNOWFLAKE_ID()`	`BIGINT`	Time-ordered 64-bit unique ID
`UUID_V7()`	`TEXT`	Time-ordered UUID v7
`ULID()`	`TEXT`	Lexicographically sortable unique ID
`CURRENT_USER()`	`TEXT`	Authenticated username
`NOW()`	`TIMESTAMP`	Current server timestamp
`COSINE_DISTANCE(vector, query)`	`FLOAT` or `DOUBLE`	Vector similarity distance. JSON query literals return `FLOAT`; array/array inputs use native DataFusion 54 and return `DOUBLE`.

DataFusion Function Support

KalamDB supports DataFusion SQL function execution for SELECT/query workloads, including scalar, aggregate, and window functions documented in DataFusion.

If you need advanced expression behavior beyond KalamDB-specific built-ins, use the DataFusion function references linked above as the canonical guide.

For end-to-end schema and index examples, see /docs/server/architecture/vector-search.