# EventQL Reference

This guide provides a comprehensive reference for **EventQL**, the **query language** used in EventSourcingDB **to retrieve and transform event data**. It describes the **syntax**, **supported clauses**, **available functions**, and **supported types**. This page is intended as a technical reference and does not explain usage patterns or best practices.

EventQL is **case-insensitive**. Keywords, function names, types, and the names of sources can be written in any casing. **Aliases and field names are case-sensitive**, though: `E.id` does not refer to the alias `e`, and `e.ID` is `null`, since the field is called `id`.

## Syntax Overview

An EventQL query consists of one or more **`FROM` clauses** followed by optional **`WHERE`**, **`ORDER BY`**, **`GROUP BY`**, **`HAVING`**, **`SKIP`**, and **`TOP` clauses**, and **must end with a `PROJECT INTO` clause**:

*(Changed in 1.3)*

```eventql
FROM <alias> IN <source>
[FROM <alias> IN <source>] ...
[WHERE <condition>]
[ORDER BY <field> [ASC | DESC]]
[GROUP BY <field> [HAVING <condition>]]
[SKIP <int-expression>]
[TOP <int-expression>]
PROJECT INTO [DISTINCT] <projection>
```

**Comments** can be added using `//` for **single-line** or `/* ... */` for **block comments**.

## Sources and Fields

You can **query from the following sources**:

- **`events`**: The event stream.
- **`subjects`**: All known subjects.
- **`eventtypes`**: All known event types.
- **Subqueries**: Enclosed in parentheses and returning a projected structure.

Each `FROM` clause defines an **alias (`FROM e IN events`)** that is used to refer to elements in expressions. **Multiple `FROM` clauses create a cartesian product** of their respective sources.

*(Removed in 1.3; Broken in 1.2, fixed in 1.3)* A **subquery** that names two of its sources alike, mixes aggregate functions with fields of its sources in its `PROJECT INTO`, reads a source that does not exist, such as `FROM e IN nosuch`, or has a `SKIP` or a `TOP` that cannot be evaluated, such as one that divides by zero, fails the query **only once a row reaches it**. So a query that holds one **returns no results** where no row does, as on an empty store.

*(Added in 1.1; Changed in 1.3; Broken in 1.2, fixed in 1.3)* For the same reason, an **[`isEventQlQueryTrue` precondition](/docs/eventsourcingdb/v1.2/preconditions#iseventqlquerytrue)** with such a query, such as one that ends in `PROJECT INTO COUNT() == 0`, **holds** where no row reaches the subquery, and lets its write through.

Fields available for events:

- **`specversion`**: Version of the specification.
- **`id`**: Unique identifier of the event.
- **`time`**: Timestamp (date and time) the event was written.
- **`source`**: Source URI of the event.
- **`subject`**: Event subject.
- **`type`**: Event type.
- **`datacontenttype`**: Content type of the `data` field (always `application/json`).
- **`data`**: JSON payload.
- **`predecessorhash`**: Hash of the predecessor event.
- **`hash`**: Hash of the event.
- **`traceparent`**: Distributed tracing parent context (missing from the event if not set, so that `e.traceparent` is `null`).
- **`tracestate`**: Distributed tracing state (missing from the event if not set, so that `e.tracestate` is `null`).
- *(Added in 1.2)* **`signature`**: Ed25519 signature of the event (or `null` if signatures are disabled).

Access to **nested fields** in `data` is supported **using dot notation (e.g. `e.data.count`)**. If the field does not exist, the **value resolves to `null`**. Reading a field of that `null`, or of any other value that is not an object, **fails the query**, such as `e.data.address.city` for an event without `address`, with `index operator not supported: NULL`. Guard such an access with a condition that leaves those events out first, as in `e.data.address != NULL AND e.data.address.city == "Berlin"`.

**Square brackets** read an element of an array by its index, counted from `0`, as in `e.data.tags[0]`. A negative index counts from the end, so `e.data.tags[-1]` is the last element, and an index beyond either end yields `null`. The index has to be an integer. With a string in the brackets, they read a field by its name instead, which also reaches a field whose name the dot notation cannot, such as one with a hyphen: `e.data["book-id"]`.

*(Changed in 1.3; Broken in 1.2, fixed in 1.3)* **A name takes in `*`, `#`, and the digits**, since they count as emoji, and can even start with a digit. So `e.data.qty*2` reads as a field named `qty*2`, which does not exist, rather than as a multiplication, and `WHERE e.data.qty*2 == 10` **silently returns no events**. Put spaces around the operator, as in `e.data.qty * 2`.

**A name also takes in every character from U+0080 to U+FFFF**, also one that looks like an operator, a space, or a quote, such as a typographic minus (U+2212), a no-break space (U+00A0), a zero-width space (U+200B), or curly quotes, as text pasted from a word processor can hold them. So `WHERE e.data.n−2 == 3`, written with a typographic minus, reads a field named `n−2`, which does not exist, and **silently returns no events**. Replace such a character with the one it looks like, such as `-` for the typographic minus, or remove it.

For the source **`subjects`**, each item is **a string representing the subject** identifier. There are no additional fields.

For the source **`eventtypes`**, each item is **a string representing the event type**. There are no additional fields.

## Filtering with `WHERE`

The **`WHERE` clause filters the input by evaluating a condition expression**. Only results for which the condition evaluates to `TRUE` are included.

*(Changed in 1.3)* A condition that is not a boolean **results in an error**, such as `not a BOOLEAN, got NULL`, and so does an operand of `AND`, `OR`, or `XOR` that is not a boolean.

Operators:

- **Comparison**: `==`, `!=`, `<`, `>`, `<=`, `>=`
- **Logical**: `AND`, `OR`, `XOR`, `NOT`
- **Containment**: `array CONTAINS value`
- **Grouping**: `(...)`

Conditions may include arithmetic, function calls, type conversion, and access to any fields in the current scope.

The operators **bind in this order**, from the most tightly to the most loosely, and operators of the same level apply from left to right, so `10 - 2 - 3` is `5`:

- `.` and `[]` for fields and elements, and function calls
- `NOT` and `-` before a value
- `*`, `/`, and `%`
- `+` and `-`
- `AS`
- `<`, `<=`, `>`, and `>=`
- `==`, `!=`, and `CONTAINS`
- `AND`
- `XOR`
- `OR`

So **`NOT` binds more tightly than `==`**: `NOT e.type == "io.eventsourcingdb.library.book-borrowed"` means `(NOT e.type) == "io.eventsourcingdb.library.book-borrowed"`, not what it seems to say. To negate a comparison, **put it in parentheses**, as in `NOT (e.type == "io.eventsourcingdb.library.book-borrowed")`. Likewise, `AS` binds more loosely than arithmetic, so `1 + 2 AS STRING` is `"3"`.

*(Changed in 1.3; Broken in 1.2, fixed in 1.3)* **`NOT` converts a value that is not a boolean** rather than failing on it: `NOT NULL` is `TRUE`, and `NOT` of any other value that is not a boolean, such as a string or a number, is `FALSE`. So `WHERE NOT e.type == "io.eventsourcingdb.library.book-borrowed"` compares `FALSE` with the event type and **silently returns no events**.

*(Changed in 1.3; Broken in 1.2, fixed in 1.3)* **`==` and `!=` on values of different types fail or not depending on which one stands on the left.** With an array, an object, a boolean, or `NULL` on the left, `==` is `false` and `!=` is `true`, but with a number, a string, a `DATE`, a `DATETIME`, or a `TIME` on the left, a value of another type on the right **ends the query with an error**, such as `type mismatch: INTEGER == ARRAY`, apart from the comparisons of numbers and strings with each other and with `NULL`, and of a `DATE` with a `DATETIME`. So `[1, 2] == 1` is `false`, while `1 == [1, 2]` fails, and `WHERE e.data.isActive == true` fails at the first event whose `isActive` holds a number or a string.

A field that does not exist is `NULL`, and **`NULL` compares by `==` and `!=` only**: `e.data.price == NULL` holds for an event without `price`, but `<`, `>`, `<=`, and `>=` with `NULL` on either side **end the query with an error**, such as `type mismatch: NULL > INTEGER`. So `WHERE e.data.price > 20` fails at the first event that lacks `price`. Guard such a comparison with a condition that leaves those events out first, since `AND` stops at its first false operand: the type of the events that carry the field, as in `e.type == "io.eventsourcingdb.library.book-acquired" AND e.data.price > 20`, or the field itself, as in `e.data.price != NULL AND e.data.price > 20`.

*(Changed in 1.3; Broken in 1.2, fixed in 1.3)* **The kind of that error depends on which side `NULL` stands on**: with a number, a string, or an array on the left, it is an unknown operator, such as `unknown operator: INTEGER > NULL` for `5 > NULL`, while `NULL < 5` fails with `type mismatch: NULL < INTEGER`.

*(Changed in 1.3; Broken in 1.2, fixed in 1.3)* **With an array on the left, that error is an unknown operator**, such as `unknown operator: ARRAY < INTEGER` for `[1] < 5`, while `5 < [1]` fails with `type mismatch: INTEGER < ARRAY`.

**Strings compare by `==` and `!=` only** as well: `<`, `>`, `<=`, and `>=` on two strings fail with `unknown operator: STRING < STRING`, in a `WHERE` as in any other expression. `ORDER BY`, by contrast, sorts strings, byte by byte.

*(Changed in 1.3)* A number that meets a string **takes part as a string**, by its digits, so `42 == "42"` holds, and `<`, `>`, `<=`, and `>=` fail as they do on two strings.

The error names two strings, such as `unknown operator: STRING > STRING` for `5 > "a"`.

*(Changed in 1.3; Broken in 1.2, fixed in 1.3)* **`==` and `!=` tell two objects apart** even where they hold the same fields with the same values, so `WHERE e.data.address == {city: "Berlin"}` matches no event, and `!=` holds for every one. Arrays compare by their content, and so do objects inside an array.

## Sorting, Grouping and Limiting

- *(Changed in 1.3)* **`ORDER BY <field>`** sorts the result ascending (default) or descending. Ordering by the `id` of an event orders the events as they are stored, so `10` follows `9`, although `id` is a string in the result. An `id` that comes out of a subquery is an ordinary string, so order it with `ORDER BY e.id AS INT` to compare it as a number.
- **`GROUP BY <field>`** groups the results by a single field. All items in a group share the same value for that field. Aggregate functions will be evaluated separately for each group.
- **`HAVING <condition>`** filters entire groups based on aggregate conditions.
- *(Changed in 1.3; Broken in 1.2, fixed in 1.3)* **`SKIP <int-expression>`** skips a number of groups with `GROUP BY`. **Without `GROUP BY`, it skips rows before they are projected**, rather than results, so that `SKIP 3 PROJECT INTO COUNT()` counts every row but three. A negative `SKIP` skips nothing.
- **`TOP <int-expression>`** limits the number of groups with `GROUP BY`. **Without `GROUP BY`, it limits the rows before they are projected**, rather than the results, so that `TOP 2 PROJECT INTO COUNT()` returns `2`, and `TOP 3 PROJECT INTO DISTINCT e.type` returns only the event types among the first three rows. **`TOP 0` sets no limit**, and a negative `TOP` returns nothing.

**`ORDER BY` fails on a value it cannot sort** – `NULL`, an array, or an object – and on values of different types in different rows, such as a string and a number: `ORDER BY e.data.price` over events of which some lack `price` fails with `type mismatch: NULL FLOAT null 30.000000`. Leave such rows out first, as in `WHERE e.data.price != NULL`.

*(Removed in 1.3; Broken in 1.2, fixed in 1.3)* **Ordering by the `id` of an event can leave the rows unsorted.** With several sources, ordering by the `id` of any but the outer one, whose name comes first in alphabetical order, is ignored, so the rows come in the order in which they are read. With a source written in another case, such as `FROM e IN EVENTS`, the ids compare as strings, so `10` comes before `9`. And an `id` cast to another type, such as `e.id AS STRING`, still compares as a number.

*(Removed in 1.3; Broken in 1.2, fixed in 1.3)* **`GROUP BY` on a decimal puts values with the same integer part into one group**, such as `1.0` and `1.5`, so the items of a group do not necessarily share the same value.

**Each group becomes one item of the result, an array.** With aggregates in the projection, the array holds **one element**, the projection of the group; without, it holds **the projection of every row of the group**. This query returns one item for each reader:

```eventql
FROM e IN events
WHERE e.type == "io.eventsourcingdb.library.book-borrowed"
GROUP BY e.data.borrowedBy
PROJECT INTO { reader: UNIQUE(e.data.borrowedBy), books: COUNT() }
```

```json
[{"books":2,"reader":"/readers/23"}]
[{"books":1,"reader":"/readers/42"}]
```

Projecting `e.subject` instead returns the subjects of the books each reader borrowed, such as `["/books/1","/books/3"]`.

The groups come **in the order of their first rows**, and the rows of a group in the order in which they arrive. **`ORDER BY` runs before `GROUP BY`**, as the syntax places it: it sorts the rows before they are grouped, and with them the groups, but it cannot sort the groups by their aggregates. To do that, group in a subquery and sort its items, as in `FROM g IN (...) ORDER BY g[0].books DESC PROJECT INTO g[0]`.

## Projection with `PROJECT INTO`

The **final result is defined using the `PROJECT INTO` clause**. It accepts any expression, such as a structure **using JSON-like syntax** (which is a valid hash map literal in EventQL):

**Examples:**

```eventql
PROJECT INTO { id: e.id, kind: e.type, details: e.data }
PROJECT INTO e.subject
```

Unlike in JSON, **the keys of a structure are names without quotes**, and keywords are names as well, as in `{ from: e.subject }`. A quoted key, as in `{ "id": e.id }`, fails with `expected next token to be IDENTIFIER, got "id" instead`.

The optional **`DISTINCT` keyword** removes duplicates. When using `GROUP BY`, **duplicates are removed within each group**.

*(Changed in 1.3; Broken in 1.2, fixed in 1.3)* **`DISTINCT` and `GROUP BY` tell two objects apart whose keys come in different orders**, such as `{ a: 1, b: 2 }` and `{ b: 2, a: 1 }`, although they hold the same keys with the same values.

## Type Conversion

EventQL supports **explicit type conversion** using the **`AS` keyword**. Conversion behaves like parsing based on the expected format.

```eventql
"2021-01-01T00:00:00Z" AS DATETIME
"13:45:39" AS TIME
```

All date and time values must follow the **RFC 3339** (with nanosecond precision, i.e. **RFC3339Nano**) format.

**Supported types:**

- `STRING`
- `INT`
- `FLOAT64`
- `BOOLEAN`
- `DATE`
- `TIME`
- `DATETIME`

**Invalid formats result in a runtime error** (e.g. `cannot cast '13:45:00' of type STRING to DATETIME: invalid format`). A cast to `INT` cuts off the fractional part, so `3.9 AS INT` is `3`.

*(Changed in 1.3; Broken in 1.2, fixed in 1.3)* A cast to `INT` of a value beyond the range of a 64-bit integer **returns a wrong number**, such as `9223372036854775807` for `'99999999999999999999' AS INT`, and one of `'NaN'` returns `0`.

## Expression Language

**Expressions in EventQL** include:

- **Literals**: Numbers, strings, booleans, and null. A string stands in double or single quotes and knows the escapes `\n`, `\r`, `\t`, `\"`, `\'`, and `\\`. A decimal needs digits on both sides of its point, such as `0.5`, there is no exponent notation such as `1e3`, and a negative number is a number negated with `-`, as in `-5`. There is no literal for dates and times: cast a string instead, such as `"2026-10-20" AS DATE`.
- **Object literals (hash maps)**: `{ key: value, ... }`
- **Array literals**: `[ value1, value2, ... ]`
- **Field references**: e.g. `e.data.price`
- **Index access**: e.g. `e.data.tags[0]` or `e.data["book-id"]`
- **Function calls**: e.g. `LEN(e.data.items)`
- **Type conversions**: e.g. `"42" AS INT`
- **Conditional expressions**: `IF(condition, then, else)`
- **Operators**: Comparison, logical, arithmetic

Numbers are either **integers** or **decimals**, which are 64-bit floating-point numbers. An operation on an integer and a decimal yields a decimal, and a comparison compares numbers by their value, whatever their type, so `1 == 1.0` is true. **Numbers in the `data` of events are decimals**, also those written without a point, such as `15`. So cast such a number with `AS INT` where an integer is required, such as for an index or for `SUBSTRING`, as in `e.data.tags[e.data.position AS INT]`.

*(Changed in 1.3; Broken in 1.2, fixed in 1.3)* **`CONTAINS`, `GROUP BY`, and `DISTINCT` tell an integer and a decimal of the same value apart**, so `[1] CONTAINS 1.0` is false, and `ORDER BY` fails on a mix of integers and decimals, with an error such as `type mismatch: FLOAT INTEGER 1.500000 1`.

### Arithmetic

The **arithmetic operators** are `+`, `-`, `*`, `/`, and `%` (modulo), where `*`, `/`, and `%` bind more tightly than `+` and `-`. **A division always yields a decimal**, also for two integers, so `7 / 2` is `3.5`. The modulo of integers has the sign of the dividend, so `-7 % 2` is `-1`. **A division or a modulo by zero fails** the query with `division by zero` or `modulo by zero`.

`+` also **joins strings**, and a number with a string by its digits, so `"Book " + 42` is `"Book 42"`.

*(Changed in 1.3; Broken in 1.2, fixed in 1.3)* **`+` joins a boolean, `NULL`, or an object with a string that follows it**, by its text, so `true + "a"` is `"truea"` and `NULL + "a"` is `"nulla"`, while with the string in front, `"a" + true` fails with `type mismatch: STRING + BOOLEAN` and `"a" + NULL` with `unknown operator: STRING + NULL`.

*(Changed in 1.3; Broken in 1.2, fixed in 1.3)* Integers are 64-bit, and a result beyond their range wraps around silently. A number literal beyond the range of its type, such as the integer `9223372036854775808`, or a decimal beyond the range of a 64-bit floating-point number, makes the server **drop the request without an answer**, and every write after it, and every read after such a write, then **waits until the server is restarted**. The modulo of decimals cuts both of them to integers first, so `5.5 % 2` is `1`, and a divisor between -1 and 1, such as in `5 % 0.5`, **terminates the server process**, and with it every connection.

*(Changed in 1.3; Broken in 1.2, fixed in 1.3)* **The kind of that error depends on which side a value stands on** for `NULL` and for an array: `5 + NULL` fails with `unknown operator: INTEGER + NULL`, while `NULL + 5` fails with `type mismatch: NULL + INTEGER`, and `[1] + 5` with `unknown operator: ARRAY + INTEGER`, while `5 + [1]` fails with `type mismatch: INTEGER + ARRAY`. The same holds for `CONTAINS` with `NULL`.

## Built-in Functions

*(Removed in 1.3; Broken in 1.2, fixed in 1.3)* A call of a function whose name a source bears **in the same case**, such as `upper(upper.subject)` in `FROM upper IN events`, **takes the source for the function** and fails with `not a function`, and next to an aggregate function, the query is refused as mixing aggregate functions with fields of its sources. Since the **[management UI](/docs/eventsourcingdb/v1.2/management-ui)** writes the names of functions in upper case when it formats a query, **formatting can change what such a query means**. Where a query leaves such a call of an aggregate function unevaluated, as in the branch of an `IF` that it does not take, the aggregate functions written after it **take in no events**, so that a `COUNT()` among them returns `0`.

### Mathematical Functions

- **`ABS(x)`**: Absolute value of a number.
- **`CEIL(x)`**: Smallest integer not less than `x`.
- **`FLOOR(x)`**: Largest integer not greater than `x`.
- **`ROUND(x)`**: Rounded value of `x`.
- **`COS(x)`**, **`SIN(x)`**, **`TAN(x)`**: Trigonometric functions.
- **`EXP(x)`**: Exponential function of `x`, e raised to the power of `x`. Without an argument, `EXP()` is e.
- **`POW(x, y)`**: `x` raised to the power of `y`.
- **`SQRT(x)`**: Square root of `x`.
- **`RAND()`**: Pseudorandom number between 0 and 1.
- **`PI()`**: Value of π.

*(Changed in 1.3; Broken in 1.2, fixed in 1.3)* A computation whose result is **not a number**, such as `SQRT(-1)`, yields a value that **sorts, compares, and aggregates by chance**, or fails the query without naming where.

### String Functions

- **`LOWER(x)`**: Lowercase string.
- **`UPPER(x)`**: Uppercase string.
- **`TRIM(x)`**: Trim leading and trailing whitespace.
- **`LTRIM(x)`**: Trim leading whitespace.
- **`RTRIM(x)`**: Trim trailing whitespace.
- **`LEN(x)`**: Length of a string in bytes, or number of elements of an array or hash map.
- **`INSTR(x, y)`**: Index of the first occurrence of `y` in `x`, counted in bytes from `0`, or `-1` if `x` does not contain `y`.
- *(Changed in 1.3; Broken in 1.2, fixed in 1.3)* **`SUBSTRING(x, start, end)`**: Substring of `x` from index `start` through index `end`, both counted from `0`, so `SUBSTRING("hello", 1, 3)` returns `ell`. Without `end`, it runs to the end of `x`. An `end` beyond the end of `x` is capped at its end, and one directly before `start` yields `""`. `start` has to lie between `0` and the length of `x`, and `end` must not lie further before `start` than that; otherwise, the call **terminates the server process**, and with it every connection.
- **`REPLACE(x, y, z)`**: Replace all occurrences of `y` in `x` with `z`.
- **`STARTSWITH(x, y)`**: Checks if `x` starts with `y`.
- **`ENDSWITH(x, y)`**: Checks if `x` ends with `y`.

`LEN`, `INSTR`, and `SUBSTRING` count **bytes of UTF-8**, not characters: `LEN("Café")` is `5`, since `é` takes two bytes, and `SUBSTRING("Café", 0, 3)` cuts the `é` in two.

### Date and Time Functions

- **`NOW()`**: Current timestamp.
- **`DATE(x)`**: Date of a `DATETIME`, as a `DATE`.
- **`TIME(x)`**: Time of day of a `DATETIME`, as a `TIME`.
- **`YEAR(x)`**: Year part of a `DATE` or `DATETIME`.
- **`MONTH(x)`**: Month part of a `DATE` or `DATETIME`.
- **`DAY(x)`**: Day part of a `DATE` or `DATETIME`.
- **`HOUR(x)`**: Hour part of a `TIME` or `DATETIME`.
- **`MINUTE(x)`**: Minute part of a `TIME` or `DATETIME`.
- **`SECOND(x)`**: Second part of a `TIME` or `DATETIME`.
- **`WEEK(x)`**: Week of the year according to ISO 8601, from 1 to 53, of a `DATE` or `DATETIME`.
- **`WEEKDAY(x)`**: Weekday (0 = Sunday) of a `DATE` or `DATETIME`.

*(Changed in 1.3)* **Dates and times keep the zone of the value they come from.** `NOW()` returns the current time in the **zone of the server**, so `DATE(e.time) == DATE(NOW())` matches no event written today, whatever that zone, UTC included. `DATE(x)`, `TIME(x)`, and the casts of a `DATETIME` to `DATE` and `TIME` take the day and the time of day **at the offset of `x`**, and so do `HOUR`, `DAY`, and the other parts of a `DATETIME`, so `DATE("2026-10-20T23:30:00-02:00" AS DATETIME)` is `2026-10-20`, and `HOUR("2026-10-20T14:00:00+02:00" AS DATETIME)` is `14`. A `DATETIME` is written at its offset as well, by its cast to `STRING` and in the results of a query.

A `DATE` or a `TIME` taken at different offsets compares **neither by what it shows nor by its instant**: `TIME("2026-10-20T14:00:00+02:00" AS DATETIME)` shows `14:00:00`, yet equals neither `"14:00:00" AS TIME` nor `"12:00:00" AS TIME`, and `GROUP BY` and `DISTINCT` take dates and times at their offsets as well.

*(Changed in 1.3; Broken in 1.2, fixed in 1.3)* **`==`, `!=`, `<=`, and `>=` tell the same instant apart** where two `DATETIME` values carry different offsets, and so do **`GROUP BY` and `DISTINCT`**, while `<` and `>` compare them as instants. So `"2026-10-20T00:00:00Z" AS DATETIME == "2026-10-20T02:00:00+02:00" AS DATETIME` is `false`, `e.time >= "2026-10-20T02:00:00+02:00" AS DATETIME` leaves out an event written at exactly that instant, and `GROUP BY` puts the two into different groups.

*(Changed in 1.3)* A `DATE` compared with a `DATETIME` stands for **midnight at the start of its day** where it stands on the right, so `e.time == "2026-10-20" AS DATE` holds only for an event written exactly at midnight UTC. **With the `DATE` on the left, the `DATETIME` is cut to its date** instead, so swapping the operands can change the result: for an event written in the afternoon, `e.time == DATE(e.time)` is `false`, while `DATE(e.time) == e.time` is `true`.

*(Changed in 1.3)* To find the events of a day, compare the time with a range, as in `e.time >= "2026-10-20" AS DATE AND e.time < "2026-10-21" AS DATE`, or compare its date, as in `DATE(e.time) == "2026-10-20" AS DATE`.

### Conditional Functions

- **`IF(condition, then, else)`**: Returns `then` if condition is true, otherwise `else`.

## Aggregation Functions

When **aggregation functions** are used, **the result will contain a single item, unless grouped**. Only aggregate functions and constant expressions are allowed in the projection. Mixing aggregate functions with source-bound fields (e.g. `e.id`) is not allowed.

**Examples:**

```eventql
// Allowed
PROJECT INTO {
  sum: SUM(e.data.count),
  label: "everything summed",
  randomvalue: RAND()
}

// Not allowed
PROJECT INTO {
  sum: SUM(e.data.count),
  id: e.id
}
```

*(Changed in 1.3; Broken in 1.2, fixed in 1.3)* A key of a structure, or the type of a cast, that bears the name of a source counts as a field of that source, so `FROM e IN events PROJECT INTO { count: COUNT(), keys: { e: 1 } }` fails with `aggregate functions and source field accesses cannot be mixed in the INTO clause`, although it reads nothing of an event.

**Available functions:**

- **`COUNT()`**: Counts all input items.
- **`COUNT(x)`**: Counts only inputs where `x` evaluates to `TRUE`.
- **`SUM(x)`**: Sum of all values of `x`.
- **`AVG(x)`**: Arithmetic mean of values of `x`.
- **`MIN(x)`**: Minimum value of `x`.
- **`MAX(x)`**: Maximum value of `x`.
- *(Changed in 1.3; Broken in 1.2, fixed in 1.3)* **`MEDIAN(x)`**: Meant as the median value of `x`, but **returns the value that arrives in the middle**, as a float, without sorting the values first. For an even number of values, it is the later of the two values in the middle.
- **`STDDEV(x)`**: Sample standard deviation of `x`, dividing by one less than the number of values. For a single value, the query fails with `json: unsupported value: NaN`.
- **`VARIANCE(x)`**: Sample variance of `x`, dividing by one less than the number of values. For a single value, the query fails with `json: unsupported value: NaN`.
- **`UNIQUE(x)`**: Ensures `x` has the same value across all inputs. For example the field that is used by the `GROUP BY` expression, which is always the same within each group.

`SUM`, `AVG`, `MEDIAN`, `STDDEV`, and `VARIANCE` take numbers, `MIN` and `MAX` take numbers, dates, times, and date-times, but no strings, and `COUNT(x)` takes a boolean. **Any other value fails the query, `NULL` included**, such as ``unsupported argument type to `sum` at position 0, got NULL, expected one of [FLOAT INTEGER]`` for `SUM(e.data.price)` over events of which some lack `price`. So leave such events out first, as in `WHERE e.data.price != NULL`. `UNIQUE` fails with `unique constraint violated` as soon as a value differs from the first one.

*(Removed in 1.3; Broken in 1.2, fixed in 1.3)* A projection that calls **the same aggregate twice**, such as `{ a: SUM(x), b: SUM(x), c: COUNT() }`, **feeds no values to the aggregates after the second call**, so that a `COUNT()` among them, for example, returns `0`.

*(Changed in 1.1)* Over no rows at all, **`COUNT` and `SUM` return `0`**, while `AVG`, `MIN`, `MAX`, `MEDIAN`, `STDDEV`, `VARIANCE`, and `UNIQUE` **fail the query** with an error such as ``cannot use `max` on an empty dataset``. Where no event matches, an **[`isEventQlQueryTrue` precondition](/docs/eventsourcingdb/v1.2/preconditions#iseventqlquerytrue)** that compares such an aggregate, such as `MAX(e.data.price) < 50`, therefore refuses the write with an error rather than letting it pass. Guard the aggregate with `COUNT()`, since `OR` stops at its first true operand:

```eventql
FROM e IN events
WHERE e.type == "io.eventsourcingdb.library.book-acquired"
PROJECT INTO COUNT() == 0 OR MAX(e.data.price) < 50
```

## Reading Only Part of the Events

*(Changed in 1.3)* A query reads **every event** of the store and filters them by its `WHERE` clause, unless the clause **bounds the event ID**: `e.id AS INT` compared with an integer by `==`, `<`, `<=`, `>`, or `>=`, with the field on the left and the integer on the right, and joined with the other conditions **by `AND` only**, as in `e.id AS INT >= 1000`, has the query read only the events within that bound.

An **`ORDER BY e.id`** reads the events in the order in which they are stored rather than sorting them, and **backwards for `DESC`**, so that `ORDER BY e.id DESC TOP 100` reads the 100 latest events and stops. Any other order, such as `ORDER BY e.time DESC TOP 100`, reads every event and holds all of them while it sorts them, as **[Rows Held in Memory](#rows-held-in-memory)** describes.

## Rows Held in Memory

*(Changed in 1.1)* Most parts of a query pass on one row after the other, but some **hold rows in memory**. Each of them holds **at most 1,000,000 rows** at once, and a query that needs more fails with `query result exceeds limit of 1000000 concurrently loaded items`.

*(Changed in 1.3)* Rows are held by an **`ORDER BY`**, which holds every row it sorts, unless it orders by `e.id`, which needs no sorting, and by a **`GROUP BY`**, which holds every row of every group.

*(Changed in 1.3; Broken in 1.2, fixed in 1.3)* **`MEDIAN`**, **`STDDEV`**, and **`VARIANCE`** keep a value for **every row they aggregate**, and **`DISTINCT`** keeps every distinct result it hands out; the limit counts neither.

## Example Queries

*(Changed in 1.3)*

```eventql
// Select the 100 most recent events
FROM e IN events
ORDER BY e.id DESC
TOP 100
PROJECT INTO e
```

```eventql
// Select events of a specific type
FROM e IN events
WHERE e.type == "io.eventsourcingdb.library.book-acquired"
PROJECT INTO { id: e.id, book: e.data.title }

// Count all events of a certain subject
FROM e IN events
WHERE e.subject == "/books/42"
PROJECT INTO { total: COUNT() }

// Select events with nested data
FROM e IN events
WHERE e.type == "io.eventsourcingdb.library.book-acquired" AND e.data.price != NULL AND e.data.price > 20
PROJECT INTO { id: e.id, price: e.data.price }

// Group by event type and count per group
FROM e IN events
GROUP BY e.type
PROJECT INTO { type: UNIQUE(e.type), count: COUNT() }

// Use a subquery to enrich events
FROM e IN (
  FROM e IN events
  WHERE e.type == "io.eventsourcingdb.library.book-acquired" AND e.data.total != NULL
  PROJECT INTO { orderId: e.id, value: e.data.total }
)
WHERE e.value > 100
PROJECT INTO e
```