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, and types can be written in any casing.

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:

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.

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.

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 (or null if not set).
tracestate: Distributed tracing state (or null if not set).
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.

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. There is no implicit boolean conversion – non-boolean values result in an error.

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 id of an event is a string in the result, but compares as a number with a number, so e.id >= 1000 holds for the event with ID 1000 and every one after it. An id that comes out of a subquery is an ordinary string, so compare it with AS INT, such as e.id AS INT >= 1000.

Sorting, Grouping and Limiting¶

ORDER 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 numbers, so 10 follows 9, although id is a string in the result; cast to another type, such as e.id AS STRING, it compares as that type. 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. 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.
SKIP <int-expression> skips a number of results after filtering, ordering, and grouping.
TOP <int-expression> limits the number of returned items or groups.

There is no ungrouped output of group members (i.e. no array-style group flattening). Grouping is intended exclusively for aggregation; grouped items cannot be enumerated individually.

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

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

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), and so does a value the target type cannot hold (e.g. cannot cast 'NaN' of type STRING to INTEGER: out of range). A cast to INT cuts off the fractional part, and keeps integers exact up to 1,000 digits, so '99999999999999999999' AS INT is 99999999999999999999.

Expression Language¶

Expressions in EventQL include:

Literals: Numbers, strings, booleans, null, timestamps.
Object literals (hash maps): { key: value, ... }
Array literals: [ value1, value2, ... ]
Field references: e.g. e.data.price
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, which are exact up to 1,000 digits, or decimals, which are 64-bit floating-point numbers. An operation on an integer and a decimal yields a decimal, and so does a division. Numbers compare by their value, whatever their type, so 1 == 1.0 is true.

Built-in Functions¶

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.
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 π.

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, array, or hash map.
INSTR(x, y): Index of substring y in string x.
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.
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.

Date and Time Functions¶

NOW(): Current timestamp.
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.
WEEKDAY(x): Weekday (0 = Sunday) of a DATE or DATETIME.

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.