EventQL Reference

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

FROM <alias> IN <source>
[FROM <alias> IN <source>] ...
[WHERE <condition>]
[ORDER BY <field> [ASC | DESC] [, <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.

Added in 1.3A block comment that is never closed takes the rest of the query as its text and fails the query with query-malformed where it opens, whatever that text holds, so that text is neither checked nor counted towards the bounds below.

Added in 1.3A query may nest at most 256 levels, and a chain of operators in it may hold at most 10,000 operands. A level opens with every parenthesis, bracket, and brace, whether around an expression, the arguments of a function, an array, an index, a map, or a subquery, and with every prefix operator, NOT and -, in front of an operand, so NOT NOT e.data.isActive nests two levels. A chain is what binary operators join, whatever their precedence, as in 1 + 2 * 3, together with every field, index, and cast that follows an operand, each of which counts as one operand more. A comma, a colon, and the keyword of a clause end a chain, and a chain within brackets counts towards the chain around them. Both are counted before the query is parsed, so a query beyond either bound is refused with query-too-deep, also where it would not parse either.

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.

Changed in 1.3A query is refused before it reads an event, with query-unevaluable, where it names two of its sources alike, with duplicate source identifier 'e', reads a source that does not exist, with unknown source: nosuch, mixes aggregate functions with fields of its sources in its PROJECT INTO, with aggregate functions and source field accesses cannot be mixed in the INTO clause, or has a SKIP or a TOP that cannot be evaluated or that its clause does not take, such as TOP 1 - 1, with TOP must be at least 1, wherever it does so, subqueries included. So whether a query fails does not depend on its data, and the error points at the mistake inside the subquery, as it does for the same mistake in the query itself.

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.2signature: 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.3A name, in dot notation as for an alias or a key of a structure, follows the Unicode standard for identifiers (UAX #31): it starts with a character of ID_Start or an underscore and goes on with characters of ID_Continue, the letters, marks, and digits of every script, also beyond U+FFFF, such as größe, μήκος, or 名前. It leaves out every format character, every punctuation mark other than the underscore, and every symbol, also the few of them that ID_Start and ID_Continue hold, such as the zero-width joiner, the middle dot, or ℘, since they are invisible or read as something else. Every character that is not part of a name ends it: a space of any kind, such as a no-break space, a control or format character, such as a zero-width space or a zero-width joiner, punctuation, such as -, #, a middle dot, or a curly quote, and a symbol, such as *, €, a typographic minus, or an emoji. So e.data.qty*2 multiplies the field qty by 2, and a character that starts nothing else either fails the query with query-malformed rather than becoming part of the name, as e.data.qty#1 fails with token recognition error at: '#', and e.data.n−2, written with a typographic minus, with token recognition error at: '−'. A field whose name holds a character a name cannot, or starts with a digit, is read with a string in the brackets, as in e.data["qty#1"] or e.data["2fa"]; a key of a structure cannot be such a name, so PROJECT INTO { 2fa: e.data["2fa"] } fails.

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.3There is no implicit boolean conversion: a condition that is not a boolean, NULL included, results in an error, such as not a BOOLEAN, got NULL, and so does an operand of AND, OR, XOR, or NOT 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.3Since the event type is not a boolean, WHERE NOT e.type == "io.eventsourcingdb.library.book-borrowed" fails with not a BOOLEAN, got STRING, which points to the missing parentheses. To find the events that lack a field, compare it with NULL, as in WHERE e.data.price == NULL, rather than negating it.

Changed in 1.3Values of different types are unequal, whichever stands on the left: == is false and != is true, so 1 == [1, 2] and [1, 2] == 1 are both false, and WHERE e.data.isActive == true leaves out the events whose isActive holds a number or a string. Only numbers compare across their types, by their value, so 1 == 1.0 holds. <, >, <=, and >= on values of any other two types end the query with an error, such as type mismatch: INTEGER < BOOLEAN.

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.3That error is a type mismatch whichever side NULL stands on, so 5 > NULL fails with type mismatch: INTEGER > NULL, just as NULL < 5 fails with type mismatch: NULL < INTEGER.

Changed in 1.3That error is a type mismatch whichever side each value stands on, so [1] < 5 fails with type mismatch: ARRAY < INTEGER, just as 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.3A number and a string are unequal, as values of different types are, so 42 == "42" is false and 42 != "42" is true. <, >, <=, and >= fail on them with an error that names the types as the query has them, such as unknown operator: INTEGER > STRING for 5 > "a". To compare a number in the data of events, compare it with a number, as in e.data.zip == 10115.

Changed in 1.3Arrays and objects compare by their content with == and !=. Two arrays are equal where they hold equal elements in the same order, and two objects where they hold the same fields with equal values, in any order, so WHERE e.data.address == {city: "Berlin", zip: "10115"} holds for an event whose address has these two fields and no other. Arrays and objects inside them compare by their content as well. Their elements and fields compare as CONTAINS compares a value with the elements of an array: numbers by their value, whatever their type, and any other value only with one of the same type. So {zip: 10115} == {zip: 10115.0} holds, while {zip: 10115} == {zip: "10115"} is false, as 10115 == "10115" is.

Added in 1.3The id of an event is a string, in the result as wherever a query compares it, so e.id == "1000" holds for the event with ID 1000, while e.id == 1000 is false, and e.id >= 1000 ends the query with unknown operator: STRING >= INTEGER. To compare it as a number, cast it with AS INT, as in e.id AS INT >= 1000, which holds for the event with ID 1000 and every one after it.

Sorting, Grouping and Limiting#

Changed in 1.3ORDER BY <field> sorts the result ascending (default) or descending. Several fields, separated by commas and each with a direction of its own, sort by the first, rows with an equal first value by the second, and so on, as in ORDER BY e.type, e.time DESC. Ordering by the id of an event compares the ids as the strings they are, byte by byte, so 10 comes before 9; order by e.id AS INT to compare them as numbers, in the order in which the events were stored. Rows whose values are all equal keep the order in which they were read when the last field sorts ascending, and come in the reverse order when it sorts descending, as though the order in which they were read followed as a last field in that direction, so that reversing every direction reverses the result exactly: ORDER BY e.time DESC lists the events of one write, which share their time, latest first, and so does ORDER BY e.subject, e.time DESC within each subject.
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.3SKIP <int-expression> skips a number of results, or of groups with GROUP BY.
TOP <int-expression> limits the number of results, or of groups with GROUP BY.

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.

Added in 1.3An error names a value whole only where its text takes at most 100 bytes, and a longer one by its type alone, so that an error stays short however long the value it concerns: where one row holds a title of more than 100 bytes and another a number, ORDER BY e.data.title fails with an error such as type mismatch: FLOAT STRING 1.000000 a STRING longer than 100 bytes. The error of UNIQUE and that of a cast name values the same way, as in unique constraint violated, expected a STRING longer than 100 bytes, got a STRING longer than 100 bytes and cannot cast a STRING longer than 100 bytes to INTEGER: invalid format.

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:

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() }

[{"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].

Added in 1.3SKIP and TOP limit the results, after the projection, DISTINCT, and aggregates, as OFFSET and LIMIT do in SQL: TOP 2 PROJECT INTO COUNT() counts every row, and TOP 3 PROJECT INTO DISTINCT e.type returns the first three distinct event types. Where each row is a result of its own, without GROUP BY, DISTINCT, or aggregates, the rows they leave out are not projected, so that a projection that would fail on one of them does not end the query, and a server with a signing key does not sign their events, unless the query reads a signature, or an event as a whole, before its projection. Otherwise, every row goes into the results, so that the failure of any row ends the query.

TOP takes an integer of at least 1, and SKIP one of at least 0. A value written in the query that breaks this, such as TOP 0, SKIP -1, or TOP 1.5, makes the query malformed, with TOP must be at least 1, SKIP must be at least 0, or TOP must be an integer. A value that the query computes, as in TOP 1 - 1, fails the query when it runs instead. To set no limit, leave out TOP.

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:

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.3Two objects are duplicates where they hold the same keys with the same values, whatever the order of their keys, so { a: 1, b: 2 } and { b: 2, a: 1 } are one result of DISTINCT and one group of GROUP BY.

Type Conversion#

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

"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.3A value the target type cannot hold results in a runtime error as well (e.g. cannot cast 'NaN' of type STRING to INTEGER: out of range), and a cast to INT keeps integers exact up to 1,000 digits, so '99999999999999999999' AS INT is 99999999999999999999.

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.3Integers are exact up to 1,000 digits, and an integer in the data of an event that a 64-bit floating-point number cannot hold exactly, such as 12345678901234567891, is kept as an exact integer. A literal beyond its type, an integer with more than 1,000 digits or a decimal beyond the range of a 64-bit floating-point number, makes the query malformed, with an error that names the literal and says that it is out of range. CONTAINS, GROUP BY, DISTINCT, and ORDER BY compare numbers by their value as well, whatever their type.

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.3Any other value joined with a string ends the query with a type mismatch, whichever side it stands on, such as type mismatch: BOOLEAN + STRING for true + "a" and type mismatch: NULL + STRING for NULL + "a", so a field that an event lacks does not join as null. To join a boolean, a date, a time, or a date-time, cast it first, as in "Active: " + (e.data.isActive AS STRING).

Changed in 1.3A computation whose integer result has more than 1,000 digits fails with integer overflow: the result has more than 1000 digits. The modulo of decimals keeps the fractional part and has the sign of the dividend, as for integers, so 5.5 % 2 is 1.5, and -5.5 % 2 is -1.5.

Changed in 1.3An operand of a type that the operator does not take together with the other ends the query with a type mismatch, whichever side it stands on, NULL and arrays included, such as type mismatch: INTEGER + NULL for 5 + NULL; two operands of the same type that it does not take end it with an unknown operator, such as unknown operator: BOOLEAN + BOOLEAN.

Built-in Functions#

Added in 1.3A function is always called, with its arguments in parentheses, as in LEN(e.data.items) or PI(), and so is an aggregate function, as in COUNT(). A query that names one without calling it, such as PROJECT INTO { items: LEN }, fails with function must be called: LEN, as in LEN(...) before it reads an event, wherever it names it, subqueries included. A source and a key of a structure may still bear the name of a function, as in FROM len IN events or { len: LEN(e.data.items) }, and a call of that name still calls the function, or the aggregate function, whatever the case of either, as in len(len.data.items), since a source cannot be called.

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.3A computation whose result is not a number, such as SQRT(-1), ends the query at the expression that computed it, with query-unevaluable, and so does an isEventQlQueryTrue precondition. To leave such events out, exclude them first, as in WHERE e.data.x >= 0 AND SQRT(e.data.x) >= 0.

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.3SUBSTRING(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. start has to lie between 0 and the length of x; otherwise the query fails with substring: start index must not be negative or, for example, substring: start index 6 is out of range for a string of length 5. An end beyond the end of x is capped at its end, and one before start yields "".
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.3EventQL takes dates and times in UTC throughout, and the zone of the server plays no part. NOW() returns the current time in UTC, and DATE(x), TIME(x), and the casts of a DATETIME to DATE and TIME convert the DATETIME to UTC first, so DATE("2026-10-20T23:30:00-02:00" AS DATETIME) is 2026-10-21. The parts of a DATETIME, such as HOUR and DAY, are taken in UTC as well, so HOUR("2026-10-20T14:00:00+02:00" AS DATETIME) is 12. A DATETIME is handed out in UTC, in the results of a query as by its cast to STRING and in a text that holds it, such as an error message, so a query that projects e.data.at AS DATETIME for an at of 2026-10-20T14:00:00+02:00 hands out 2026-10-20T12:00:00Z. The same holds for the time of an event restored from a backup with an offset: a query hands it out in UTC, as the same instant, while reading the events hands it out as it is stored. A time whose instant lies outside the years 0000 to 9999 in UTC cannot be written in UTC as RFC 3339 writes it, so its cast to DATETIME fails, even where its own offset keeps it within them, such as "9999-12-31T23:59:59-05:00" AS DATETIME with cannot cast '9999-12-31T23:59:59-05:00' of type STRING to DATETIME: out of range.

A DATE and a TIME are values without a zone: they compare, group, and leave out duplicates by the day or the time of day they show, so TIME("2026-10-20T14:00:00+02:00" AS DATETIME) == "12:00:00" AS TIME holds, and DATE(e.time) == DATE(NOW()) holds for the events written today in UTC. A DATE cast to DATETIME is midnight UTC at the start of its day, so DATE("2026-10-20T23:30:00-02:00" AS DATETIME) AS DATETIME equals "2026-10-21T00:00:00Z" AS DATETIME.

Changed in 1.3DATETIME values compare as instants, whatever their offsets, so "2026-10-20T00:00:00Z" AS DATETIME == "2026-10-20T02:00:00+02:00" AS DATETIME is true, e.time >= "2026-10-20T02:00:00+02:00" AS DATETIME includes an event written at exactly that instant, and GROUP BY and DISTINCT take the two as one value.

Changed in 1.3A DATE and a DATETIME are values of different types, since a day is no point in time: == is false and != is true, whichever stands on the left, so e.time == "2026-10-20" AS DATE holds for no event, and <, >, <=, and >= end the query with a type mismatch, such as type mismatch: DATETIME >= DATE. Arrays, objects, CONTAINS, GROUP BY, and DISTINCT tell the two apart as well.

Changed in 1.3To find the events of a day, compare the time with a range of DATETIME bounds, as in e.time >= "2026-10-20T00:00:00Z" AS DATETIME AND e.time < "2026-10-21T00:00:00Z" AS DATETIME, which reads only part of the events through an index, 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.

Added in 1.3Aggregate functions may stand only in HAVING and PROJECT INTO. Anywhere else, such as in WHERE, ORDER BY, GROUP BY, SKIP, or TOP, the query fails with aggregate functions can only be used in HAVING and INTO clauses, whatever its sources, subqueries included.

Examples:

// 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.3A key of a structure, or the type of a cast, may bear the name of a source, as in { count: COUNT(), keys: { e: 1 } }, since it reads nothing of the source.

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.3MEDIAN(x): Median value of x, always as a float. For an even number of values, it is the mean 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, it is NULL.
VARIANCE(x): Sample variance of x, dividing by one less than the number of values. For a single value, it is NULL.
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.

Added in 1.3Since comparing NULL is an error, a comparison of STDDEV(x) or VARIANCE(x) in a group that may hold a single value needs a guard, such as HAVING COUNT() > 1 AND STDDEV(x) > 1: AND stops at its first false operand.

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.

Added in 1.3MIN and MAX take values of one kind: numbers, whatever their type, or dates, or date-times, or times. A value of another kind than the first fails the query, as it does for ORDER BY, so a DATE followed by a DATETIME fails with unsupported argument type of consecutive call to `min` at position 0, got DATETIME, expected DATE.

Changed in 1.1Over 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 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:

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.3A query, or an isEventQlQueryTrue precondition, reads every event of the store and filters them by its WHERE clause, unless the clause lets it read only part of them through an index. That takes conditions that compare a field of the event, on the left, with a literal, on the right, and are joined with the other conditions by AND only:

A bound on e.id AS INT, compared with an integer by ==, <, <=, >, or >=, as in e.id AS INT > 1000, reads only the events within the bound.
e.subject == "<subject>" reads only the events of that subject.
e.type == "<event type>" reads only the events of that type, unless a subject is required as well, whose events the query reads instead.
A range of e.time, compared with a string cast AS DATETIME by ==, <, <=, >, or >=, as in e.time >= "2026-10-01T00:00:00Z" AS DATETIME, leaves out the events that an index of their times shows to lie outside the range.

These combine, so that e.subject == "/books/42" AND e.id AS INT > 1000 reads only the events of that book after event 1000. A query that uses nothing of the events but their ID, and the subject or event type it requires, reads no events at all, only their IDs from the index, as FROM e IN events WHERE e.subject == "/books/42" PROJECT INTO COUNT() does.

Any other condition reads every event: a literal on the left, as in "/books/42" == e.subject, conditions joined by OR or negated by NOT, and a value computed from the field, as in DATE(e.time) == "2026-10-20" AS DATE. Such a query takes the longer, the more events the store holds.

The order matters as well. An ORDER BY e.id AS INT on the only source, or on the outer one of several, whose name comes first in alphabetical order, reads the events in the order in which they are stored rather than sorting them, and backwards for DESC. So, where each row is a result of its own, without GROUP BY, DISTINCT, or aggregates, ORDER BY e.id AS INT DESC TOP 100 reads the 100 latest events and stops, while any other order, such as ORDER BY e.time DESC TOP 100, ORDER BY e.id DESC, which sorts the ids as strings, or one by the id of another source of several, reads every event and sorts them, although it holds no more than 100 of them at a time, as Rows Held in Memory describes.

Rows Held in Memory#

Changed in 1.1Most 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.3Rows are held by:

ORDER BY, unless it orders by the id cast to INT of the only source, or of the outer one of several, which needs no sorting. With TOP and without GROUP BY, DISTINCT, or aggregates, it holds no more rows than SKIP and TOP together pass on; otherwise, it holds every row it sorts.
GROUP BY with a projection of rows, rather than of aggregates, which holds every row of every group.
GROUP BY with a projection of aggregates alone, which holds one entry for each group instead, so that the limit counts the groups.
The inner sources of a join, which share the limit. Of several FROM clauses, the source whose name comes first in alphabetical order is the outer one, which is read row by row; the others are held, each with only the rows that the conditions of the WHERE naming it alone let through.

Changed in 1.3DISTINCT keeps every distinct result it hands out, and MEDIAN, STDDEV, and VARIANCE keep a number for every row they aggregate. The limit counts these as well: the numbers of all their calls together, and, in a GROUP BY with a projection of aggregates alone, those of all groups together, while the HAVING of a GROUP BY with a projection of rows, and its DISTINCT, count those of each group on its own. A query that keeps more fails with query result exceeds limit of 1000000 concurrently loaded items, as one that sorts or groups that many rows does.

The limit counts values rather than their size, so a query also holds at most 1 GiB at once in all of these parts together, its subqueries included, by an estimate of the size of every value they hold, taken as a part keeps it: a row with everything its events hold, a sort with the keys it computes for each row, a GROUP BY with the key of each group and, with a projection of aggregates alone, also with the group itself, its last row, and its result, MEDIAN, STDDEV, and VARIANCE with eight bytes for every number they keep, and UNIQUE, MIN, MAX, and a SUM of integers with the value they keep. The estimate covers a long string that a query computes as well as a large event it reads, and a part counts an event that several of its rows hold, as the rows of a join do, once. A part hands back what it no longer holds: a sort each row it has handed on, a GROUP BY each group it has handed on or its HAVING has dropped, and every part all it holds once it ends, so that the limit counts what the query holds at each moment. A query that holds more fails with query result exceeds limit of 1 GiB of concurrently loaded data, at whichever of the two limits it reaches first.

What is bounded by its size besides is a single string that a query builds, by joining with + or with REPLACE, UPPER, or LOWER: it may hold at most 1 MiB. The query works out how long the string becomes before it builds it, and fails with string is too long: a query may build strings of at most 1 MiB where it would be longer. This holds, for example, for a REPLACE with nothing to replace, as in REPLACE(x, "", y), which puts y between every two characters of x.

So is every array and object that a query builds, such as an array or an object in its projection, as in [x, x] or { a: x }, the results that a GROUP BY with a projection of rows collects for each group, and each row of a join: it may hold at most 64 MiB, by the same estimate of its size, which counts a value each time it holds it. A subquery may hand out the same value in every row, so a short query, such as one whose subqueries each put the rows of the one inside into an array ten times, can build values that hold far more than it reads or holds in memory, while writing such a value out, or comparing it, as DISTINCT, GROUP BY, UNIQUE, and ORDER BY do, takes time and memory for each of the values it holds. The query works out the size of an array or an object before it builds it, and that of a row of a join as it builds the row, before it hands it on, and fails with value is too large: a query may build arrays and objects of at most 64 MiB where it would be larger. The limit of 1 GiB still counts an event that several rows hold once, since they share it in memory.

A query that computes from rows it holds in memory, as when it combines several sources, sorts, groups, or computes STDDEV or VARIANCE, ends once its client goes away, as a query that reads events does, rather than computing on for as long as that takes. The same holds for the query of an isEventQlQueryTrue precondition.

Example Queries#