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 := 'Alice',
    LAST := 'Johnson',
    EMAIL := 'alice.johnson@example.com',
SELECT
    FULL_NAME := FIRST || ' ' || LAST,
    DOMAIN := SPLIT_PART(EMAIL, '@', 2),
    INITIALS := SUBSTR(FIRST, 1, 1) || SUBSTR(LAST, 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_DIFF() 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_DIFF(SIGNUP, NOW, 'DAY'),
    MONTH := DATE_FORMAT(SIGNUP, '%B %Y'),
    IS_RECENT := DATE_DIFF(SIGNUP, NOW, '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_LENGTH()). 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_LENGTH(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.