About this book

The OpenGlacier Modeling Language (OGML in this book) is one language for three jobs:

• querying: reading documents, filtering, computing, joining, aggregating;
• writing: inserting, updating, deleting and bulk-loading documents in one transaction;
• modeling: naming queries as views, stacking views into a data model, and keeping the costly ones materialized.

A query is a pipeline. It names a source, then sends its rows through stages separated by |:

on orders
| where status == "paid"
| derive revenue = price * qty
| group | by region | sum revenue | end
| sort revenue desc

How this book is organized

Each reference entry follows the same layout:

• Definition: one sentence.
• Syntax: the accepted forms, with <placeholders> and [optional parts].
• Behavior: what it does, row by row, including the edge cases.
• Examples: queries with their results.
• Errors and limits: what it refuses, and why.
• See also: related stages and functions.

Conventions

• Queries are shown in text blocks. Results are shown as JSON, one document per line, without _id unless it matters.
• -- … after a query line is a comment of this book, not of the language (OGML has no comments).
• <name> is a placeholder; [ … ] is optional; a | b in a syntax line means one of, except where the pipe is the stage separator (it then stands at the start of a line).
• The examples read the three small collections below.

users     { name, age, city, active, tags: [...], profile: { country, score } }
orders    { order_id, user, product, qty, price, status, month, region }
products  { code, label, category, embedding: [x, y, z] }

Sources of truth

This book follows the engine. Where they disagree, the engine wins and the book has a bug:

• the stage catalogue, og-core/src/query/stage.rs (also listed live by the .query.stages operation);
• the function table, og-core/src/query/eval.rs (Function);
• the views, og-core/src/view.rs;
• the developer reference, og-core/src/query/README.md.

To read the book as a website: mdbook serve og-books/manifest/the_ogml_language. The Markdown files also read as they are.

1. Introduction

1.1 What OGML is

OGML is the language of every openglacier node: clients send it, and the Core plans and runs it. There is no second language for definitions: a view is written in OGML, and so is the query that reads it.

It is a pipeline language. A query starts at a source, and each stage receives the rows of the stage before it, transforms them, and hands its own rows on:

on users                  -- source: every document of `users`
| where age >= 18         -- keeps some rows
| select name, city       -- reshapes each row
| sort city               -- reorders the rows

Read it top to bottom, as a recipe. Each line is one step, and each step can be understood on its own.

1.2 The mental model

1.3 Design principles

• One pipeline, read in order. The order you write is the order of meaning. The planner may move work (filters toward the source, for example), but never in a way that changes the result or hides an error.
• Strict by default. A field that a filter or a formula reads must exist: a typo is an error, not an empty result. Optional fields are said explicitly, with field?, exists() or coalesce().
• No cross-kind coercion. age == "31" is an error, not false. Conversions are explicit (to_int, to_string).
• Bounded memory. ogd never loads a whole collection in memory. A query either fits a shape it can run within its budget (spilling to disk where needed), or it is refused before it starts.
• Transactions. Every write query commits all its changes, or none.
• Everything is queryable. Views, caches, memory, index statistics and history are collections, read with the same language.

1.4 What the language is not

• It has no comments, no user-defined functions, no loops.
• It has no date type: dates are ISO-8601 strings or epoch milliseconds, read by the date functions.
• It is not SQL: there is no SELECT … FROM … GROUP BY sentence, but there is a stage for each of these ideas.

1.5 How a query reaches the engine

A client sends the text of a query, with optional parameters, in a query.execute request (chapter 17). The request may name a Place and an App instance: the scope is set by the request, never by the query text.

2. A guided tour

Twelve short steps through the language. Each one adds one idea.

2.1 Read a collection

on users

Every document of users. from users is the same.

2.2 Keep some rows

on users | where age >= 18 and city == "Paris"

{"name":"Alice","age":31,"city":"Paris",…}
{"name":"Dan","age":28,"city":"Paris",…}

where keeps the rows whose predicate is true. Conditions combine with and, or, not, and parentheses.

| separates stages. A query without | can separate them with ;, or write one stage per line:

on users
where age >= 18
  and city == "Paris"

Both styles read the same (3.2); this book writes |.

2.3 Choose and compute fields

on orders | select order_id, price * qty as total, upper(status) as status

{"order_id":1,"status":"PAID","total":20.0}
…

select keeps only what it lists (plus _id). To add fields and keep the rest, use derive:

on orders | derive total = price * qty, big = total > 100

2.4 Sort and page

on orders | sort price * qty desc | limit 3

sort takes fields or expressions; limit and skip page. A sort followed by limit runs as a bounded Top-N.

2.5 Aggregate

on orders | where status == "paid" | group | by region | sum qty as units | avg price | count as orders | end

{"avg_price":54.75,"orders":2,"region":"EU","units":3}
{"avg_price":130.0,"orders":2,"region":"US","units":5}

The compound group opens a sub-pipeline: one by, then any number of measures, closed by end. The one-line form covers the common cases: group region, qty sums qty per region.

2.6 Join

on orders | lookup products on code == product into product

Each order receives an array product holding the matching products. For anything richer than one equality, write the sub-pipeline:

on users as u
| lookup orders as o
    | where o.user == u.name and o.status == "paid"
    | select order_id, price
    | into paid_orders
| end

2.7 Stack two sources

on actual | select month, amount | union | on budget | select month, amount | end

2.8 Reuse a value

let $since = "2026-02", $min = 50
| on orders
| where month >= $since and price * qty > $min

let names values once per query. $name can also come from the request (params).

2.9 Name a query: a view

define view sales.paid
| description "Paid orders with their total"
| exec
  | on orders | where status == "paid" | derive total = price * qty
| end
| end

Then read it like a collection:

on view sales.paid | group | by region | sum total | end

2.10 Keep it computed: a materialized view

define view sales.by_month | materialized | exec
  | on view sales.paid | group | by month, region | sum total | end
| end
| end

The first read computes the rows and keeps them. Later reads use them, until orders changes, or a view it reads is redefined.

2.11 Write

on users | insert {name: "Eve", age: 22, city: "Lyon"}
on users | where name == "Bob" | set age = age + 1
on orders | where status == "cancelled" | delete
on orders | group | by region | sum qty as units | end | into region_totals

Every write query is one transaction.

2.12 Look under the hood

explain on orders | where qty > 1 | sort price * qty desc | limit 3
on _views | where state == "active" | select name, version, reads
on _memory | where scope == "global"

explain shows the plan without running it; system collections expose views, caches and engine state.

Next: the lexical structure, or jump to the stage reference.

3. Lexical structure

3.1 Layout

Spaces and tabs are interchangeable. A query may sit on one line or many, and indenting a sub-pipeline is cosmetic. A line break is a space too, except in a query written without |, where it can end a stage (3.2).

on users
| where age >= 18
| select name

is the same query as on users | where age >= 18 | select name.

There are no comments. In this book, -- … marks an annotation, never something to type.

3.2 Stage separators

A query separates its stages in one of two styles. The three queries below are the same query:

on users | where age >= 18 | select name

on users; where age >= 18; select name

on users
where age >= 18
select name

Both styles give the same stages, and every rule of this book is the same in both. The query is kept as written: a view stores its body in the style it was defined in.

The pipe

| separates stages.

• A | outside brackets always ends a stage.
• A | inside ( … ), [ … ] or { … } does not: brackets must balance.
• || is the boolean or, never two separators: where a || b is one stage.
• In a query holding a |, line breaks are spaces, as in 3.1.

; and line breaks

A query that holds no | (outside brackets and strings, || aside) separates its stages with ; and with line breaks. ; fits a query on one line (a request, a command line); line breaks fit a query written by hand.

• A ; outside brackets always ends a stage. A final ; is allowed (on users;); an empty stage (;;) is refused.
• A line break ends a stage when all three hold:
  1. brackets are balanced: insert {…} and values [ … ] may span lines;
  2. the line does not end on a continuation: ,, an operator (=, +, ==, >, &&…), or one of the words and or not in as between by on into with from to step default version of keep index view;
  3. the next line starts with a stage word, or with a directive of the block the line is in (below).
• Otherwise the next line goes on with the stage: a line starting with a field, a value, an operator or and continues the line before.
• end closes a block, at the start of a line or after ;.

on orders
where status == "paid"
  and qty > 1                      -- `and` goes on with the `where`
derive total = price * qty,
  big = total > 100                -- the line before ends with `,`
sort total desc

Inside a block, its directives start lines:

on orders
group
  by region
  sum qty as units
  count as orders
end
sort units desc

A source (on, from) starts a stage only after union, after exec, or after a let header:

let $min = 2
on orders
lookup customers
  on _id == customer into who      -- one-line lookup: `on` goes on with it
where qty >= $min

A one-line form stays on one line, or its next lines start with a continuation. Here the second line starts a stage into, so lookup opens a block that no end closes, and the query is refused with the line it read:

on orders
lookup customers on _id == customer
  into who
-- `lookup` at line 2 opens a block that no `end` closes; line 3 starts a new stage `into`: …

To go on with a line whose next word is a stage word, end the line with an operator or a comma, or put the expression in parentheses.

One style per query

• A query uses one style: on t; where a | sort a is refused (`;` at line 1 in a query separated by `|`).
• Text in parentheses keeps its own style: in let $s = (on settings | limit 1) the let query is read on its own, so the query around it may still be written with lines.
• explain stays in front of the query, on the first line or alone on it.

3.3 Identifiers

• Start with a letter (any Unicode letter) or _; continue with letters, digits or _.
• Case-sensitive: Name and name are two fields.
• Unicode works everywhere: on données | where prénom == "Élodie".

Field paths join identifiers with dots: profile.country, a.b.c. A key holding a space or a hyphen can be stored (insert {"display name": "Zed"}) but not written as a path: read it with get(document, "display name").

Optional paths put ? after a segment: nick?, profile?.country. A missing field then reads as null instead of failing (4.4).

Collection names may be dotted too: on shop.orders. Names starting with _ are system collections.

View names are identifiers joined by dots: ledger.cube, sales.by_month.

3.4 Keywords

Reserved by the pipeline lexer:

on  from  as  where  set  lookup  join  union  load  pivot
into  with  chunk  replace  update  merge  end
and  or  not  true  false  null

Inside expressions in is also reserved. between is recognized only after an operand (age between 18 and 30), so it stays usable as a field name.

Other stage names (select, sort, group, limit, window, …) are ordinary identifiers recognized in stage position, and function names are not reserved: round(x) is a call, round alone a field.

Statement words: explain, let, define view, undefine view, and the view clauses description, version, materialized, exec.

3.5 Literals

3.6 Signs

A + or - written directly before a number literal is part of it: -5, - 5 and +2.5 are constants in every stage. Between two operands, + and - are binary: a - 5 subtracts, a > -5 compares with -5. Before a field (-qty), - negates at run time.

3.7 Operators and punctuation

4. Values and types

4.1 The value model

Documents have no schema. Every field holds one of six kinds:

A field can also be missing: absent from the document. Missing is not null (4.4).

4.2 _id

Every stored document has an _id, a UUID v7 such as 01900000-0000-7000-8000-000000000003.

• insert and load generate it when absent.
• load accepts an explicit _id (32 hex digits, with or without dashes): idempotent imports and upserts.
• where _id == "<uuid>" runs as a primary-key lookup instead of a scan.
• select always keeps _id.
• Rows made by group carry a deterministic synthetic _id.

Other reserved fields: _place and _app_instance (the scope of a document, written by the Core), _distance (written by near), and the history fields _doc_id, _version, _op, _generation, _at, _by, _by_device (chapter 8).

4.3 Comparison rules

• Integers and floats compare numerically: age == 31.0 matches age: 31.
• Strings compare lexicographically (by code point): name >= "B" and name < "D".
• No cross-kind coercion. age == "31" is an error (operation "==" is incompatible with values …), not false.
  • Exception: == and != accept null on either side (nick? == null).
  • Exception: x in [...] never fails on kinds: an item of another kind just does not match.
• Fields compare with fields: where age >= profile.score, where o.user == u.name.

Order between kinds (used by sort, group keys, distinct, window partitions): missing and null first, then booleans, numbers, strings, arrays, objects. A number and a numeric string are never compared by value.

4.4 Missing fields

A field read by a predicate or an expression must exist on every row that reaches it:

expression evaluation failed: field path "nickname" is missing

This is deliberate: a typo surfaces as an error instead of an empty result. Rows already streamed before the failing one have been sent, and the response ends in error.

The tools for optional fields:

Stages that tolerate missing fields: projections (select field, drop, rename), near (drops rows without the vector), unwind (passes rows without the array), group and pivot keys after a filter was pushed past them (read as optional, null).

Pattern. A collection fed by files of several shapes, where some rows lack date: on data | where date? != null | … keeps the dated rows and never fails.

4.5 Numbers

On the wire, integers above 2³² travel as floats so that JavaScript clients never lose precision silently; integers beyond 2⁵³ are refused on the wire.

4.6 Text

Text is UTF-8. Positions count characters, not bytes; a negative start counts from the end. There is no + on strings: use concat.

4.7 Dates

There is no date type. A date is either:

• an ISO-8601 string: "2026-03-09", "2026-03", "2026-03-09T10:00:00Z", "2026-03-09 10:00:00+02:00";
• a number of milliseconds since the Unix epoch.

Every computation is in UTC. ISO strings of the same shape sort and compare correctly as text (month >= "2026-02"); mixing shapes or kinds does not. The date functions accept both and return epoch milliseconds, except date_format (text) and date_part (a number).

5. Expressions

An expression computes one value from a row: a filter’s predicate, a derived field, a sort key, a group measure. There is one evaluator for every stage, so anything that works in where works in derive, set, select … as, sort keys, group measures and sub-pipelines, with the same results and the same errors.

5.1 Forms

Every form nests in every other: round(div(price * qty, qty - 1, 0), 2), max([a, b, c]) in allowed.

5.2 Precedence

From lowest to highest; binary operators associate to the left.

As in SQL and Python, not binds looser than comparisons: not city == "Berlin" is not (city == "Berlin"), and not active and age > 18 is (not active) and age > 18.

5.3 Semantics

5.4 Lazy evaluation

and, or, if, case and coalesce evaluate only what they need. This makes guards safe:

on users | where exists(nick) and nick != ""
on orders | derive unit = if(qty == 0, null, price / qty)
on rows | derive label = case(score >= 9, "A", score >= 7, "B", "C")

5.5 Constant folding

Once parameters and variables are bound, a conditional whose condition is constant is replaced by the branch it takes: if(false, a, b) is b, true or x is true, coalesce(null, x) is x, and case drops its false conditions. derive f = f writes nothing. So a switch driven by a parameter costs nothing at run time:

on ledger | derive entity = if($consolidate, null, entity) | group | by entity | sum amount | end

5.6 Function calls

Functions are called by name with parenthesized arguments; any expression may be an argument. Unknown names and wrong argument counts are refused when the query is parsed, before any data is read:

invalid expression "nope(age) > 1" for stage "where": unknown function `nope`; available functions: abs, sqrt, …
invalid expression "abs(age, 2) > 1" for stage "where": function `abs` expects 1 argument(s), got 2

The function reference lists all of them.

5.7 Expressions per stage

6. Anatomy of a query

[explain] [let … |]* <source> ( | <stage> )*

Here | stands for any stage separator: |, or ; and line breaks in a query that holds no | (3.2).

A request is, in order: an optional explain, zero or more let headers, one source, and zero or more stages. A request can instead be a view statement (define view …, undefine view …, chapter 11).

6.1 Sources

Each takes an optional as <alias>. A query may be the source alone: on users returns every document.

Collections

• Dotted names work: on shop.orders.
• A collection exists from its first write; reading one that does not exist returns no rows (not an error).
• In a Place, the source reads only the rows of that Place (and of the App instance, when the request names one).

Aliases

At the top level, on users as u | where u.age > 30 and where age > 30 are the same: the qualifier is dropped before planning (outside string literals), so an alias shadows a field of the same name. In a lookup or union sub-pipeline, the alias names the outer row, and the sub-pipeline’s own alias names the joined row.

Literal rows: values

on values [{code: "FR", rate: 0.2}, {code: "DE", rate: 0.19}] | where rate > 0.19

Objects with bare or quoted keys. values works wherever a collection is read: lookup values […] as r | … | end, union | on values […] | end, the one-line lookup values […] as v on code == account into hit. The rows are written once into a constant collection named after their content (_values.v<hash>): read like any collection (indexes of lookup, streaming), shared by every Place, never writable.

6.2 Stages

Simple stages

| <stage-name> <arguments>

Everything up to the next top-level separator is the stage’s argument text, which the stage’s own grammar reads.

Compound stages and end

Some stages open a sub-pipeline, a sequence of stages closed by | end:

on users
| lookup orders
    | where status == "paid"
    | into paid
| end
| select name, paid

Without |, the same query reads:

on users
lookup orders
  where status == "paid"
  into paid
end
select name, paid

• A missing | end is an error: sub-pipeline opened by stage at … is missing '| end'; written with lines, `lookup` at line 2 opens a block that no `end` closes.
• An end with nothing open is an error: unexpected 'end' outside a sub-pipeline (`end` at line N closes no block written with lines).
• Sub-pipelines nest: a lookup inside a union branch inside a view.

The words inside a compound stage are directives, not stages: by, sum, avg… in group; rows, columns, values, aggregate in pivot; partition by, order by, lag… in window; with, chunk in load.

6.3 Placement rules

6.4 Read and write queries

A query is a write when it holds insert, set, delete, into or load; otherwise it only reads.

• A read streams its rows as they are produced, within the memory contract of chapter 14.
• A write runs in one transaction: everything commits, or nothing does.
• A readOnly request refuses a write (query.read_only_violation), and so do view bodies and let queries.

6.5 Scope: Places and App instances

When a request runs in a Place:

• every source reads only the rows of that Place (_place), and of the App instance when the request names one (_app_instance);
• every write stamps its rows with that scope;
• on view reads the Place’s view of that name, else the standard one;
• materialized views keep one cache per scope (chapter 12).

The language does not change: the scope is applied by the Core, not written in the query.

7. Parameters, variables and explain

7.1 Parameters: $name

A query can reference named parameters. Their values travel next to the query:

{"query": "on users | where age >= $min and city in $cities | limit $n",
 "params": {"min": 18, "cities": ["Paris", "Lyon"], "n": 10}}

Behavior

• Each $name outside a string literal is replaced by the literal of its value before the query is authorized, planned or run. Strings are quoted and escaped, so a value never changes the structure of the query: $name bound to x" | delete stays one string.
• Any JSON value works: numbers, strings, booleans, null, arrays (for in), objects (for insert).
• A parameter goes wherever its literal could: limit $n, near embedding, $vector, fill … from $start to $end.
• An unbound $name fails the request (variable $name is not bound; …); unused parameters are ignored.
• From a terminal: ogcli --param min=18 --param 'cities=["Paris","Lyon"]' '…' (a value is JSON when it parses, else a string).
• Each distinct set of values is planned (and cached) on its own.

7.2 Variables: let

let <$name> = <expression> [, <$name> = <expression>]* | … | <source> | …
let <$name> ?= <expression> | …
let <$name> = ( <query> ) | …

Definition. Names a value computed once per query, before it runs.

let $threshold = 50, $since = parse_date("2026-02-01")
| on orders
| where price * qty > $threshold and parse_date(month) >= $since
| derive margin = price * qty - $threshold

Behavior

• Evaluated once per query, before planning, then used wherever $name appears. let $now = now() gives every row the same instant.
• A value may use any function, request parameters, and variables declared before it (let $max = $min * 2, or a later let header). It cannot read document fields: let $x = qty is refused (cannot depend on documents).
• A name is bound once: redeclaring a variable, or declaring a name also given in params, is an error.
• ?= binds a default: only when the name is not bound already (by params, an earlier variable, or the query reading the view). This is how a view gives its parameters default values: let $gran ?= "month" | ….
• Variables are bound like parameters, as escaped literals: explain let $n = 2 | on orders | limit $n shows limit 2.

Query variables

A value written as a query in parentheses is the array of its rows, read once in the scope of the request:

let $settings = (on settings | limit 1)
| let $opening = get(at($settings, 0), "opening")
| on ledger | where month >= $opening

• The query may use the variables before it and read views.
• It must only read, and returns at most 1,000 rows (a query in let returns at most 1000 rows).
• It runs through query.execute (in a Place), like on view.

7.3 explain

explain <query>

Definition. Plans a query without running it and returns one document describing the plan. Authorization is the same as for the query itself.

explain on orders | where qty > 1 | sort price * qty desc | limit 3

{"access":"collection_scan","collection":"orders","executor":"governed_blocking","mode":"governed_blocking",
 "operators":[{"detail":"qty > 1","stage":"filter"},{"detail":"__og_sort_0 = price * qty","stage":"derive"},
 {"detail":"__og_sort_0 desc","stage":"sort"},{"detail":"__og_sort_0","stage":"drop"},{"detail":"3","stage":"limit"}],
 "read_only":true}

explain define view … checks a view definition without writing it. explain shows views expanded and parameters bound, so it is the tool to see what a view really runs.

8. Past states and history

on <collection> as of "<ISO date or datetime>" | …
on <collection> as of <generation> | …
on <collection> history | …

History needs a store that keeps it: OGD_HISTORY=on (memory and glacier backends). Without it, both forms fail with this store does not keep history.

8.1 as of

Definition. Reads the store as it was at a time or at a commit generation.

on orders as of "2026-09-01T12:00:00Z" | where status == "paid" | count
on orders as of 1842 | sort total desc | limit 10
on orders as of "2026-09-01" | into orders_restored        -- restore a past state

Behavior

• as of <string>: the state after the last commit made at or before that time (ISO 8601; a date alone means midnight UTC).
• as of <number>: the state at that commit generation.
• Every collection of the query is read at that point, lookup and union collections included.
• A date after the last commit reads the current state.
• Cost: the current state minus the documents changed since, plus the versions they had then. A collection not changed since reads at full speed.

Errors

• A generation after the current one, or before the start of the kept history.
• A date before the first recorded commit time.
• as of without a value keeps its old meaning: an alias named of (on users as of | where of.age > 3).

8.2 history

Definition. One row per stored write of the collection, in commit order.

on orders history | where _doc_id == "0190f3c2-…" | sort _generation
on users history | where _op == "delete" and _at >= "2026-10-01" | select _doc_id, _by, name

Each row holds the document of that version, plus:

These names replace document fields of the same name. Other collections of the query (lookup, union) are read in their current state. history reads every stored version of the collection.

8.3 Rules for both

• Read-only: a writing stage is refused, except a terminal into, which copies the rows into another collection (a restore).
• A Place or App instance scope applies to every version, through its _place/_app_instance fields.

9. Stages at a glance

The 28 built-in stages, in catalogue order. .query.stages lists the same catalogue from a running node.

Flow. Streaming stages handle one row at a time. Blocking stages need their whole input first (they spill to disk when it outgrows memory, chapter 14). Transactional stages write.

Choosing a stage

Entry layout

Every entry reads: Definition, Syntax, Behavior, Examples, Errors and limits, See also.

9.1 Filtering and shaping

Streaming stages that keep rows, or change each row into exactly one row.

where

Definition. Keeps the rows whose predicate is true.

Syntax

where <predicate>

Behavior

• The predicate is any expression giving a boolean. A row for which it gives false is dropped; any other kind is an error.
• Chained where stages narrow further; where a | where b is where a and b.
• A field the predicate reads must exist on every row reaching it (use field? or exists() otherwise).
• where _id == "<uuid>" runs as a primary-key lookup.
• The planner moves filters toward the source when that changes neither the rows nor the errors (14.2). A filter written after a view reaches the view’s source.

Examples

on users | where age >= 18 and city == "Paris" | select name, age

{"age":31,"name":"Alice"}
{"age":28,"name":"Dan"}

on users | where (city == "Paris" or city == "Lyon") and age < 30
on users | where active == false
on users | where profile.score >= 8
on users | where "dev" in tags
on users | where nick? == "Zed"                -- rows without nick are simply not kept
on orders | where status in ["paid", "pending"] and region not in ["US"]
on orders | where price * qty between 20 and 100
on orders | where starts_with(month, "2026-")

Errors and limits

• field path "x" is missing: a row lacks a field read without ?.
• operation "==" is incompatible with values …: a comparison of two kinds (age == "31").

See also exists, coalesce, single.

select

Alias: project

Definition. Keeps only the listed fields and named expressions, plus _id.

Syntax

select <item> [, <item>]*
item := <field.path> [as <field.path>] | <expression> as <field.path>

Behavior

• A field path keeps that field; a nested source is rebuilt nested (select profile.score gives {"profile":{"score":…}}).
• as renames, and may write a nested path (profile.country as geo.country).
• A computed item needs as.
• Items are evaluated left to right: a later item reads an alias introduced earlier (select price * qty as total, total > 50 as big).
• A missing field in a plain field item is skipped, not an error.
• At most one select per pipeline.

Examples

on orders | select order_id, price * qty as total, product as sku | limit 2

{"order_id":1,"sku":"P1","total":20.0}
{"order_id":2,"sku":"P2","total":99.5}

on users  | select name, profile.country as geo.country
on orders | select order_id, "fixed" as label, 42 as answer
on orders | project order_id, (price * qty) - 1 as net

See also derive, drop, rename.

derive

Definition. Adds or replaces computed fields, keeping the rest of the row.

Syntax

derive <field.path> = <expression> [, <field.path> = <expression>]*

Behavior

• Assignments run left to right: each sees the fields computed before it.
• A target path may be nested (meta.total = …) and may overwrite an existing field.
• The list splits only on top-level commas, so arrays and multi-argument calls are safe.
• It never changes stored data: use set to write.

Examples

on orders | derive total = price * qty, big = total > 100, label = if(big, "big", "small")
          | select order_id, total, label | limit 2

{"label":"small","order_id":1,"total":20.0}
{"label":"small","order_id":2,"total":99.5}

on orders | derive r = qty % 2, n = -qty
on users  | derive inactive = not active
on orders | derive flag = true, pair = [qty, qty * 2], obj = {"a": 1}, meta.total = price * qty
on sales  | derive month = date_format(date, "%Y-%m"), year = date_part(date, "year")

See also select, set, functions.

rename

Definition. Moves one field to another path.

Syntax

rename <source.path> as <target.path>

Behavior. The value moves; the source path disappears. A row without the source passes unchanged. Source and target must differ.

Examples

on users | rename name as full_name | select full_name, age
on users | rename profile.score as rating

See also select … as.

drop

Definition. Removes fields from the rows.

Syntax

drop <field.path> [, <field.path>]*

Behavior. Missing fields are ignored. Stored data is not changed.

Example

on users | drop tags, profile

{"active":true,"age":31,"city":"Paris","name":"Alice"}

See also select.

root

Definition. Replaces each row by the object stored at a path.

Syntax

root <field.path>

Example

on users | where name == "Alice" | root profile

{"country":"FR","score":9.5}

Pattern. After a lookup … | first | into hit, unwind hit | root hit turns the joined row into the row.

See also unwind, get.

9.2 Ordering, paging and sampling

sort

Definition. Orders rows by one or more keys.

Syntax

sort <key> [asc | desc] [, <key> [asc | desc]]*
key := <field.path> | <expression>

Behavior

• Stable: rows with equal keys keep their input order.
• The default direction is asc.
• A key may be any expression (sort price * qty desc, sort len(name), name): it is computed into a hidden field, sorted on, then dropped.
• Missing fields and null come first ascending, last descending. Different kinds sort by kind: booleans, numbers, strings, arrays, objects.
• Blocking: it needs every input row before emitting one; it spills sorted runs to disk past its memory.
• sort … | limit N (or | first, or | skip k | limit n) runs as a bounded Top-N holding only N rows.
• One sort per pipeline.

Examples

on orders | sort price desc | limit 2
on orders | sort price * qty desc | limit 3
on users  | select name, city | sort city, name
on orders | sort month desc, region

Errors and limits

• sort operator is duplicated.
• A number and a numeric text are not compared by value: convert first (derive n = to_float(code) | sort n).

See also limit, window, sort_array.

limit

Definition. Keeps the first n rows.

Syntax

limit <non-negative integer>

Behavior. Declares a row bound: the scan stops as soon as n rows have passed. After a blocking stage it bounds that stage (Top-N). One per pipeline; a parameter works (limit $n).

Examples

on orders | limit 10
on orders | sort qty desc | limit 3
on orders | skip 2 | limit 2 | select order_id

Errors. A negative or non-integer count is a planning error; limit operator is duplicated.

skip

Alias: offset

Definition. Drops the first n rows.

Syntax

skip <non-negative integer>

Examples

on orders | skip 2 | limit 2
on orders | offset 4
on orders | sort order_id | skip 10 | limit 5      -- a bounded Top-15, then the page

first

Definition. Keeps the first row that reaches it.

Syntax

first [<field.path>]

Behavior

• Declares a row bound of 1, like limit 1: the scan stops at the first row.
• Works inside lookup and union sub-pipelines (the first match per outer row).
• Without a path it is a pure bound, so sort … | first is a Top-1. With a path it also projects that field.
• Unlike limit, it may appear several times.

Examples

on users | first | select name
on users | first profile
on orders | select order_id, price | sort price desc | first
on users | lookup orders on user == name into o | …             -- every match
on users as u | lookup orders as o | where o.user == u.name | sort month desc | first | into last_order | end

See also single, first measure of group.

single

Definition. Lets exactly one row through, or fails.

Syntax

single [<field.path>]

Behavior. The first row passes; a second row fails the query, and so does the end of input with none. With a path it also projects.

Examples

on users | where name == "Alice" | single | select name, age
-- {"age":31,"name":"Alice"}
on users | where city == "Paris" | single
-- error: single expected exactly 1 row(s), found more
on users | where age > 100 | single
-- error: single expected exactly 1 row(s), found 0

Limits. When streaming, the first row may have been sent before a second one fails the query; the response still ends in error.

sample

Definition. Keeps n rows drawn uniformly at random (all of them when there are fewer).

Syntax

sample <n>

Behavior. A reservoir sample (algorithm R): memory bounded by n whatever the input size. Not deterministic, so a lookup holding sample is never shared between outer rows.

Examples

on orders | where status == "paid" | sample 100
on orders | select order_id | sample 3 | limit 2

9.3 Arrays, deduplication and counting

unwind

Definition. Emits one row per item of an array field, the item in place of the array.

Syntax

unwind <field.path> [with index <field.path>] [keep empty]

Behavior

• Items come out in order.
• A row whose field is missing or not an array passes through unchanged.
• A row whose array is empty is dropped, unless keep empty: it is kept with the field set to null.
• with index <field> stores the 0-based position of each item (null on rows not expanded).
• Streams mid-pipeline; works inside lookup sub-pipelines.

Examples

on users | unwind tags with index position | select name, tags, position

{"name":"Alice","position":0,"tags":"admin"}
{"name":"Alice","position":1,"tags":"dev"}
{"name":"Bob","position":0,"tags":"dev"}
{"name":"Dan","position":0,"tags":"ops"}

on users | unwind tags keep empty | where tags == null | select name     -- {"name":"Carol"}
on values [{m: 0}] | derive m = range(0, 12) | unwind m                  -- a row per month index
on users | lookup orders on user == name into o | unwind o | root o      -- the joined rows as rows

See also range, list measure.

distinct

Definition. Keeps one row per distinct value.

Syntax

distinct                               -- whole-row equality
distinct <field.path> [, <field.path>]*  -- the first row of each key

Behavior. Without fields, two rows are equal when every field is (_id aside, after a select). With fields, the first row of each key is kept whole. Keys compare as sort compares them. Blocking; after another blocking stage it keeps only the keys it has seen and emits each first row as it comes.

Examples

on orders | select status | distinct

{"status":"paid"}
{"status":"pending"}
{"status":"cancelled"}

on orders | distinct region, status
on orders | distinct product | sort product

count

Definition. Returns one row holding the number of input rows.

Syntax

count
count as <alias>

Behavior. Terminal: nothing follows it. The default field is count. Streaming: it counts without keeping rows. To count per value, use group <key>; to count a value inside a group, count <field> or count where <predicate>.

Examples

on orders | where price > 20 | count as expensive_orders
-- {"expensive_orders":3}
on orders | group status            -- a count per status

Errors. … follows a terminal operator when a stage follows it. To go on after counting, use the compound group with a constant key: derive k = 0 | group | by k | count as n | end | ….

9.4 Aggregation and analytics

Blocking stages that summarize rows (group, bucket, pivot) or compute across rows (window, fill). All run within the query’s memory budget and spill to disk past it.

group

Definition. Aggregates rows by keys: one output row per distinct key.

Compound form

group
| by <key> [as <alias>] [, <expression> as <alias>]*
| <measure> …
| end

Every measure accepts [where <predicate>] and [as <alias>]:

| sum amount where category in ["Sales", "Services"] as revenue
| count where amount < 0 as credits

Behavior

• by is required, exactly once, and may list several keys. A key may be an expression with as (by date_format(date, "%Y-%m") as month).
• Measures appear zero or more times, in any order. With none, the group emits count.
• A measure’s value may be an expression with as (sum price * qty as revenue).
• where on a measure counts the value as null on the rows it rejects: every measure ignores them, and count where p counts the kept rows. first/last with where need by <order>.
• median, percentile and stddev keep the group’s numbers (a list) and reduce them at the end.
• A collection is read in storage order, which a replaced row may change: use first … by <order> / last … by <order> for a defined order.
• Output rows come in key order.

Compact form

group <key> [as <alias>]                          -- count per key
group <key> [as <alias>], <item> [as <alias>]…    -- numbers summed, the rest keys

• One item: count per key.
• Several items: the first is a key. Each later item is summed when every value it takes is a number, and is a key otherwise. With no summed item, the group counts.
• What an item holds is known before the scan, from the storage’s per-field kinds, through the where, limit, skip, sort and derive stages before the group. An arithmetic or numeric function expression is a number; a comparison, a text or date function, a text or boolean field is a key.
• When that cannot be known (another stage before, a nested path, at/get), every item but the last is a key and the last is summed.
• A numeric code (an integer client number) is summed too: group by it with the compound form, or to_string(code) as code.

Examples

on orders | group | by region | sum qty as units | avg price | count as orders | end

{"avg_price":39.833333333333336,"orders":3,"region":"EU","units":8}
{"avg_price":119.83333333333333,"orders":3,"region":"US","units":8}

on orders | group status                                    -- {"count":4,"status":"paid"} …
on orders | group region, qty                               -- {"qty":8,"region":"EU"} …
on orders | group region as zone, month, qty as units
on orders | group region, qty, price * qty as revenue, status
on ledger
| group
    | by entity, account
    | last balance by month as closing
    | sum amount where category in ["Sales", "Services"] as revenue
    | count where amount < 0 as credits
    | sum amount * rate as converted
| end
on orders | group | by user | list product as products | end
on orders | group | by region | median price | percentile price 0.9 as p90 | stddev price | end

Patterns

• HAVING: filter after the group: group | by user | sum qty as units | end | where units > 3.
• Grand total: a constant key, derive all = "total" | group | by all | sum amount | end.
• Top N groups: group … | sort revenue desc | limit 10 (a bounded group then Top-N).

Errors and limits

• by missing or repeated.
• min/max on values of different kinds.
• group | limit N keeps only the N smallest live keys.

See also pivot, bucket, count, aggregate functions.

bucket

Definition. Counts rows per value range.

Syntax

bucket <expression> by [<b0>, <b1>, …] [default <value>] [as <alias>]

Behavior. Each row falls in the range [b(i), b(i+1)) named by its lower bound; rows outside every range go to default (or null). Bounds must be strictly ascending. The alias defaults to bucket. It is shorthand for derive <alias> = bucket(<expression>, [...], <default>) | group <alias>.

Example

on users | bucket age by [0, 18, 40] default "40+" as band

{"band":0,"count":1}
{"band":18,"count":2}
{"band":"40+","count":1}

See also the bucket function, to bucket without grouping.

pivot

Definition. Cross-tabulates rows: one output row per row key, one field per column value.

Syntax

pivot
| rows    <field> | <expression> as <alias> [, …]
| columns <field> | <expression> as <alias> [, …]
| values  <field> [as <alias>] | <expression> as <alias> [, …]
| aggregate sum | count | avg | average | min | minimum | max | maximum | first | last
| end

Behavior

• Each directive appears exactly once.
• values takes one or more measures, written like select items. With one measure, each cell is named after its column value; with several, <column>.<name> (names must be unique).
• A row is named after its alias; a column’s alias is required but unused (its values name the cells).
• Expressions in rows, columns or values are computed by a hidden derive before the pivot.
• Cells aggregate as group does: sums stay integers when exact (a cell with no number is 0), non-numbers are ignored by sum/avg, missing values skipped by every aggregate but count.
• A cell with no row is absent: read it with ? after the pivot.
• Stages may follow end.

Examples

on orders
| pivot | rows region | columns month | values qty | aggregate sum | end

{"2026-01":7,"2026-02":1,"region":"EU"}
{"2026-01":7,"2026-02":1,"region":"US"}

on sales
| pivot | rows substr(period, 0, 4) as year | columns kind | values amount | aggregate sum | end
| derive margin = coalesce(sales?, 0) - coalesce(costs?, 0)

See also group.

window

Definition. Adds to every row values computed over its ordered partition: previous and next values, running sums, ranks.

Syntax

window
| partition by <field> [, <field>]*
| order by <field> [asc | desc] [, …]
| lag <field> [<rows>] [as <alias>]
| lead <field> [<rows>] [as <alias>]
| cumsum <field> [as <alias>]
| first <field> [as <alias>]
| last <field> [as <alias>]
| row_number [as <alias>]
| end

Behavior

• order by is required, once; partition by is optional (the whole input is one partition without it); at least one function.
• Partition keys compare as sort compares them (1 and 1.0 share a partition).
• Functions run in order: one may read a field an earlier one added.
• Rows come out sorted by partition, then order, keeping all their fields.
• It is a stable sort (it spills) then a pass holding one partition at a time.

Example

on ledger
| window
    | partition by entity, account, fiscal_year
    | order by month
    | lag balance as previous
    | cumsum movement as ytd
    | row_number as n
| end
| derive variation = balance - coalesce(previous, 0)

Errors and limits

• a window partition exceeds the governed query working set: partition more finely.
• Partition and order keys are field paths: derive computed keys first.
• Top level or a union branch only.

fill

Definition. Adds, in each partition, a row for every value of a grid that no row holds: complete missing months, missing steps.

Syntax

fill <field> from <start> to <end> step <step> [by <field> [, <field>]*] [with <field> = <value> [, …]]

Behavior

• The grid is start, start + step, … up to end, inclusive.
• step is a positive number, or a date unit for date bounds (step "month", units as in date_add). A date grid is written as start is: "2026-01" gives "2026-02", "2026-03"…; a full date gives full dates; epoch milliseconds give milliseconds.
• A new row holds the partition’s by fields, the grid value and the with values; other fields are missing.
• Rows already there pass unchanged, values off the grid included. Output is sorted by the by fields then <field>.
• An empty input stays empty: a partition exists only through its rows.
• Bounds may be parameters (from $start to $end); at most 100,000 grid values.

Example

on ledger
| fill month from $opening to $closing step "month" by entity, account with balance = 0, movement = 0
| window | partition by entity, account | order by month | lag balance 12 as last_year | end

Limits. <field> and by are field paths; top level or a union branch.

9.5 Combining collections

lookup

Alias: join

Definition. For each row, runs a sub-pipeline over another source and stores its rows as an array field.

Syntax

lookup <source> [as <alias>]
| <read-only stages>
| into <field>
| end

lookup <source> [as <alias>] on <joined field> == <outer field> into <field>     -- one-line equality

source := <collection> | view <name> [version <n>] | values [{…}, …]

Behavior

• into <field> is required, exactly once. The field holds an array (empty when nothing matches).
• The sub-pipeline accepts read-only stages; on, from, with and chunk are refused inside it.
• Field scope. Inside the sub-pipeline, <lookup alias>.f reads the joined row, <outer alias>.f the row being enriched (the source alias, or the alias of the enclosing lookup/union), and unqualified names the joined row. Any expression may mix both.
• Row bounds work inside (| first | attaches only the first match), and so do blocking stages (sort | first, group, distinct), run on the rows joined to each outer row. Stages may follow the end.
• The one-line form expands to lookup <c> | where exists(…) and exists(…) and <joined> == <outer> | into <field> | end: rows where either field is missing do not match. Its left side reads the joined collection, its right side the outer row.
• lookup view <name> needs a view whose source is a bare on <collection> or on values […]; as <alias> names the view’s source, which must not have one already.

Execution

• When the sub-pipeline’s first where begins (after exists guards) with joined == outer, or starts_with(outer, joined), the joined source is indexed once on the joined field. An equality probes one key; a prefix probes each prefix length present (longest-prefix rules read only matching rules).
• Several leading equalities make one composite key; a side read with ? keys a missing field as null (where s.entity == t.entity and s.version? == t.version?).
• When the sub-pipeline reads only known fields of the outer row and is deterministic (no sample, no now()), its result is kept per value of those fields: outer rows sharing them share one run.
• The index and kept results live within the query working set; past it, rows are joined by scan.

Examples

on orders | lookup products on code == product into product
on users as u | lookup orders as o on o.user == u.name into orders

on users as u
| where age > 30
| lookup orders as o
    | where o.user == u.name and o.price * o.qty > 20
    | select order_id, price
    | into big_orders
| end

{"age":31,"big_orders":[{"order_id":2,"price":99.5}],"name":"Alice",…}
{"age":45,"big_orders":[{"order_id":4,"price":250.0},{"order_id":6,"price":10.0}],"name":"Carol",…}

-- Longest-prefix classification
on ledger as a
| lookup rules as r
    | where starts_with(a.account, r.prefix)
    | derive length = len(prefix) | sort length desc | first
    | select category
    | into rule
| end
| derive category = get(at(rule, 0), "category", "unclassified")

-- The single match as a value
on sales as s
| lookup prices as p on p.article == s.article into p
| derive cost = if(len(p) == 1, get(at(p, 0), "cost", null), null)
| drop p

-- Join a view, or literal rows
on orders | lookup view catalog.products as c on c.code == product into product
on ledger | lookup values [{code: "601", label: "Purchases"}] as v on code == account into hit

See also union, at, get, unwind.

union

Definition. Emits the main stream, then the rows of another pipeline.

Syntax

union
| on <source> [as <alias>]
| <read stages>
| end

Behavior

• The first sub-stage must be on <source> (a collection, view <name>, or values […]). from is refused there.
• A branch takes any read pipeline (sort, group, window, fill, lookup, another union…); it then runs as a query of its own, governed like the main one.
• Rows of both sides keep their own fields: align them with select when needed.
• Stages may follow the end; a filter after it is also copied into the branch by the planner.

Examples

on users | select name | union | on products | select label | end

{"name":"Alice"}
{"name":"Bob"}
{"name":"Carol"}
{"name":"Dan"}
{"label":"Pen"}
{"label":"Printer"}
{"label":"Phone"}

on actual | group | by month | sum amount as a | end
| union | on forecast | group | by month | sum amount as a | end | end
| group | by month | sum a as a | end

on view sales.eu | union | on view sales.us | end | union | on view sales.asia | end

See also lookup, views.

9.6 Writes

Every write query runs in one transaction: all its changes commit, or none do. Write stages are terminal. The response holds the written documents (for insert and set) and the counters inserted, replaced, deleted.

A write streams its rows into the transaction and charges what it holds to the query memory budget. Past it, a Glacier transaction moves its pending writes to disk and commits them as one at the end; the memory backend fails the write instead.

In a Place or App instance, written rows get the scope fields (_place, _app_instance), and a write reads only that scope’s rows.

insert

Definition. Inserts one document, or an array of documents.

Syntax

insert { <key>: <value>, … }
insert [ { … }, { … }, … ]

Behavior

• Keys may be bare identifiers or JSON strings; values are JSON (nested objects and arrays included).
• One object: inserted and returned with its generated _id.
• An array: every object in one transaction, as a streaming load in merge mode. Rows without _id get one; a row with the _id of an existing document is merged into it (not in a Place scope, where a row may not merge into a stored document).
• insert is the only stage of its pipeline.

Examples

on users | insert {name: "Frank", age: 52, tags: ["new"], profile: {country: "IT"}, active: true}
-- {"_id":"01a0d795-…","active":true,"age":52,"name":"Frank","profile":{"country":"IT"},"tags":["new"]}
on users | insert [{name: "Gus", age: 20}, {name: "Hal", age: 33}]

set

Definition. Updates fields of every document reaching it.

Syntax

set <field.path> = <expression> [, <field.path> = <expression>]*

Behavior

• Paths may be nested and are created when missing. = null stores null (it does not remove the field).
• As in an SQL UPDATE, every value is computed from the document as it was before the set: set a = b, b = a swaps two fields.
• It acts on exactly the documents that reach it, so sort, skip and limit may come before it.
• Returns the updated documents.

Examples

on users | where name == "Bob" | set active = true, profile.level = "gold", age = 18
on users | where name == "Bob" | set age = age + 1, nickname = lower(name), grade = if(profile.score >= 7, "B", "C")
on users | sort name | limit 1 | set top = true
on counters | where name == "visits" | set value = value + 1

delete

Definition. Deletes every document reaching it.

Syntax

delete

Examples

on orders | where status == "cancelled" | delete
on staging | delete                    -- empties the collection

into

Alias: out

Definition. Inserts every row reaching it into another collection.

Syntax

into <collection>

Behavior

• Returns no rows; the counters report inserted.
• Each stored row gets a fresh _id, even when the pipeline kept the source _id. The source is never modified.
• The target must differ from the source and cannot be a system collection (_…).
• Authorization: write on the target, read on the source.
• It is the only write allowed after as of or history (a restore).

Examples

on users | where active | into archive
on orders | group | by region | sum qty as units | end | into region_totals
on orders as of "2026-09-01" | into orders_restored

load

Definition. Imports or upserts batches of documents.

Syntax

load
| with replace | update | merge
| chunk [ {…}, {…}, … ]
| chunk [ … ]
| end

Behavior

• with sets the mode, exactly once; at least one chunk, each a JSON array of objects.
• Rows without _id get a fresh one; rows with an _id match stored documents:

• Atomic: an invalid _id, a duplicate _id in one request, or an update miss rolls back every chunk.
• load is the first stage.
• The compact form load <target> is a runtime hook (in ogd it rewrites the documents unchanged); use the streaming form for imports.

Examples

on customers | load | with merge | chunk [{"_id":"01900000-0000-7000-8000-00000000c001","name":"ACME","tier":"gold"}] | end
on customers | load | with update | chunk [{"_id":"01900000-0000-7000-8000-00000000c001","tier":"platinum"}] | end
on tmp | load | with merge | chunk [{"n":1},{"n":2}] | chunk [{"n":3}] | end      -- 3 inserts

9.7 Vector search

near

Definition. Scores each row by the cosine distance between a vector field and a query vector.

Syntax

near <vector.field>, [<number>, <number>, …]
near <vector.field>, $vector

Behavior

• Writes the distance to _distance (0 means the same direction, 2 the opposite).
• Rows without the field are discarded; a dimension mismatch is an error.
• It does not reorder rows: pair it with sort _distance | limit k for k nearest neighbours, or with where _distance < t for a radius search.
• Streaming; combine with filters before it to search within a subset.

Examples

on products | near embedding, [0.1, 0.9, 0.0] | select code, label, _distance | sort _distance | limit 2

{"_distance":0.006116265326381098,"code":"P3","label":"Phone"}
{"_distance":0.7804878048780488,"code":"P2","label":"Printer"}

on products | near embedding, [0.0, 1.0, 0.0] | where _distance < 0.5 | select code, _distance
on products | where category == "office" | near embedding, $vector | sort _distance | limit 5

10. Functions at a glance

74 functions, called by name: round(price * qty, 2). Names are not reserved words, arguments are any expressions, and the argument count is checked when the query is parsed. Arity reads min..max (n.. for no maximum).

group measures (sum, avg, min, max, first, last, count, list, median, percentile, stddev) and window functions (lag, lead, cumsum, first, last, row_number) are directives of their stage, not calls.

Common rules

• Missing arguments. A function reading a missing field fails, like any expression, except exists, coalesce and optional, made for that. Write field? to pass null instead.
• Null. There is no general null propagation: numeric, text and date functions refuse null (`upper` expects a string, found Null). The functions that accept it: coalesce, exists, optional, nullif, if/case branches, concat (skips it), to_int/to_float/to_string (keep it), type, merge (skips it), and the aggregates sum, avg, median, percentile, stddev (skip null items; min and max refuse them).
• Kinds. Functions check their argument kinds: upper(42) is an error. Convert with to_string, to_int, to_float.
• Positions. Text positions count characters; negative positions count from the end; arrays are 0-based.
• Determinism. Every function is deterministic except now().

10.1 Numeric functions

Numeric functions expect numbers: a null, a string or a missing field is an error (`abs` expects a number, found Null). Guard optional values with coalesce(x?, 0) or if(x? == null, …, …). The aggregates sum, avg, median, percentile and stddev are the exception: they skip null items (min and max do not).

abs

abs(x) → the absolute value. An integer stays an integer.

abs(-3)        → 3
abs(-2.5)      → 2.5
abs(price - 100)

sqrt

sqrt(x) → the square root, as a float. A negative input is an error (`sqrt` expects a non-negative number).

sqrt(16)       → 4.0

pow

pow(x, y) → x to the power y; an integer when the result is exact.

pow(2, 10)     → 1024
pow(2, 0.5)    → 1.4142135623730951

round

round(x [, digits [, mode]]) → x rounded.

A value written as a decimal half (2.675) counts as one, even though its binary float is slightly below. An integer input is returned unchanged.

round(13.333, 2)               → 13.33
round(2.675, 2)                → 2.68
round(2.5)                     → 3
round(-2.5)                    → -3
round(0.125, 2, "half_even")   → 0.12

Errors. digits outside 0..15; a mode other than the two above.

trunc

trunc(x [, digits]) → x rounded toward zero, same rules as round.

trunc(-2.7)        → -2
trunc(3.14159, 2)  → 3.14

floor

floor(x) → the largest integer not above x.

floor(12.5)    → 12
floor(-2.5)    → -3

ceil

ceil(x) → the smallest integer not below x.

ceil(2.1)      → 3

sign

sign(x) → -1, 0 or 1.

sign(-3)       → -1

ln

ln(x) → the natural logarithm, as a float; x must be positive.

ln(1)          → 0.0

log10

log10(x) → the base-10 logarithm, as a float; x must be positive.

log10(1000)    → 3.0

log

log(x, base) → the logarithm of x in base, as a float.

log(8, 2)      → 3.0

exp

exp(x) → e to the power x, as a float.

exp(0)         → 1.0

min

min(a, b, …) → the smallest argument. With one array argument, the smallest item; null for an empty array. Values must be comparable (numbers with numbers, strings with strings): a null item is an error (`min` cannot compare Null and Number…), whereas the min measure of group skips null.

min(3, 7, 1)        → 1
min([3, 1, 2])      → 1
min("b", "a")       → "a"

max

max(a, b, …) → the largest argument, or the largest item of one array argument; same rules as min.

max(age, 30)
max(scores)

sum

sum(a, b, …) → the sum. With one array argument, the sum of its items. null items are skipped; the result stays an integer while every term is; 0 for an empty array.

sum(1, 2, 3)          → 6
sum([])               → 0
sum([1, null, 2.5])   → 3.5
sum(scores)

avg

avg(a, b, …) → the mean, or the mean of one array argument’s items; null items skipped; null when there is nothing to average.

avg(1, 2)      → 1.5
avg([])        → null

median

median(a, b, …) → the middle value (the mean of the two middle values for an even count), as a float; one array argument works too; null items skipped; null when empty.

median([3, 1, 2])      → 2.0
median([1, 2, 3, 4])   → 2.5

percentile

percentile(array, fraction) → the value at fraction (0 to 1) of the sorted numbers, linear between the two closest values (a spreadsheet’s inclusive percentile); null when empty.

percentile([3, 1, 2], 0.5)     → 2.0
percentile(scores, 0.9)

stddev

stddev(a, b, …) → the sample standard deviation; one array argument works too; null with fewer than two values (stddev([1]) → null).

stddev([3, 1, 2])      → 1.0

div

div(a, b [, fallback]) → a / b, or fallback when b is 0 (null without a fallback). The safe division: / by zero is an error.

div(7, 2)          → 3.5
div(1, 0)          → null
div(1, 0, -1)      → -1
div(margin, revenue, 0)

Operators. + - * / % follow 4.5: integers stay integers while they fit, / always gives a float (7 / 2 → 3.5), % is the remainder (7 % 3 → 1).

10.2 Logic, missing values and conversions

if

if(condition, then, else) → then when the condition is true, else else. The condition must be a boolean. Only the chosen branch is evaluated, so the other may read a missing field.

if(age >= 18, "adult", "minor")
if(qty == 0, null, price / qty)
if(true, 1, nope)          → 1          -- `nope` is never read

case

case(c1, v1, c2, v2, …, [default]) → the value of the first true condition, else default, else null. Lazy, like if.

case(qty > 5, "bulk", qty > 1, "few", "one")
case(false, 1)             → null
case(false, 1, 2)          → 2

coalesce

coalesce(a, b, …) → the first argument that is present and not null; null when none is. Missing arguments do not fail: coalesce is made for optional fields.

coalesce(nick, name)
coalesce(nope, n, 3)       → 3          -- nope missing, n null
coalesce(sales?, 0) - coalesce(costs?, 0)

exists

exists(x) → true when x is present (even null), false when missing. Never fails.

exists(nickname)
where exists(date) and date >= "2026-01-01"

optional

optional(x) → x, or null when missing. x? is the same.

optional(nick)             -- same as nick?

nullif

nullif(a, b) → null when a == b, else a. Turns a sentinel into null.

nullif(status, "")
coalesce(nullif(label, ""), code)

between

between(x, low, high) → low <= x and x <= high. x between low and high is the operator form.

between(5, 1, 10)          → true
where price between 10 and 100

bucket

bucket(x, [b0, b1, …, bn] [, default]) → the lower bound b(i) of the range [b(i), b(i+1)) holding x, else default (or null). The last bound closes the last range: with [0, 18, 65], 65 and above fall to the default. Bounds must be strictly ascending.

bucket(25, [0, 18, 65])            → 18
bucket(70, [0, 18, 65], "old")     → "old"
derive band = bucket(age, [0, 18, 30, 50, 120])

See also the bucket stage, which also counts.

to_int

to_int(x) → an integer from a number (truncated toward zero), a numeric string or a boolean (true → 1). null stays null.

to_int("42")       → 42
to_int(3.9)        → 3
to_int(-3.9)       → -3
to_int(null)       → null
to_int("x")        → error: `to_int` cannot convert String("x")

to_float

to_float(x) → a float from a number, a numeric string or a boolean; null stays null.

to_float("2.5")    → 2.5

to_string

to_string(x) → text. Numbers, booleans, arrays and objects are written as JSON; null stays null.

to_string(qty)     → "3"
to_string([1,2])   → "[1,2]"
group | by to_string(client_code) as client | … | end

to_bool

to_bool(x) → a boolean from a boolean, a number (non-zero is true), or the texts true/false, 1/0, yes/no. Anything else is an error.

to_bool("yes")     → true
to_bool(0)         → false
to_bool("maybe")   → error: `to_bool` cannot convert "maybe"

type

type(x) → the kind of a value: "null", "bool", "number", "string", "array" or "object".

type(1.5)          → "number"
type(profile)      → "object"
where type(code?) == "string"

Operators. and, or, not (and &&, ||, !) need booleans and short-circuit; in / not in test membership in an array; between … and … tests a range. See chapter 5.

10.3 Text functions

Positions count characters, not bytes; a negative start counts from the end. Text functions expect strings: a null, a number or a missing field is an error (`upper` expects a string, found Null), except where noted. Convert first with to_string, or guard with coalesce(x?, "").

len

len(x) → the characters of a string, the items of an array, or the keys of an object.

len("Élodie")      → 6
len(tags)          → 2
len(profile)       → 2
where len(join_1) == 1

lower

lower(s) → s in lower case (Unicode).

upper

upper(s) → s in upper case (Unicode).

upper(trim(name))

trim

trim(s) → s without leading and trailing white space.

trim("  x ")       → "x"

ltrim

ltrim(s) → s without leading white space.

rtrim

rtrim(s) → s without trailing white space.

concat

concat(a, b, …) → the arguments joined as text. Numbers and booleans are written as text; null is skipped. When every argument is an array, the arrays are concatenated instead.

concat(name, "@", lower(city))
concat("x", true, 2.5)     → "xtrue2.5"
concat("a", null, 1)       → "a1"
concat([1], [2])           → [1,2]
concat(tags, ["new"])

contains

contains(x, y) → for strings, whether y is a substring of x; for arrays, whether y is an item.

contains("Paris", "ar")    → true
contains(tags, "dev")

starts_with

starts_with(s, prefix) → whether s begins with prefix. In a lookup, starts_with(outer, joined) is indexed (longest-prefix rules).

starts_with(account, "60")

ends_with

ends_with(s, suffix) → whether s ends with suffix.

ends_with("abc", "bc")     → true

substr

substr(s, start [, length]) → the substring from start (0-based; negative from the end), up to length characters (to the end without it).

substr("Paris", 0, 3)      → "Par"
substr("Paris", 2)         → "ris"
substr("Ab", -1)           → "b"
substr(period, 0, 4) as year

split

split(s, separator) → the array of parts. An empty separator splits into characters.

split("a,b", ",")          → ["a","b"]
split("Ab", "")            → ["A","b"]

replace

replace(s, from, to) → s with every occurrence of from replaced by to.

replace("a-b-c", "-", "+")     → "a+b+c"

index_of

index_of(x, y) → the position of y in a string or an array, -1 when absent.

index_of("Ab", "b")        → 1
index_of(tags, "dev")      → 1

pad_left

pad_left(s, width [, fill]) → s padded on the left to width characters with fill (a space by default).

pad_left(to_string(qty), 3, "0")   → "003"

pad_right

pad_right(s, width [, fill]) → s padded on the right.

pad_right("ab", 4)         → "ab  "

reverse

reverse(x) → a string or an array reversed. See arrays.

Comparison of text is by code point: "10" < "9" is true. Compare numbers as numbers (to_int, to_float), and dates as ISO strings of one shape or as epoch milliseconds.

10.4 Array and object functions

Arrays are 0-based; a negative index counts from the end.

at

at(array, i) → the item at i, null when out of range.

at(tags, 0)
at([3, 1, 2], -1)          → 2
at([3, 1, 2], 5)           → null
get(at(join_1, 0), "groupe", null)       -- the first joined row's field

slice

slice(array, start [, length]) → the sub-array from start, up to length items.

slice([3, 1, 2], 1)        → [1,2]
slice([3, 1, 2], -2, 1)    → [1]

reverse

reverse(x) → the array (or string) reversed.

reverse(tags)
reverse("Ab")              → "bA"

sort_array

sort_array(array [, "asc" | "desc"]) → a sorted copy; items must be comparable.

sort_array([3, 1, 2], "desc")      → [3,2,1]

unique

unique(array) → the distinct items, the first occurrence kept.

unique([1, 1, 2])          → [1,2]

flatten

flatten(array) → the array flattened by one level.

flatten([[1], [2, [3]]])   → [1,2,[3]]

range

range(start, end [, step]) → the integers from start up to, not including, end; at most 1,000,000 items.

range(0, 6, 2)             → [0,2,4]
on values [{m: 0}] | derive m = range(0, 12) | unwind m        -- twelve rows

keys

keys(o) → the keys of an object, sorted.

keys(profile)              → ["country","score"]

values

values(o) → the values of an object, in key order.

values({"k": 1})           → [1]

values is also the source of literal rows, on values [{…}]. In stage position it is a source; in an expression it is this function.

get

get(o, key [, default]) → the value under key, including keys that are not identifiers; default (or null) when absent.

get(meta, "display name", "")
get(o, "z", 0)             → 0
get(coalesce(at(o, 0), {}), "path", "")

has

has(o, key) → whether key is present.

has(profile, "score")

merge

merge(o1, o2, …) → a shallow merge, later objects winning; null arguments skipped.

merge(profile, {"level": 2})
merge({"k": 1}, {"z": 2}, null)    → {"k":1,"z":2}

Membership. x in array and x not in array are operators: "dev" in tags, status in ["paid", "pending"]. contains(array, x) is the function form.

10.5 Date and fiscal functions

There is no date type (4.7). Every function below reads a date as epoch milliseconds or an ISO-8601 string (2026-03-09, 2026-03, 2026-03-09T10:00:00Z, 2026-03-09 10:00:00+02:00), computes in UTC, and returns epoch milliseconds, except date_part (a number), date_format (text), fiscal_year, fiscal_month and days_in (numbers). A null or a value of another kind is an error (`date_format` expects epoch milliseconds or an ISO-8601 string, found Null).

Units: year, quarter, month, week (weeks start on Monday), day, hour, minute, second, millisecond, singular or plural.

Epoch milliseconds of today exceed 2³², so responses carry them as floats (1772323200000.0): the wire rule of 4.5. Inside a query they stay integers.

now

now() → the current time, epoch milliseconds. Not deterministic: let $now = now() gives every row the same instant.

parse_date

parse_date(t) → t as epoch milliseconds (a number is returned as it is).

parse_date("1970-01-02")   → 86400000
parse_date("2026-03")      → 1772323200000          -- 2026-03-01T00:00:00Z

date_trunc

date_trunc(t, unit) → the start of the unit holding t.

date_trunc("2026-03-09T10:00:00Z", "month")             → 2026-03-01
date_format(date_trunc("2026-03-11", "week"), "%F")     → "2026-03-09"   -- a Monday

date_add

date_add(t, n, unit) → t moved by n units (n may be negative). Adding months or years clamps the day to the end of the month.

date_format(date_add("2026-01-31", 1, "month"), "%F")   → "2026-02-28"
date_format(date_add("2026-01-31", -2, "days"), "%F")   → "2026-01-29"
date_add("2024-01-01", m, "month")

date_diff

date_diff(start, end, unit) → the number of unit boundaries crossed from start to end (negative when end is before start).

date_diff("2025-12-31", "2026-01-01", "year")     → 1
date_diff("2026-01-01", "2026-03-15", "months")   → 2
date_diff("2026-03-01", "2026-01-01", "month")    → -2

date_part

date_part(t, part) → one part of the date, as a number.

date_part("2026-03-09", "day_of_week")    → 1
date_part("2026-03-09", "week")           → 11
date_part("2026-03-09", "quarter")        → 1
date_part("2026-03-09", "day_of_year")    → 68

Errors. unknown date part "dow"; expected year, quarter, month, week, day, day_of_week, day_of_year, hour, minute, second or millisecond.

date_format

date_format(t [, pattern]) → text. Without a pattern, ISO-8601 with milliseconds (2026-03-09T10:20:30.000Z).

date_format("2026-03-09", "%Y-%m")               → "2026-03"
date_format("2026-03-09", "%j %u %V %F")         → "068 1 11 2026-03-09"
date_format("2026-03-09T10:20:30.123Z", "%T %L") → "10:20:30 123"

Fiscal years

A fiscal year is described by its closing: either the month it closes at the end of (1 to 12; 12 is the calendar year), or the date it opens on (a timestamp on the first day of a month, repeated every year). A fiscal year is named after the calendar year it closes in.

fiscal_year

fiscal_year(t, closing) → the fiscal year holding t.

fiscal_year("2026-07-01", 6)               → 2027      -- July 2026 – June 2027
fiscal_year("2026-07-01", "2019-07-01")    → 2027      -- the same year, by its opening date

fiscal_month

fiscal_month(t, closing) → the month within the fiscal year, 1 to 12.

fiscal_month("2026-07-01", 6)              → 1

fiscal_start

fiscal_start(t, closing) → the first day of the fiscal year.

date_format(fiscal_start("2026-07-01", 6), "%F")   → "2026-07-01"

fiscal_end

fiscal_end(t, closing) → the last day of the fiscal year.

date_format(fiscal_end("2026-07-01", 6), "%F")     → "2027-06-30"

month_end

month_end(t) → the last day of the month.

date_format(month_end("2024-02-10"), "%F")         → "2024-02-29"

days_in

days_in(t, unit) → the number of days in the unit holding t.

days_in("2024-02-10", "month")     → 29
days_in("2024-02-10", "quarter")   → 91
days_in("2024-02-10", "year")      → 366
days_in("2024-02-10", "week")      → 7

Date recipes

-- Month of an ISO date, as text
derive month = date_format(date, "%Y-%m")

-- The same month as epoch milliseconds
derive month = date_trunc(date, "month")

-- ISO year-week label
derive week = concat(date_format(date, "%Y"), "-W", date_format(date, "%V"))

-- Last 12 months from a parameter
let $from = date_add($to, -11, "month") | on sales | where parse_date(date) >= $from and parse_date(date) <= month_end($to)

-- Fiscal year closing in June
derive fy = fiscal_year(date, 6), fm = fiscal_month(date, 6)

-- Pro rata of a monthly amount
derive per_day = amount / days_in(date, "month")

11. Views

A view is a named read query kept in the _views collection, one document per version. Views are how a data model is written in OGML: each view reads collections or other views, and queries, reports and other views read it like a collection.

define view sales.paid
| description "Paid orders with their total"
| exec
  | on orders | where status == "paid" | derive total = price * qty
| end
| end

{"name":"sales.paid","version":1,"state":"active","description":"Paid orders with their total",
 "query":"on orders | where status == \"paid\" | derive total = price * qty",
 "parameters":[],"reads":["orders"],"materialized":false,"createdBy":"…","createdAt":…}

11.1 define view

Definition. Writes a new version of a view.

Syntax

define view <name>
[| description "<text>"]
[| materialized]
| exec
  | <query>
| end
| end

define view <name> | description "<text>" | end      -- a new version, another description
define view <name> | version <n> | end               -- restores version n as a new version
define view <name> | end                            -- a new version, identical

Written without | (3.2), the view of the chapter opening reads:

define view sales.paid
description "Paid orders with their total"
exec
  on orders
  where status == "paid"
  derive total = price * qty
end
end

Its query keeps that text, line breaks included.

Behavior

• <name> is identifiers joined by dots: ledger.cube, sales.by_month.
• exec is the last clause; the body runs from exec to the two final end (the body’s own end stay in it).
• Each define writes a new version and marks the previous active one superseded. Without description, the description is kept; without materialized, a restore or a copy keeps the flag of the version it copies.
• The body is checked when defined: planned with its parameters read as 1, and refused when it writes (view.invalid: a view only reads: its query cannot insert, set, delete or load), when it reads a view that does not exist, or when it makes a cycle.
• The definition records:

• History is never rewritten: every version stays in _views.

Who may define views. In a Place, its Owner. Outside a Place, a standard view, which needs the right to write _views. explain define view … checks without writing.

Errors and limits

11.2 undefine view

undefine view <name>

Marks the active version deleted. The standard view of the same name, if any, is read again by the Place. The cache of a materialized view leaves with it.

11.3 Reading a view

on view <name> [version <n>] [as <alias>]
lookup view <name> [version <n>] as <alias> | … | end
lookup view <name> as <alias> on <joined field> == <outer field> into <field>
union | on view <name> | … | end
let $rows = (on view <name> | …)

Behavior

• The view’s query replaces the source before the query is planned: the plan, explain and authorization see the collections it reads, and the stages that follow are optimized with it (a filter after a view reaches the view’s source, 14.2).
• In a Place, the Place’s view is read, else the standard view of that name; its active version, or version <n>.
• lookup view needs a view whose source is a bare on <collection> or on values […]; as <alias> names the view’s source, which must not have one already.
• Views are resolved by the query service (the query.execute operation), not by the bare pipeline layer of the engine library.

Examples

on view sales.paid | group | by region | sum total | end
on view ledger.cube version 1 | union | on view ledger.cube | end
on orders | lookup view catalog.products as c on c.code == product into product

11.4 Parameters

A view’s parameters are the $name its body reads without declaring. They are bound by the reading query: request params, or its let variables.

define view sales.since | exec | on orders | where month >= $since | end | end

let $since = "2026-02" | on view sales.since

• let $name ?= <default> in the body gives a default, used when the reader binds nothing:

define view orders.big | exec | let $min ?= 2 | on orders | where qty >= $min | end | end

on view orders.big | count                     -- {"count":4}
let $min = 4 | on view orders.big | count      -- {"count":2}

• The view’s own let stay local: they may shadow a variable of the reading query, which cannot read them.
• An unbound parameter fails the reading query: $scenario is not bound.
• A materialized view takes no parameter: its body binds them with let.

11.5 Listing views

on _views | where state == "active" | select name, version, parameters, reads, materialized
on _views | where name == "sales.paid" | sort version | select version, state, createdAt, query

In a Place, _views shows only that Place’s views and the standard ones; only define and undefine write it.

11.6 Standard views

A view defined outside any Place is a standard view: every Place reads it, unless the Place defines a view of the same name, which then takes its place for that Place. Standard views suit definitions shared by every Place (a calendar, a reference classification); Place views suit what one Place models for itself.

Never assume a standard view exists: a node may hold none. Read on _views first, or define what you need.

11.7 Views and Places

A view never reads across Places: whichever view is read, its sources read the rows of the Place running the query.

12. Materialized views and caches

og-core keeps two kinds of computed rows. They are independent and can both serve one request.

12.1 Materialized views

define view sales.by_month | materialized | exec
  | on view sales.paid | group | by month, region | sum total | end
| end
| end

The first read in a scope computes the rows into a cache collection; later reads in that scope read it, as fast as reading a small collection. A scope is the reading Place and App instance, plus the access filters of the reader when its Place access filters rows (such a reader gets rows of its own, computed under its filters).

The rows are computed again when, since they were computed:

1. a collection the view reads changed for that Place: a write of the Place, or a write og-core cannot attribute to one Place (an unscoped write, a load, a direct storage write), which counts for every Place. A write in another Place leaves this Place’s cache alone;
2. a view it reads (at any depth) was redefined, restored or undefined: the cache records the name, version and Place of each view it reads, and the collections it watches are those of the views as they are defined now;
3. the view itself got a new version: its cache key holds its version;
4. ogd restarted (what it knew of the writes is not kept).

They are computed into the scope’s other cache collection (slots 0 and 1), so a reader still reading the previous rows is not cut short.

Automatic refresh. When a view is redefined (define view, a restore, undefine view), ogd hands it to a background worker that computes again, in every scope a cache already exists for, the materialized views reading it (and the view itself). The next reader finds the rows ready. A scope filtered by a Place access is left to its next read.

Clean-up. The rows of an earlier version of a view in the same scope are dropped once the new ones are in; the cache of an undefined view is removed by the refresh.

Rules

• A materialized view takes no parameter: its body binds what it needs with let. Filters written after on view apply to the cached rows (they reach the cache like any source).
• A view defined over a materialized view lists the collections it reads among its own reads.
• Only og-core writes the cache: a query writing _cache… is refused.

Listing caches

on _cache | select view, version, place, appInstance, slot, collection, computedAt, reads, changed

When to materialize

A common shape: a materialized base (the heavy join and classification, no parameters), and inline views over it that take the parameters (period, scenario) and filter the cached rows.

define view ledger.cube.all | materialized | exec | … heavy classification … | end | end
define view ledger.cube | exec | on view ledger.cube.all | where date >= $from and date <= $to | end | end

12.2 The result cache

ogd keeps the rows of read requests that a Place repeats: clients ask the same views again each time a report opens, and every open client asks again after an import.

• The key is built from the prepared query text (views expanded, parameters bound), the scope, and the change tokens of the collections it reads: a write of the Place makes a new key, so kept rows are never stale.
• A read cheaper than 20 ms runs again rather than being kept.
• Rows nobody asks again for a minute leave.
• A read arriving while the same one runs waits for its rows instead of running twice.
• The budget is the results memory class of the profile (128 MiB by default, at most 1/16 of the managed memory); an entry larger than a quarter of it is not kept.
• OGD_RESULT_CACHE=off disables it.

A read of a materialized view uses both: the view’s rows come from its cache collection, and the response to a repeated request comes from memory.

12.3 Choosing what to cache

• Nothing to do for repeated identical reads: the result cache handles them.
• Materialize what is expensive to compute and read by many different queries (other views, different filters, different reports): the result cache cannot share across different queries, the view cache can.
• Keep the cache small: materialize the aggregated or classified shape, not a copy of the raw collection.

13. Modeling patterns

A data model in OGML is a graph: collections (raw data, imports, settings), views that read them and each other, and the queries of reports reading the views.

collection data_ventes ─┐
collection data_prix ───┼─► view sales.rows ─► view sales.margins (materialized) ─► reports
collection settings ────┘

13.1 Layers

Keep each view small and named after what it holds; stack them with on view.

13.2 Clean the raw data once

define view production.rows | exec
  | on data_production
  | where day? != null                                    -- empty spreadsheet rows have no day
  | derive date = date_format(day, "%F"), week = date_format(day, "%Y-W%V"), weight = to_float(weight)
  | select date, week, site, product, units, weight
| end
| end

• Guard every field that some rows may lack, with ?.
• Convert once (to_float, to_string, date_format), so the layers above never repeat it.
• select the columns the model needs: later views and caches stay small.

13.3 Reference data and classification

-- Exact match, one row expected
on view sales.rows as s
| lookup prices as p on p.article == s.article into p
| derive cost = if(len(p) == 1, get(at(p, 0), "cost", null), null)
| drop p

-- Longest prefix wins (chart of accounts)
on ledger as a
| lookup rules as r | where starts_with(a.account, r.prefix) | derive n = len(prefix) | sort n desc | first | into rule | end
| derive category = get(coalesce(at(rule, 0), {}), "category", "unclassified")

-- A small fixed table, in the query
| lookup values [{code: "A", label: "Retail"}, {code: "B", label: "Wholesale"}] as f on code == family into f

13.4 Stacking sources

-- Actual and budget side by side, aligned on the same fields
on view ledger.actual | select month, account, amount, scenario
| union | on view ledger.budget | select month, account, amount, scenario | end

-- Several sites, one model
on data_site_a | derive site = "A" | union | on data_site_b | derive site = "B" | end

13.5 Time series

-- Complete the months, then compare with last year
on view sales.monthly
| fill month from $from to $to step "month" by product with amount = 0
| window | partition by product | order by month | lag amount 12 as last_year | cumsum amount as ytd | end
| derive growth = div(amount - coalesce(last_year, 0), last_year, null)

13.6 Settings read by a view

define view ledger.balances | exec
  | let $s = (on ledger_settings | limit 1)
  | let $opening ?= get(coalesce(at($s, 0), {}), "opening", 1)
  | on ledger | where fiscal_month(date, $opening) <= $month
| end
| end

A let query reads the Place’s own settings at each read; ?= lets a caller override them.

13.7 Report views with parameters

define view sales.report | exec
  | let $gran ?= "month"
  | on view sales.by_month                          -- materialized, no parameter
  | where month >= $from and month <= $to
  | derive key = if($gran == "year", substr(month, 0, 4), month)
  | group | by key, region | sum total | end
| end
| end

Constant conditions fold away once parameters are bound (5.5), so a switch like $gran costs nothing.

13.8 Checklist

• Every field read by a filter exists on every row, or is read with ?.
• Costly shapes read by several queries are materialized; parameters live in the thin views above them.
• Joins expected to match once are checked (len(p) == 1) and fall back explicitly.
• Dates are one shape per field (ISO text of one length, or epoch milliseconds).
• Views are named by layer and subject (sales.rows, sales.by_week).
• explain on view … shows filters reaching the source.

14. Execution model

14.1 From text to rows

Planning errors are raised before any data is read. Plans are cached: a repeated query skips parsing and planning.

14.2 Filters move toward the source

The planner splits where a and b into where a | where b (same result, same errors, same order) and moves each filter before the stages that leave its fields and the set of rows alone:

It stops at any other stage (limit, skip, first, distinct, fill…) and after another filter, whose order it keeps. A guard then goes on alone: for each comparison of a field with a constant, type(f?) != "string" or f? == "x", which cannot fail and drops only rows the filter would drop. The filter itself stays where it stopped, so no row reaches it that did not before, and no error is lost. explain shows the result.

A filter written after a view therefore reaches the view’s source:

let $end = "2026-03" | on view ledger.statement | where entity == "E1"

reads the ledger with the guard on entity right after the view’s own date filter, instead of classifying every entity first (10.2 s → 0.25 s on a ledger of 1.8 M rows).

14.3 Execution shapes

ogd never falls back to loading everything in memory. A read query fits one of these shapes, or is refused before it runs.

1. Pure streaming. Any mix of where, select, derive, rename, drop, root, near, skip, limit, first, single, unwind, optionally ending with count. Rows flow one by one; a row bound (limit, first) stops the scan early.

2. A streaming prefix, then blocking stages. The blocking stages are sort, distinct, group, bucket, pivot, window, fill, sample, lookup and union. Any number may follow one another, each followed by row-local stages, and the query may end with count.

• The first blocking stage reads the streaming prefix; group | sort and sort | distinct run as one stage.
• Each later blocking stage takes the rows of the stages before it once they are all produced.
• A leading skip k / limit n / first after a blocking stage bounds it, even across select, derive, rename, drop, root; a stage that can drop rows (where, near) stops that: sort k | skip 10 | limit 5 is a bounded Top-15.
• sort, group, pivot, window and fill spill sorted runs to disk when their state outgrows the memory they can get.

Anything else is refused:

query plan contains a blocking stage sequence without a bounded executor;
materialized fallback is forbidden by the memory contract

3. Transactional. Writes (set, delete, load, insert, into) and system collections (_…) take the transactional path, which these shapes do not restrict.

14.4 Memory

Every query is admitted with a small reservation and reserves memory as its blocking stages grow; all queries share the query budget of the memory profile. OGD_MEMORY_LIMIT sets the profile; on _memory and on _query_memory show it live.

A request waits for a query slot when all are busy (query wait, 60 s by default).

14.5 Joins at scale

• An equality or prefix lookup indexes the joined source once per query.
• A deterministic sub-pipeline reading only known outer fields is run once per distinct value of those fields.
• The index and kept results live within the query working set; past it, rows are joined by scan.

14.6 Grouping at scale

• group reads the storage’s per-field kinds to decide the compact form’s roles before the scan.
• A one-key group whose only stage before is a derive of + - * / over number fields is computed on each storage block’s typed columns (37 → 22 ms on 2 M rows).
• group | limit N keeps only the N smallest live keys.

14.7 Tips

• Filter early and on fields that exist everywhere.
• select what you need before a blocking stage: smaller rows, smaller spills.
• Prefer sort … | limit N to sort alone.
• Materialize costly shapes read by many queries (chapter 12).
• Read the plan: explain ….

15. System collections

Collections whose name starts with _ are reserved. Some are virtual (computed on read from the engine’s live state), some are stored and written only by ogd. All are read with the full language; writes to them are refused or limited to their own operations.

15.1 Engine state (virtual)

on _memory | where scope == "global" | select profile, current_bytes, peak_bytes, pressure_state
on _query_memory | where scope != "global"
on _index_observations | where collection == "orders" | sort executions desc | limit 10

15.2 Modeling

15.3 Platform

These are managed by ogd operations and protected by authorization; a Place reads only what concerns it.

15.4 Scope fields

A request in a Place reads only rows carrying its _place (and _app_instance when it names one). Do not write these fields yourself: the Core stamps them.

16. Results, statistics and errors

16.1 The response

A query returns its rows, then execution statistics:

{"documents":[…],
 "statistics":{"committed":false,"compact":false,"deleted":0,"filtered":1,"inserted":0,
               "replaced":0,"returned":3,"scanned":4,
               "strategies":["collection_scan"],"streamed":true,
               "timings_us":{"parse":29,"plan":116,"execute":436,"materialize_response":55}}}

Numbers on the wire. Integers above 2³² are sent as floats so that JavaScript clients never lose precision silently; integers beyond 2⁵³ are refused. A client sending such an integer may also send it as a float: og-core takes a whole float where it expects an integer count (pool and policy limits, for example).

16.2 Error layers

Every message carries a byte span or a stage location.

Request-level codes returned by ogd: query.invalid (preparation or planning), query.execution_failed, query.read_only_violation, view.invalid, view.forbidden.

16.3 Reading an execution error

query execution failed: storage execution error: storage backend error: expression evaluation failed: field path "date" is missing

Read it from the right: the evaluator could not read date on a row. Find the stage reading date, then either filter the rows that lack it first (where date? != null) or read it optionally (date?, coalesce(date?, …)).

Write errors roll back the whole transaction.

17. The query request

17.1 query.execute

Every query reaches the engine as a query.execute request:

{"operation": "query.execute",
 "payload": {"query": "on view sales.paid | where region == $r", "params": {"r": "EU"}, "readOnly": true}}

The query service prepares the text before planning: it binds parameters, evaluates let, resolves views and their caches, and writes values sources. The bare pipeline layer of the engine library does none of this.

17.2 Introspection operations

17.3 From a terminal

ogcli sends the same request: ogcli 'on users | limit 3', with --param NAME=VALUE for parameters, and a line starting with . (.query.stages) for an operation.

A. Cookbook

Recipes by task. Each is a complete query.

Reading

-- One document by id (primary-key lookup)
on users | where _id == "01900000-0000-7000-8000-000000000003"

-- Distinct values of a field
on orders | select region | distinct

-- Rows lacking a field
on data | where not exists(date)
on data | where date? == null

-- Exactly one row, or fail
on users | where name == "Alice" | single

-- A random sample
on orders | where status == "paid" | sample 100

-- Literal rows as a source
on values [{code: "FR", rate: 0.2}, {code: "DE", rate: 0.19}]

Computing

-- Safe ratio and a label
on orders | derive ratio = div(price, qty, 0), band = if(ratio > 50, "high", "low")

-- First non-empty label
on items | derive label = coalesce(nullif(trim(label?), ""), code)

-- Normalized text
on users | select name, lower(trim(city)) as city

-- Tags
on users | where "admin" in tags or len(tags) == 0

Aggregating

-- Top 3 customers by paid revenue
on orders | where status == "paid" | derive revenue = price * qty
          | group | by user | sum revenue | end | sort revenue desc | limit 3

-- Count per value, most frequent first
on users | where age >= 18 | group city | sort count desc

-- Several measures
on orders | group | by region | sum qty as units | avg price as basket | max qty | count as orders | end

-- Conditional measures
on ledger | group | by account | sum amount where amount > 0 as debit | sum amount where amount < 0 as credit | end

-- Last value by date
on balances | group | by account | last balance by date as closing | end

-- HAVING
on orders | group | by user | sum qty as units | end | where units > 3

-- Grand total
on orders | derive all = "total" | group | by all | sum qty as units | end

-- Distribution
on orders | group | by region | median price | percentile price 0.9 as p90 | stddev price | end

-- Age bands
on users | bucket age by [0, 18, 30, 50, 120] as band

-- A cross table
on orders | pivot | rows region | columns month | values qty | aggregate sum | end

Time

-- Orders per month from an ISO timestamp
on orders | derive month = date_format(created_at, "%Y-%m") | group month

-- Complete missing months with zeros
on monthly | fill month from "2026-01" to "2026-12" step "month" by product with amount = 0

-- Month over month and year to date
on monthly
| window | partition by product | order by month | lag amount as previous | cumsum amount as ytd | end
| derive change = amount - coalesce(previous, 0)

-- Rank within a partition
on sales | window | partition by region | order by amount desc | row_number as rank | end | where rank <= 3

-- Fiscal year closing in June
on ledger | derive fy = fiscal_year(date, 6) | group | by fy | sum amount | end

Joining

-- Attach one related row
on orders | lookup products on code == product into product

-- Attach a filtered set
on users as u | lookup orders as o | where o.user == u.name and o.status == "paid" | select order_id | into paid | end

-- Keep the joined field as a value
on orders as o
| lookup products as p on p.code == o.product into p
| derive label = get(coalesce(at(p, 0), {}), "label", "?")
| drop p

-- Longest-prefix classification
on ledger as a | lookup rules as r | where starts_with(a.account, r.prefix)
  | derive n = len(prefix) | sort n desc | first | into rule | end

-- Anti-join: orders without a known product
on orders | lookup products on code == product into p | where len(p) == 0

-- Stack two sources
on actual | select month, amount | union | on budget | select month, amount | end

Writing

-- Insert one, insert many
on users | insert {name: "Eve", age: 22}
on users | insert [{name: "Gus"}, {name: "Hal"}]

-- Update, increment, flag the first
on users | where name == "Bob" | set age = age + 1
on counters | where name == "visits" | set value = value + 1
on orders | where status == "pending" | sort order_id | limit 1 | set priority = true

-- Delete, purge
on orders | where status == "cancelled" | delete
on staging | delete

-- Idempotent upsert, update-only
on customers | load | with merge | chunk [{"_id":"01900000-0000-7000-8000-00000000c001","tier":"gold"}] | end
on customers | load | with update | chunk [{"_id":"01900000-0000-7000-8000-00000000c001","tier":"platinum"}] | end

-- Save a result
on orders | group | by region | sum qty as units | end | into region_totals

-- Restore yesterday's state
on orders as of "2026-10-20" | into orders_restored

Views

-- A view, then a report over it
define view sales.paid | exec | on orders | where status == "paid" | derive total = price * qty | end | end
on view sales.paid | group | by region | sum total | end

-- A parameter with a default
define view orders.big | exec | let $min ?= 2 | on orders | where qty >= $min | end | end
let $min = 4 | on view orders.big

-- A materialized base and a thin view over it
define view sales.by_month | materialized | exec | on view sales.paid | group | by month, region | sum total | end | end | end
define view sales.window | exec | on view sales.by_month | where month >= $from and month <= $to | end | end

-- Inspect
on _views | where state == "active" | select name, version, reads
on _cache | select view, version, place, computedAt
explain let $from = "2026-01", $to = "2026-06" | on view sales.window

Engine

-- Health
on _memory | where scope == "global" | select pressure_state, current_bytes, peak_bytes, spill_runs_active

-- Slowest shapes
on _index_observations | sort average_elapsed_us desc | limit 10

-- How a query runs
explain on orders | sort price * qty desc | limit 10

B. Grammar

The complete grammar, in EBNF. The pipeline layer (lexer, parser) splits a request into stages and sub-pipelines; each stage then reads its own argument text with the stage grammar below.

In this grammar "|" stands for a stage separator: |, or, in a query that holds no |, ; or a line break (B.6, 3.2). Both styles give the same stages.

B.1 Requests

request        = view-statement
               | [ "explain" ] , { let-header } , query ;
               (* `$name` parameters are bound as literals before parsing *)

let-header     = "let" , binding , { "," , binding } , "|" ;
binding        = variable , ( "=" | "?=" ) , ( expression | "(" , query , ")" ) ;
variable       = "$" , identifier ;

query          = source , { "|" , stage } ;
source         = ( "on" | "from" ) , source-target , [ "as" , identifier ] ;
source-target  = collection , [ "as" , "of" , ( string | integer ) | "history" ]
               | "values" , object-array
               | "view" , view-name , [ "version" , integer ] ;
collection     = identifier , { "." , identifier } ;
view-name      = identifier , { "." , identifier } ;
object-array   = "[" , object-literal , { "," , object-literal } , "]" ;

B.2 View statements

view-statement = [ "explain" ] , ( define-view | undefine-view ) ;
define-view    = "define" , "view" , view-name ,
                 { "|" , view-clause } ,
                 [ "|" , "exec" , "|" , query , "|" , "end" ] ,
                 "|" , "end" ;
view-clause    = "description" , string
               | "version" , positive-integer      (* restore: no exec *)
               | "materialized" ;
undefine-view  = "undefine" , "view" , view-name ;

B.3 Stages

stage          = simple-stage | compound-stage ;
simple-stage   = stage-name , arguments ;
arguments      = { token } ;            (* up to the next top-level "|", brackets balanced *)
sub-pipeline   = { "|" , stage-or-directive } , "|" , "end" ;

compound-stage = lookup | union | group-compound | pivot | window | load ;

Filtering and shaping

where          = "where" , expression ;
select         = ( "select" | "project" ) , select-item , { "," , select-item } ;
select-item    = field-path , [ "as" , field-path ] | expression , "as" , field-path ;
derive         = "derive" , assignment , { "," , assignment } ;
assignment     = field-path , "=" , expression ;
rename         = "rename" , field-path , "as" , field-path ;
drop           = "drop" , field-path , { "," , field-path } ;
root           = "root" , field-path ;
near           = "near" , field-path , "," , ( number-array | variable ) ;

Ordering and paging

sort           = "sort" , sort-key , { "," , sort-key } ;
sort-key       = expression , [ "asc" | "desc" ] ;
limit          = "limit" , count-value ;
skip           = ( "skip" | "offset" ) , count-value ;
first          = "first" , [ field-path ] ;
single         = "single" , [ field-path ] ;
sample         = "sample" , count-value ;
count-value    = non-negative-integer | variable ;

Rows

unwind         = "unwind" , field-path , [ "with" , "index" , field-path ] , [ "keep" , "empty" ] ;
distinct       = "distinct" , [ field-path , { "," , field-path } ] ;
count          = "count" , [ "as" , field-path ] ;

Aggregation and analytics

group-compact  = "group" , group-item , { "," , group-item } ;
group-item     = field-path , [ "as" , field-path ] | expression , "as" , field-path ;

group-compound = "group" , "|" , "by" , group-item , { "," , group-item } ,
                 { "|" , measure } , "|" , "end" ;
measure        = ( "sum" | "avg" | "min" | "max" | "list" | "median" | "stddev" ) , expression , [ measure-filter ] , [ "as" , field-path ]
               | "percentile" , expression , fraction , [ measure-filter ] , [ "as" , field-path ]
               | ( "first" | "last" ) , expression , [ "by" , expression ] , [ measure-filter ] , [ "as" , field-path ]
               | "count" , [ expression ] , [ measure-filter ] , [ "as" , field-path ] ;
measure-filter = "where" , expression ;

bucket         = "bucket" , expression , "by" , array , [ "default" , expression ] , [ "as" , field-path ] ;

pivot          = "pivot" ,
                 "|" , "rows" , group-item , { "," , group-item } ,
                 "|" , "columns" , group-item , { "," , group-item } ,
                 "|" , "values" , group-item , { "," , group-item } ,
                 "|" , "aggregate" , aggregate ,
                 "|" , "end" ;      (* the four directives in any order, each once *)
aggregate      = "sum" | "count" | "avg" | "average" | "min" | "minimum" | "max" | "maximum" | "first" | "last" ;

window         = "window" ,
                 [ "|" , "partition" , "by" , field-path , { "," , field-path } ] ,
                 "|" , "order" , "by" , field-path , [ "asc" | "desc" ] , { "," , field-path , [ "asc" | "desc" ] } ,
                 window-fn , { window-fn } ,
                 "|" , "end" ;
window-fn      = "|" , ( ( "lag" | "lead" ) , field-path , [ integer ]
                       | ( "cumsum" | "first" | "last" ) , field-path
                       | "row_number" ) , [ "as" , field-path ] ;

fill           = "fill" , field-path , "from" , expression , "to" , expression , "step" , expression ,
                 [ "by" , field-path , { "," , field-path } ] ,
                 [ "with" , assignment , { "," , assignment } ] ;

Combining

lookup         = ( "lookup" | "join" ) , lookup-source , [ "as" , identifier ] ,
                 ( "on" , expression , "==" , expression , "into" , field-path       (* one-line *)
                 | { "|" , stage } , "|" , "into" , field-path , { "|" , stage } , "|" , "end" ) ;
lookup-source  = collection | "view" , view-name , [ "version" , integer ] | "values" , object-array ;

union          = "union" , "|" , ( "on" ) , source-target , [ "as" , identifier ] , { "|" , stage } , "|" , "end" ;

Writes

insert         = "insert" , ( object-literal | object-array ) ;
set            = "set" , assignment , { "," , assignment } ;
delete         = "delete" ;
into           = ( "into" | "out" ) , collection ;
load           = "load" , "|" , "with" , ( "replace" | "update" | "merge" ) ,
                 { "|" , "chunk" , json-array } , "|" , "end"
               | "load" , collection ;          (* runtime hook *)

B.4 Expressions

expression   = or-expr ;
or-expr      = and-expr , { ( "or" | "||" ) , and-expr } ;
and-expr     = not-expr , { ( "and" | "&&" ) , not-expr } ;
not-expr     = ( "not" | "!" ) , not-expr | eq-expr ;
eq-expr      = rel-expr , { ( "==" | "!=" ) , rel-expr } ;
rel-expr     = add-expr , { ( "<" | "<=" | ">" | ">=" | "in" | "not" , "in" ) , add-expr
                          | [ "not" ] , "between" , add-expr , ( "and" | "&&" ) , add-expr } ;
add-expr     = mul-expr , { ( "+" | "-" ) , mul-expr } ;
mul-expr     = unary , { ( "*" | "/" | "%" ) , unary } ;
unary        = ( "-" | "+" ) , number            (* folded into a signed literal *)
             | ( "-" | "+" ) , unary
             | primary ;
primary      = literal | variable | call | field-path | array | object-literal | "(" , expression , ")" ;
call         = identifier , "(" , [ expression , { "," , expression } ] , ")" ;
array        = "[" , [ expression , { "," , expression } ] , "]" ;
object-literal = "{" , [ member , { "," , member } ] , "}" ;
member       = ( string | identifier ) , ":" , expression ;   (* bare keys in insert, values and load *)
field-path   = identifier , [ "?" ] , { "." , identifier , [ "?" ] } ;   (* any "?" makes the path optional *)
literal      = "null" | "true" | "false" | number | string ;

B.5 Tokens

identifier   = ( letter | "_" ) , { letter | digit | "_" } ;     (* Unicode letters *)
number       = [ "+" | "-" ] , digit , { digit } , [ "." , digit , { digit } ] , [ exponent ]
             | "." , digit , { digit } , [ exponent ] ;
exponent     = ( "e" | "E" ) , [ "+" | "-" ] , digit , { digit } ;
string       = '"' , { char | escape } , '"' ;
escape       = "\" , ( '"' | "\" | "/" | "b" | "f" | "n" | "r" | "t" | "u" , hex , hex , hex , hex ) ;

Keywords reserved by the pipeline lexer: on from as where set lookup join union load pivot into with chunk replace update merge end and or not true false null; in inside expressions.

B.6 Stage separators

separator    = "|"                                  (* a query holding a top-level "|" *)
             | ";" | line-break ;                   (* any other query *)
line-break   = ? a newline ending a stage: brackets balanced, the line not ending on a continuation,
                 the next line starting with a stage word or a directive of its block ? ;
continuation = "," | "=" | "+" | "-" | "*" | "/" | "%" | "<" | ">" | "!" | "&" | "." | ":" | "$"
             | "and" | "or" | "not" | "in" | "as" | "between" | "by" | "on" | "into" | "with" | "from"
             | "to" | "step" | "default" | "version" | "of" | "keep" | "index" | "view" ;

• A top-level | is one outside strings and brackets, || aside. A query holding one uses | only: there a ; is refused and a line break is a space.
• In the other style a final ; is a space; an empty stage between two ; is refused.
• The words that start a line are the stage names of B.3 and let, end; in a bare group, pivot, window or load, and in define view, the directives of that block (3.2). on and from start a line only after union, exec or a let header.
• Text in parentheses is read on its own, in its own style (a let query).

C. Quick reference

Query

[explain] [let $a = …, $b ?= … |] on <collection | values […] | view <name>> [as of … | history] [as <alias>] | <stage> | …

Separators

on orders | where qty > 1 | sort qty desc        -- `|`
on orders; where qty > 1; sort qty desc          -- `;`, in a query without `|`
on orders                                        -- line breaks, in a query without `|`
where qty > 1
sort qty desc

One style per query. A line starting with a stage word (or a directive of its block) starts a stage, unless the line before ends with ,, an operator or a word such as and, as, on. end closes a block. Text in parentheses keeps its own style. Details: 3.2. Below, | stands for any separator.

Stages

Views

define view <name> [| description "…"] [| materialized] | exec | <query> | end | end
define view <name> | version <n> | end
undefine view <name>
on view <name> [version <n>]

Operators (lowest to highest)

or || · and && · not ! · == != · < <= > >= in not-in between · + - · * / % · unary - +

Functions

Missing fields

f? · exists(f) · coalesce(f, d) · optional(f) · if(exists(f), f, d)

Date units and parts

Units: year quarter month week day hour minute second millisecond (plural accepted). Parts: year quarter month week day day_of_week day_of_year hour minute second millisecond. Format: %Y %m %d %H %M %S %L %j %u %V %F %T %%.

D. Limits

Index

Stages, functions, keywords, directives and topics, in alphabetical order.

# · A · B · C · D · E · F · G · H · I · J · K · L · M · N · O · P · Q · R · S · T · U · V · W

Symbols

• ; (semicolon) — stage separator: Lexical structure
• ; and line breaks — section: Lexical structure
• ?= (default) — let: Parameters, variables and explain
• | (pipe) — stage separator: Lexical structure

A

• A guided tour — chapter: A guided tour
• About this book — chapter: The OpenGlacier Modeling Language
• abs — function: Numeric functions
• aggregate (pivot) — directive: Aggregation and analytics
• Aggregation and analytics — chapter: Aggregation and analytics
• alias (as): Anatomy of a query
• Aliases — section: Anatomy of a query
• Anatomy of a query — chapter: Anatomy of a query
• Array and object functions — chapter: Array and object functions
• Arrays, deduplication and counting — chapter: Arrays, deduplication and counting
• as of — section: Past states and history
• at — function: Array and object functions
• avg — function: Numeric functions

B

• between — function: Logic, missing values and conversions
• between (operator) — operator: Expressions
• blocking stage: Execution model
• bucket — function: Logic, missing values and conversions
• bucket — stage: Aggregation and analytics
• by (group) — directive: Aggregation and analytics

C

• _cache — system collection: Materialized views and caches
• case — function: Logic, missing values and conversions
• ceil — function: Numeric functions
• Checklist — section: Modeling patterns
• Choosing a stage — section: Stages at a glance
• Choosing what to cache — section: Materialized views and caches
• chunk (load) — directive: Writes
• Clean the raw data once — section: Modeling patterns
• coalesce — function: Logic, missing values and conversions
• Collections — section: Anatomy of a query
• columns (pivot) — directive: Aggregation and analytics
• Combining collections — chapter: Combining collections
• Common rules — section: Functions at a glance
• Compact form — section: Aggregation and analytics
• Comparison rules — section: Values and types
• Compound form — section: Aggregation and analytics
• Compound stages and end — section: Anatomy of a query
• concat — function: Text functions
• Constant folding — section: Expressions
• constant folding: Expressions
• contains — function: Text functions
• Cookbook — chapter: Cookbook
• count — stage: Arrays, deduplication and counting
• cumsum (window) — directive: Aggregation and analytics

D

• Date and fiscal functions — chapter: Date and fiscal functions
• Date recipes — section: Date and fiscal functions
• date_add — function: Date and fiscal functions
• date_diff — function: Date and fiscal functions
• date_format — function: Date and fiscal functions
• date_part — function: Date and fiscal functions
• date_trunc — function: Date and fiscal functions
• Dates — section: Values and types
• days_in — function: Date and fiscal functions
• define view — section: Views
• delete — stage: Writes
• derive — stage: Filtering and shaping
• Design principles — section: Introduction
• _distance — field: Vector search
• distinct — stage: Arrays, deduplication and counting
• div — function: Numeric functions
• drop — stage: Filtering and shaping

E

• end — keyword: Anatomy of a query
• ends_with — function: Text functions
• Engine state (virtual) — section: System collections
• Entry layout — section: Stages at a glance
• Error layers — section: Results, statistics and errors
• exec — view clause: Views
• Execution model — chapter: Execution model
• Execution shapes — section: Execution model
• exists — function: Logic, missing values and conversions
• exp — function: Numeric functions
• explain — section: Parameters, variables and explain
• Expressions — chapter: Expressions
• Expressions per stage — section: Expressions

F

• fill — stage: Aggregation and analytics
• filter pushdown: Execution model
• Filtering and shaping — chapter: Filtering and shaping
• Filters move toward the source — section: Execution model
• first — stage: Ordering, paging and sampling
• fiscal year: Date and fiscal functions
• Fiscal years — section: Date and fiscal functions
• fiscal_end — function: Date and fiscal functions
• fiscal_month — function: Date and fiscal functions
• fiscal_start — function: Date and fiscal functions
• fiscal_year — function: Date and fiscal functions
• flatten — function: Array and object functions
• floor — function: Numeric functions
• Forms — section: Expressions
• From a terminal — section: The query request
• From text to rows — section: Execution model
• Function calls — section: Expressions
• Functions at a glance — chapter: Functions at a glance

G

• get — function: Array and object functions
• Grammar — chapter: Grammar
• group — stage: Aggregation and analytics
• Grouping at scale — section: Execution model

H

• has — function: Array and object functions
• history — section: Past states and history
• How a query reaches the engine — section: Introduction

I

• _id — field: Values and types
• Identifiers — section: Lexical structure
• if — function: Logic, missing values and conversions
• in, not in — operator: Expressions
• index_of — function: Text functions
• insert — stage: Writes
• into — stage: Writes
• into (lookup) — directive: Combining collections
• Introduction — chapter: Introduction
• Introspection operations — section: The query request

J

• Joins at scale — section: Execution model

K

• keys — function: Array and object functions
• Keywords — section: Lexical structure

L

• lag, lead (window) — directive: Aggregation and analytics
• Layers — section: Modeling patterns
• Layout — section: Lexical structure
• Lazy evaluation — section: Expressions
• len — function: Text functions
• let — statement: Parameters, variables and explain
• Lexical structure — chapter: Lexical structure
• limit — stage: Ordering, paging and sampling
• Limits — chapter: Limits
• line breaks — stage separator: Lexical structure
• list (group) — measure: Aggregation and analytics
• Listing caches — section: Materialized views and caches
• Listing views — section: Views
• Literal rows: values — section: Anatomy of a query
• Literals — section: Lexical structure
• ln — function: Numeric functions
• load — stage: Writes
• log — function: Numeric functions
• log10 — function: Numeric functions
• Logic, missing values and conversions — chapter: Logic, missing values and conversions
• lookup — stage: Combining collections
• lookup view: Views
• lower — function: Text functions
• ltrim — function: Text functions

M

• materialized — view clause: Materialized views and caches
• Materialized views — section: Materialized views and caches
• Materialized views and caches — chapter: Materialized views and caches
• max — function: Numeric functions
• median — function: Numeric functions
• Memory — section: Execution model
• memory classes: Execution model
• merge — function: Array and object functions
• min — function: Numeric functions
• missing field: Values and types
• Missing fields — section: Values and types
• Modeling — section: System collections
• Modeling patterns — chapter: Modeling patterns
• month_end — function: Date and fiscal functions

N

• near — stage: Vector search
• now — function: Date and fiscal functions
• nullif — function: Logic, missing values and conversions
• Numbers — section: Values and types
• Numeric functions — chapter: Numeric functions

O

• on view — source: Views
• One style per query — section: Lexical structure
• Operators and punctuation — section: Lexical structure
• optional — function: Logic, missing values and conversions
• ? (optional path) — operator: Values and types
• order by, partition by (window) — directive: Aggregation and analytics
• Ordering, paging and sampling — chapter: Ordering, paging and sampling

P

• pad_left — function: Text functions
• pad_right — function: Text functions
• Parameters — section: Views
• parameters ($name): Parameters, variables and explain
• Parameters, variables and explain — chapter: Parameters, variables and explain
• Parameters: $name — section: Parameters, variables and explain
• parse_date — function: Date and fiscal functions
• Past states and history — chapter: Past states and history
• percentile — function: Numeric functions
• pivot — stage: Aggregation and analytics
• Place — scope: Anatomy of a query
• _place, _app_instance — fields: System collections
• Placement rules — section: Anatomy of a query
• Platform — section: System collections
• pow — function: Numeric functions
• Precedence — section: Expressions
• precedence: Expressions
• primary-key lookup: Values and types

Q

• Query variables — section: Parameters, variables and explain
• query variables: Parameters, variables and explain
• query.execute — operation: The query request
• Quick reference — chapter: Quick reference

R

• range — function: Array and object functions
• Read and write queries — section: Anatomy of a query
• Reading a view — section: Views
• Reading an execution error — section: Results, statistics and errors
• Reference data and classification — section: Modeling patterns
• refresh of materialized views: Materialized views and caches
• rename — stage: Filtering and shaping
• replace — function: Text functions
• Report views with parameters — section: Modeling patterns
• result cache: Materialized views and caches
• Results, statistics and errors — chapter: Results, statistics and errors
• reverse — function: Array and object functions
• reverse — function: Text functions
• root — stage: Filtering and shaping
• round — function: Numeric functions
• row_number (window) — directive: Aggregation and analytics
• rows (pivot) — directive: Aggregation and analytics
• rtrim — function: Text functions
• Rules for both — section: Past states and history

S

• sample — stage: Ordering, paging and sampling
• scope: Anatomy of a query
• Scope fields — section: System collections
• Scope: Places and App instances — section: Anatomy of a query
• select — stage: Filtering and shaping
• Semantics — section: Expressions
• set — stage: Writes
• Settings read by a view — section: Modeling patterns
• sign — function: Numeric functions
• Signs — section: Lexical structure
• Simple stages — section: Anatomy of a query
• single — stage: Ordering, paging and sampling
• skip — stage: Ordering, paging and sampling
• slice — function: Array and object functions
• sort — stage: Ordering, paging and sampling
• sort_array — function: Array and object functions
• Sources — section: Anatomy of a query
• spill to disk: Execution model
• split — function: Text functions
• sqrt — function: Numeric functions
• Stacking sources — section: Modeling patterns
• Stage separators — section: Lexical structure
• Stages — section: Anatomy of a query
• Stages at a glance — chapter: Stages at a glance
• standard view: Views
• Standard views — section: Views
• starts_with — function: Text functions
• stddev — function: Numeric functions
• streaming: Execution model
• substr — function: Text functions
• sum — function: Numeric functions
• System collections — chapter: System collections

T

• Text — section: Values and types
• Text functions — chapter: Text functions
• The mental model — section: Introduction
• The pipe — section: Lexical structure
• The query request — chapter: The query request
• The response — section: Results, statistics and errors
• The result cache — section: Materialized views and caches
• The value model — section: Values and types
• Time series — section: Modeling patterns
• Tips — section: Execution model
• to_bool — function: Logic, missing values and conversions
• to_float — function: Logic, missing values and conversions
• to_int — function: Logic, missing values and conversions
• to_string — function: Logic, missing values and conversions
• Top-N: Ordering, paging and sampling
• transaction: Writes
• trim — function: Text functions
• trunc — function: Numeric functions
• type — function: Logic, missing values and conversions

U

• undefine view — section: Views
• union — stage: Combining collections
• unique — function: Array and object functions
• unwind — stage: Arrays, deduplication and counting
• upper — function: Text functions

V

• _values — constant collections: Anatomy of a query
• values — function: Array and object functions
• values (pivot) — directive: Aggregation and analytics
• values (source) — source: Anatomy of a query
• Values and types — chapter: Values and types
• Variables: let — section: Parameters, variables and explain
• Vector search — chapter: Vector search
• version (view) — view clause: Views
• Views — chapter: Views
• _views — system collection: Views
• Views and Places — section: Views

W

• What OGML is — section: Introduction
• What the language is not — section: Introduction
• When to materialize — section: Materialized views and caches
• where — stage: Filtering and shaping
• window — stage: Aggregation and analytics
• wire numbers (2^32, 2^53): Values and types
• with (load) — directive: Writes
• with index, keep empty (unwind) — option: Arrays, deduplication and counting
• Writes — chapter: Writes