Pure transformations

FeatureQL includes a rich set of functions for transforming data — arithmetic, string manipulation, date math, conditionals, and more. This page shows a representative example for each data type to give you a feel for the syntax. It is not exhaustive: the complete list of all functions is in the syntax reference .

Numerics

Arithmetic operators (+, -, *, /) work as expected. Note that / produces a DOUBLE while // (integer division) preserves the numerator's type. FeatureQL also includes rounding and mathematical functions like ABS(), SQRT(), EXP(), and MOD().

WITH
    price := 49.99e0,
    tax_rate := 0.21e0
SELECT
    price,
    tax := ROUND(price * tax_rate, 2),
    total := ROUND(price * (1e0 + tax_rate), 2),
    half := price / 2e0,
    rounded := CEIL(total)
;

Strings

String concatenation uses || or CONCAT(). FeatureQL provides the usual toolkit for splitting, extracting, replacing, and formatting strings.

WITH
    first_name := 'Alice',
    last_name := 'Johnson',
    email := 'alice.johnson@example.com'
SELECT
    full_name := first_name || ' ' || last_name,
    domain := SPLIT_PART(email, '@', 2),
    initials := SUBSTR(first_name, 1, 1) || SUBSTR(last_name, 1, 1),
    slug := LOWER(REPLACE(full_name, ' ', '-'))
;

Conditionals

IF() handles simple branching, CASE WHEN handles multi-branch logic, and COALESCE() picks the first non-null value. All three return typed values — no implicit coercion.

WITH
    score := 73
SELECT
    score,
    grade := CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END,
    is_passing := score >= 60,
    label := IF(is_passing, 'Pass', 'Fail')
;

Dates and timestamps

Date functions follow a consistent pattern: DATE_ADD() to shift, DATE_SUBTRACT() to measure, DATE_FORMAT() to render, and EXTRACT_FROM_DATE() to pull out components. Timestamps support up to microsecond precision.

WITH
    signup := TIMESTAMP '2025-11-03 14:30:00',
    now := TIMESTAMP '2026-02-17 10:00:00'
SELECT
    signup,
    days_since := DATE_SUBTRACT(now, signup, 'DAY'),
    month := DATE_FORMAT(signup, '%B %Y'),
    is_recent := DATE_SUBTRACT(now, signup, 'DAY') <= 90
;

JSON

FeatureQL can parse, extract from, and format JSON values. Use JSON_EXTRACT_SCALAR() to pull a single value as a string, or JSON_EXTRACT() to get a nested JSON fragment.

WITH
    event := JSON '{"type": "purchase", "user_id": "u_42", "payload": {"amount": 29.99, "currency": "EUR"}}'
SELECT
    event,
    event_type := JSON_EXTRACT_SCALAR(event, '$.type'),
    user_id := JSON_EXTRACT_SCALAR(event, '$.user_id'),
    amount := JSON_EXTRACT_SCALAR(event, '$.payload.amount')
;

Arrays

Arrays support set operations (ARRAY_UNION(), ARRAY_INTERSECT(), ARRAY_EXCEPT()), membership checks (CONTAINS()), sorting, and aggregation (ARRAY_SUM(), ARRAY_COUNT()). For vector operations, see COSINE_SIMILARITY(), DOT_PRODUCT(), and EUCLIDEAN_DISTANCE().

WITH
    tags := ARRAY('premium', 'vip', 'early-adopter'),
    other_tags := ARRAY('vip', 'enterprise', 'beta')
SELECT
    tags,
    tag_count := ARRAY_COUNT(tags),
    has_vip := CONTAINS(tags, 'vip'),
    sorted := ARRAY_SORT(tags),
    common := ARRAY_INTERSECT(tags, other_tags)
;

Rows

A ROW is a named struct. You create one with ROW(...), name fields with AS, and extract fields with []. Extracting multiple fields returns a new row with just those fields.

WITH
    address := ROW(
        '123 Main St' AS street,
        'Paris' AS city,
        '75001' AS postal_code
    )
SELECT
    address,
    city := address[city],
    summary := address[city, postal_code]
;

Array of rows

Arrays of rows are FeatureQL's primary structure for one-to-many relationships — they replace SQL JOINs with nested data. See Array of rows for a full treatment.