Language reference
Find an operator
Every operator, predicate and function, with the glyph and keyword forms that reach it. These are the same names the engine's own documentation lookup resolves.
Operators
- σSelection
SELECTFilter rows by a conditionσselectselection - πProjection
PROJECTKeep or rename a subset of columnsπprojectprojection - ρRename
RENAMERename a relation or its columnsρrename - γAggregation
GROUPGroup rows and aggregateγgroupgroupbyaggregationgroup by - τSort
SORTSort rows by one or more columnsτsortorderorder by - λLimit
LIMITRestrict row count (TOP N / OFFSET)λlimit - δDistinct
DISTINCTEliminate duplicate rowsδdistinct - μUnnest
UNNESTExpand an array column to rowsμunnest - ∀Universal
FORALLUniversal quantification (∀ / FORALL)∀forall - Rolling
ROLLINGSliding / cumulative window aggregationrolling - Window RankingROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK
window rankwindow-rankingrow_numberrankdense_rankpercent_rankntile - Window OffsetLAG, LEAD, FIRST_VALUE, LAST_VALUE
window lagwindow-offsetlagleadfirst_valuelast_value - Pivot
PIVOTRotate rows to columnspivot - Unpivot
UNPIVOTRotate columns to rowsunpivot - Tree
TREEFold an adjacency relation into nested documentstree - ωWhyReify a tuple's lineage provenance as a queryable column
ωwhyprovenancelineage
Joins
- ⋈Natural join
JOINMatch on all shared columns⋈joinnatural-joinnatural join - ⨝Theta join
><Match on an explicit condition⨝theta-jointheta joinjoin on>< - ⟕Left outer join
|><Keep all left rows; NULLs for non-matches on right⟕left-outer-joinleft outer joinleft join|><ljoin - ⟖Right outer join
><|Keep all right rows; NULLs for non-matches on left⟖right-outer-joinright outer joinright join><|rjoin - ⟗Full outer join
|><|Keep all rows from both sides⟗full-outer-joinfull outer joinfull join|><|fjoin - ⋉Semi join
SEMIKeep left rows that have at least one match on right⋉semi-joinsemi joinsemi - ▷Anti join
ANTIKeep left rows with no match on right▷anti-joinanti joinanti - AS-OF joinMatch the nearest row in time
as ofasof-joinas-of joinasof - Interval joinMatch rows whose time intervals overlap (Allen-set predicates)
interval joininterval-join - Lateral join
LATERALCorrelated subquery used as a tablelaterallateral-joinlateral join
Set operations
- ×Cross product
CROSSCartesian product of two relations×crosscross product - ∪Union
UNIONSet union — deduplicated rows from both sides∪union - ⊎Union all
UALLBag union — all rows from both sides, duplicates kept⊎union-allunion alluall - ⊔Outer union
OUNIONUnion of relations with different schemas⊔outer-unionouter unionounion - −Difference
DIFFRows in the left side that are not in the right−-differencediffminusexcept - ∩Intersection
INTERRows present in both sides∩intersectioninterintersect - ÷Division
DIVRows in left related to ALL rows in right÷divisiondiv - ∆Symmetric difference
SYMDIFFRows in one side but not both∆symmetric-differencesymmetric differencesymdiff - ∘Composition
COMPOSESingle relational hop (pipe one relation through another)∘compositioncompose
Aggregates
- SUM
SUMSum of a numeric expressionsum - AVG
AVGAverage of a numeric expressionavgaverage - COUNT
COUNTCount rows or non-null valuescount - MIN
MINMinimum valuemin - MAX
MAXMaximum valuemax - COLLECT
COLLECTGather group values into an arraycollect - ARGMAX
ARGMAXYield value from row with max rankargmax - ARGMIN
ARGMINYield value from row with min rankargmin
Predicates
- ComparisonRow comparisons: =, !=, <, >, ≤, ≥
=comparison!=≠<>≤≥<=>= - ∧AND
ANDLogical conjunction∧and - ∨OR
ORLogical disjunction∨or - ¬NOT
NOTLogical negation¬not - ∈IN
INSet-membership test∈innot in∉ - IS NULLNULL / NOT NULL test
is nullis-nullisnullnull⊥ - LIKE
LIKEPattern matching with wildcardslike
Literals
| Page | Written as | What it does |
|---|---|---|
| Date | date '…' date-literal date literal | Date literal |
| Time | time '…' time-literal time literal | Time literal |
| Timestamp | timestamp '…' timestamp-literal timestamp literal | Timestamp literal |
| Duration | duration '…' duration-literal duration literal interval | Duration literal |
| Array | [expr, …] array-construction array construction array | Array construction literal |
| Struct | {name: expr} struct-construction struct construction struct | Struct construction literal |
| Truth relations | unit truth-relations truth relation empty dee dum | Zero-column truth relations |
Language and scripts
| Page | Written as | What it does |
|---|---|---|
| Assignment | := assignment | Name a relation expression |
| Source | source | Declare an external data source |
| HTTP source | http http-source http source | HTTP / JSON data source |
| Connection | connection | Declare a named database connection |
| GEDCOM source | gedcom gedcom-source gedcom source ged | Read a GEDCOM genealogy file as individuals and families |
| Log source | log-source log source access log clf | Read a web server access log as a typed relation |
| Namespace | namespace | Declare a namespace |
| Import | import | Import definitions from another script |
| Inline table | [| … |] inline-table inline table | Literal relation value written inline |
| Def | def | Define a scalar or relation function |
| Def relation | def relation def-relation tvf | Define a relation-valued (table-valued) function |
| With ordinality | with ordinality with-ordinality ordinality | Attach a row number when unnesting |
| Relate | relate relationship relationships | Declare a named, bounded relationship between two relations |
| Delimited identifier | `name` delimited-identifier delimited identifier backtick quoted-identifier | Backtick a name that collides with a reserved word |
| Comments | -- comment comments /* | Ignore text: -- to end of line, / … / for a block |
| Operator spellings | σ / select spellings spelling ascii unicode glyph glyphs syntax | Every operator's Unicode glyph and its ASCII equivalent |
| Grammar | ebnf grammar bnf | The whole language as one EBNF grammar |
| Relation catalog | relix.relations introspection-relations relations boundedness bounded unbounded row_count | relix.relations — every relation the script can name, its kind, size and whether it ends |
| Introspection stdlib | relix.unused introspection introspection-stdlib relix.deps relix.impact relix.find relix.schema relix.cycles relix.funcs | relix.unused / deps / impact / find / schema / cycles / funcs — introspection shipped as Relix |
| Observability feed | relix.events introspection-events events relix.rules feed | relix.events / relix.rules — the previous run's engine decisions, as rows |
| Component inventory | relix.version introspection-version version components inventory | relix.version — the engine, the facade and every discovered provider, as rows |
| Candidate keys | relix.keys introspection-keys keys candidate key candidate keys | relix.keys — the collected candidate keys of every relation, one row per key column |
| Reserved namespace | relix.catalog introspection-catalog catalog reserved namespace | relix.catalog — the relix.* surface describing itself |
Advanced operators
| Page | Written as | What it does |
|---|---|---|
| Closure | closure rclosure ⁺ transitive-closure | Transitive / reflexive-transitive closure |
| Cluster | cluster | Partition rows into clusters |
| Cover | cover | Combinatorial test-suite generation (t-way covering arrays) |
| Downsample | downsample | Reduce row density over a time / numeric axis |
| Fixpoint | fix fixpoint | General monotone recursion (least fixpoint) |
| Iterate | iterate iteration pagerank convergence | Replace-each-round iteration: PageRank, state machines, convergence |
| Optimize | optimize | LP / ILP declarative optimisation |
| Path | path | Enumerate shortest / all paths in a graph |
| Sample | sample bernoulli | Bernoulli (row-probability) random sampling |
| Reservoir | reservoir reservoir-sample | Reservoir (fixed-size) random sampling |
| Sessionize | sessionize | Gap-and-island / sessionisation |
| Solve | solve | Constraint solving over a relation |
| Top-K | top topk top-k | Return top-K rows per group |
| Trace | trace | Diagnostic operator — print rows as they pass through |
| Optimizer | optimizer optimiser rules rewrites | Logical rewrite rules — what fires, when, and how to see it |
| Pushdown | pushdown push-down explain | What folds into a backend and what the engine computes |
Functions
| Page | Written as | What it does |
|---|---|---|
| Len | len | String length |
| UCase | ucase | Upper-case |
| LCase | lcase | Lower-case |
| Trim | trim | Strip surrounding whitespace |
| LTrim | ltrim | Strip leading whitespace |
| RTrim | rtrim | Strip trailing whitespace |
| Left | left | Leftmost characters |
| Right | right | Rightmost characters |
| Mid | mid | Substring |
| InStr | instr | Find substring position |
| Chr | chr | Character from code |
| Asc | asc | Code of first character |
| Replace | replace | Substitute text |
| Abs | abs | Absolute value |
| Int | int | Floor to integer |
| Fix | fix | Truncate toward zero |
| Round | round | Round to nearest |
| Ceil | ceil | Round up |
| Sgn | sgn | Sign |
| Sqr | sqr | Square root |
| Log | log | Natural logarithm |
| Exp | exp | Exponential |
| Sin | sin | Sine |
| Cos | cos | Cosine |
| Tan | tan | Tangent |
| Atn | atn | Arctangent |
| Power | power | Raise to a power |
| Rand | rand | Random number |
| NOW | now | Current timestamp |
| CURRENT_DATE | current_date | Today |
| CURRENT_TIME | current_time | Current clock time |
| YEAR | year | Extract year |
| MONTH | month | Extract month |
| DAY | day | Extract day of month |
| HOUR | hour | Extract hour |
| MINUTE | minute | Extract minute |
| SECOND | second | Extract second |
| DATE_TRUNC | date_trunc | Truncate timestamp to a unit |
| MINUTES | minutes | Measure a duration in minutes |
| SECONDS | seconds | Measure a duration in seconds |
| DAYS | days | Measure a duration in days |
| to_date | to_date | Parse a string to a DATE |
| to_timestamp | to_timestamp | Parse a string to a TIMESTAMP |
| to_time | to_time | Parse a string to a TIME |
| IIf | iif | Inline if / conditional value |
| Nz | nz | Null-to-value |
| Coalesce | coalesce | First non-null |
| IsNull | isnull | Test for missing value |
| IsNumeric | isnumeric | Test if value looks numeric |
| CStr | cstr | Convert to string |
| CInt | cint | Convert to integer |
| CDbl | cdbl | Convert to number |
| Entries | entries | Read an object's fields as rows |