# 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**:

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

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

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.

## Sorting, Grouping and Limiting

- **`ORDER BY <field>`** sorts the result ascending (default) or descending.
- **`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:**

```eventql
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.

```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 to DATETIME`).

## 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

## 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, length)`**: Substring from `x` starting at `start` with given `length`.
- **`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.

**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
}
```

**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`.
- **`MEDIAN(x)`**: Median value of `x`.
- **`STDDEV(x)`**: Standard deviation of `x`.
- **`VARIANCE(x)`**: Statistical variance of `x`.
- **`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.

## Example Queries

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

// 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.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"
  PROJECT INTO { orderId: e.id, value: e.data.total }
)
WHERE e.value > 100
PROJECT INTO e
```