Functions Overview

Laminar provides 100+ built-in SQL functions powered by Apache DataFusion.

Function Categories

Category	Description	Examples
Text & Regex	String manipulation and pattern matching	`UPPER`, `CONCAT`, `REGEXP_REPLACE`
Numbers & Math	Mathematical operations	`ABS`, `ROUND`, `SQRT`, `LOG`
Dates & Time	Timestamp manipulation	`NOW`, `DATE_TRUNC`, `EXTRACT`
JSON	Parse and query JSON data	`JSON_GET`, `JSON_LENGTH`
Arrays & Structs	Collection operations	`ARRAY_AGG`, `ARRAY_LENGTH`, `UNNEST`
Aggregates	Compute over groups	`SUM`, `AVG`, `COUNT`, `APPROX_DISTINCT`
Window Functions	Compute over partitions	`ROW_NUMBER`, `LAG`, `LEAD`

Common Functions

Data Transformation

SELECT
  LOWER(email) as email,                    -- Text
  ROUND(price, 2) as price,                 -- Math
  DATE_TRUNC('hour', timestamp) as hour,    -- Time
  JSON_GET(payload, 'user.id') as user_id   -- JSON
FROM events

Null Handling

SELECT
  COALESCE(nickname, name, 'Anonymous'),    -- First non-null
  NULLIF(value, 0),                          -- NULL if equals
  NVL(phone, 'N/A')                          -- Replace NULL
FROM users

Conditionals

SELECT
  CASE
    WHEN amount > 1000 THEN 'high'
    WHEN amount > 100 THEN 'medium'
    ELSE 'low'
  END as tier,
  GREATEST(a, b, c) as max_value,
  LEAST(a, b, c) as min_value
FROM orders

Type Conversion

SELECT
  CAST(price as INT),
  CAST(timestamp as DATE),
  CAST(user_id as VARCHAR),
  amount::DECIMAL(10,2)
FROM orders

Function Syntax

Basic Syntax

FUNCTION_NAME(argument1, argument2, ...)

Named Arguments

Some functions support named arguments:

APPROX_PERCENTILE_CONT(latency, percentile => 0.95)

Expressions as Arguments

Arguments can be:

Column references: UPPER(name)
Literals: ROUND(3.14159, 2)
Other functions: UPPER(TRIM(name))
Arithmetic: ROUND(price * 1.1, 2)
Conditionals: ABS(CASE WHEN x < 0 THEN x ELSE -x END)

Null Behavior

Most functions return NULL if any argument is NULL:

SELECT CONCAT('Hello', NULL)  -- Returns NULL
SELECT 5 + NULL               -- Returns NULL
SELECT UPPER(NULL)            -- Returns NULL

Use COALESCE to handle NULLs:

SELECT CONCAT('Hello ', COALESCE(name, 'Guest'))

Aggregate vs Scalar Functions

Scalar functions operate on single values:

SELECT UPPER(name) FROM users  -- Applied to each row

Aggregate functions operate on groups:

SELECT COUNT(*), AVG(amount)
FROM orders
GROUP BY TUMBLE(interval '1 hour')

Aggregates require GROUP BY in streaming queries.