> For the complete documentation index, see [llms.txt](https://docs.mediarithmics.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.mediarithmics.io/setup-and-monitor/operate-and-monitor/query-your-datamart/otql/otql-documentation.md).
# OTQL documentation
The OTQL language reference: operators, scoring, aggregations, joins, limits, and caching.
## Your goal
Find the operator or directive you need, and use it correctly.
## Before you read this
This page is the reference. What OTQL is for and how a query is structured is in [OTQL](/setup-and-monitor/operate-and-monitor/query-your-datamart/otql.md); worked queries are in [OTQL examples](/setup-and-monitor/operate-and-monitor/query-your-datamart/otql/otql-examples.md).
## WHERE - object tree expressions
The `WHERE` clause is a list of predicates separated by the logical operators `AND`, `OR`, and `NOT`. Parentheses group predicates.
```sql
SELECT (...) FROM (...) WHERE (PredicateA AND PredicateB) OR PredicateC
SELECT (...) FROM (...) WHERE price > 50.0 AND last_modified_date > "now-10d"
```
{% hint style="info" %}
A predicate does not return a boolean directly but a **score** - `1` if the condition holds, otherwise `0`. At the end the score is compared to `0`, and the predicate is true if it is higher. The logical operators keep the same priority as boolean ones.
This is not a curiosity: it is what makes the scoring operators below possible.
{% endhint %}
### Date operators
`>=`, `>`, `<=`, `<`, `=` or `==`, `!=`.
Dates can be written in three forms:
* **ISO 8601**, with the time part optional: `2012-09-27`, `2012-09-27T12:42:00`
* a **timestamp in milliseconds**: `1549365498507`
* **date math**, defining a relative date
```sql
SELECT (...) FROM UserPoint WHERE activities {creation_ts <= "2012-09-27"}
SELECT (...) FROM UserPoint WHERE activities {creation_ts > "now-7d"}
```
#### Date math
A relative date compared to an anchor. The anchor is `now`, or a date in ISO 8601 or timestamp form followed by `||`. Then one or more expressions: `+1h` adds an hour, `-1d` subtracts a day, `/d` **rounds down** to the nearest day.
Assuming now is `2001-01-01 12:00:00`:
```
now+1h → 2001-01-01 13:00:00
now-1h → 2001-01-01 11:00:00
now-1h/d → 2001-01-01 00:00:00
2001.02.01||+1M/d → 2001-03-01 00:00:00
```
| Unit | Meaning |
| ---------- | ------- |
| `y` | Years |
| `M` | Months |
| `w` | Weeks |
| `d` | Days |
| `h` or `H` | Hours |
| `m` | Minutes |
| `s` | Seconds |
{% hint style="success" %}
Rounding matters for reproducibility. `now-7d` moves with the clock; `now-7d/d` is stable within a day, which is what you want in a segment or a saved query.
{% endhint %}
### String operators
Only **indexed** `String` fields are eligible, and the behaviour depends on the `data_type` declared in the [schema](/setup-and-monitor/collect-and-manage-your-data/design-your-data-model-and-schema/schema-definition.md#treeindex-and-how-strings-are-indexed).
{% tabs %}
{% tab title="data\_type: text" %}
All operators are **case-insensitive**. Values are stored as a set of transformed words.
| Operator | What it does |
| ---------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `match(fieldName, comparisonValue, operator = "or")` | True if words in `fieldName` match words in `comparisonValue`. `operator` defaults to `"or"` - true if **at least one** word matches; `"and"` requires **all** words of `comparisonValue` to be found. |
| `starts_with(fieldName, comparisonValue)` | True if a word of the text in `fieldName` starts with one of the words in `comparisonValue`. |
The same transformation applied to the stored data is applied to the comparison value.
```sql
-- Searches the text for words matching 'hello' or 'world'
(...) WHERE match(url_as_text, "Hello World!")
```
{% hint style="warning" %}
Diacritics such as é, è, à, ç, digits, and words containing an apostrophe are usually **stored as-is**, which means you have to provide the same value in `match`.
{% endhint %}
What gets stored, and what therefore matches
| Ingested | Stored | Matches | Does not match |
| ------------------------------------------- | ----------------------------------- | ----------------------------------- | ---------------------------------------------- |
| `Métamorphosé` | `métamorphosé` | `Métamorphosé`, `métamorphosé` | `metamorphose`, `métamorphos`, `métamorphosés` |
| `hameçon, ligne et bouchon` | `hameçon`, `ligne`, `et`, `bouchon` | any of those, any case | `hamecon` |
| `aujourd'hui` | `aujourd'hui` | `aujourd'hui` | `aujourd hui`, `aujourd`, `hui` |
| `100` | `100` | `100` | `1000`, `10` |
| `100 900 km` | `100`, `900`, `km` | the whole string, or any word | `100900`, `100,900` |
| `km/h` | `km`, `h` | `km/h`, `km`, `h` | `kmh` |
| `100km/h` | `100km`, `h` | `100km/h`, `100km`, `h`, `km/h` | `100`, `km` |
| `H&M` | `h`, `m` | `H&M`, `h`, `m` | `hm` |
| `£1,000` / `1,000` / `1,000+` | `1,000` | all three | `1000`, `000`, `1.000` |
| `1.000` | `1.000` | `1.000` | `1000`, `000`, `1,000` |
| `chou-fleur` / `chou- fleur` / `chou fleur` | `chou`, `fleur` | all forms, or either word | `chou_fleur`, `choufleur` |
| `chou_fleur` | `chou_fleur` | `chou_fleur` | `chou`, `fleur`, `choufleur`, `chou-fleur` |
| `recherche.aspx` | `recherche.aspx` | `recherche.aspx` | `recette`, `aspx` |
| `test@mics.com` | `test`, `mics.com` | `test@mics.com`, `test`, `mics.com` | `@`, `mics` |
Note the two rules that surprise people: `.` and `_` do **not** split, and `-` and `/` do.
{% endtab %}
{% tab title="data\_type: keyword" %}
All operators are **case-sensitive**. The value is a single word, with no transformation.
| Operator | What it does |
| ----------------------------------------- | -------------------------------------------------------------------------------------- |
| `starts_with(fieldName, comparisonValue)` | True if the **exact** value in `fieldName` starts with the **exact** comparison value. |
| `=` or `==` | True if the **exact** values are equal. |
```sql
(...) WHERE starts_with(mykeyword, "Hello World!")
(...) WHERE mykeyword == "Hello World!"
```
{% endtab %}
{% endtabs %}
### IN
A shortcut for filtering on several values of the same field.
```sql
SELECT {events {basket { amount @sum}}} FROM UserActivity
WHERE channel_id IN ["2456","5489","1426"]
```
Equivalent to `channel_id = "2456" OR channel_id = "5489" OR channel_id = "1426"`.
{% hint style="info" %}
`IN` performs **better** than multiple `OR` operators.
{% endhint %}
### is\_defined
Checks whether a field has a value. It applies to any indexed field and returns a boolean.
```sql
-- All UserPoints with a profile
SELECT { id } FROM UserPoint WHERE is_defined(profiles)
-- All UserPoints with an email in their profile
SELECT { id } FROM UserPoint WHERE profiles{is_defined(email)}
```
| Field value | Returns |
| --------------- | ------- |
| `"example"` | True |
| `["example"]` | True |
| `[null]` | True |
| `""` | True |
| `[]` | True |
| `null` | False |
| `undefined` | False |
| No field at all | False |
{% hint style="warning" %}
`[null]` and `""` count as **defined**. `is_defined` tells you the field exists, not that it holds anything useful.
{% endhint %}
## Scoring operators
Every pair of braces `{ }` containing a sub-query implicitly calculates a **score** for that sub-query. **By default the score is the number of items matching the sub-query.**
This is how you express frequency conditions.
```sql
-- At least one purchase, at least one visit - no scoring needed
SELECT (...) FROM UserPoint WHERE activities { events { name = "$transaction_confirmed" } }
-- Bought through at least 3 different visits
SELECT (...) FROM UserPoint
WHERE activities @ScoreSum(min: 3.0){ events { name = "$transaction_confirmed" } }
-- At least one activity containing at least 3 transaction events
SELECT (...) FROM UserPoint
WHERE activities { events @ScoreSum(min: 3.0) { name = "$transaction_confirmed" } }
```
{% hint style="danger" %}
The last two queries differ only in **where the directive sits**, and they mean different things: "three visits with a purchase" versus "one visit with three purchases". Placement is the whole semantics.
{% endhint %}
| Directive | What it does |
| ----------------------- | ----------------------------------------------------------------------------------------------- |
| `@ScoreSum(min: Float)` | Returns the score only if the nested sub-query has a score greater than or equal to `min`. |
| `@ScoreAvg(min: Float)` | Averages the sub-query's matching scores, and returns true if the average is at least `min`. |
| `@ScoreMax(min: Float)` | Takes the maximum of the sub-query's matching scores, and returns true if it is at least `min`. |
{% hint style="warning" %}
Because the default score is *the number of matching items*, `@ScoreMax` and `@ScoreAvg` used alone are useless - every sub-query score would be `1`. They only make sense once you change how the score is calculated.
{% endhint %}
### Changing how scores are calculated
Leaves of your object tree often carry numeric fields - `Int` or `Float`. `@ScoreField(name: String)` uses such a field's value as the score of the sub-query instead of a count, which is what turns a frequency condition into a monetary one.
```sql
-- Users having spent at least 1000 in a single event
SELECT (...) FROM UserPoint
WHERE activities { events @ScoreSum(min: 1000) {
amount @ScoreField(name: "amount")
}}
```
{% hint style="info" %}
`@ScoreField` on its own achieves nothing a logical operator could not - it is meaningful only combined with `@ScoreSum`, `@ScoreAvg`, or `@ScoreMax`.
{% endhint %}
## SELECT operations
Selecting fields. Any field in the schema can be selected.
```sql
-- Fields at the root level
SELECT { id name } FROM UserPoint
-- A root field plus fields on a linked object
SELECT { name emails { creation_ts id } } FROM UserPoint
```
## Aggregation operations
Aggregations are initiated by a directive in the `SELECT` clause. They take the `WHERE` filter into account, but are **not compatible with the `@filter` directive** used in `SELECT`.
{% hint style="warning" %}
Fields used with metric and bucket directives must carry the `@TreeIndex` directive in your schema.
{% endhint %}
### @count
```sql
SELECT @count {} FROM UserPoint
SELECT @count{} FROM UserPoint WHERE creation_date >= "now-7d/d"
```
### Metric directives
They calculate one value per bucket, or a single value over everything if no bucket directive is used.
| Directive | What it returns | Applies to |
| -------------- | ---------------------------- | ----------------- |
| `@avg` | Average value | Numeric values |
| `@min` | Minimum value | Numeric values |
| `@max` | Maximum value | Numeric values |
| `@sum` | Sum of values | Numeric values |
| `@cardinality` | Count of **distinct** values | Any indexed field |
```sql
-- Average basket amount over a period
SELECT {basket {amount @avg}} FROM UserEvent
WHERE { date >= "2020-12-01" AND date <= "2020-12-31" }
-- Number of channels in the datamart
SELECT {channel_id @cardinality} FROM UserEvent
-- Number of cookies on UserPoints in a segment
SELECT {agents{id @cardinality}} FROM UserPoint WHERE segments {id="XXXX"}
```
### Bucket directives
They separate values into buckets.
| Directive | What it does |
| ---------------------------------- | -------------------------------------------------------------------------------------------------- |
| `@map` | One bucket per field value. |
| `@histogram(interval: N)` | An aggregated count on a numeric field, in intervals of `N`. |
| `@date_histogram(interval: "...")` | An aggregated count by period. Allowed intervals: `1M` for a month, or `XXd` for a number of days. |
```sql
-- Average session duration per channel
SELECT { channel_id @map { session_duration @avg } } FROM UserActivity
-- Basket amounts in bands of 50
SELECT { order { amount @histogram(interval:50)}} FROM UserEvent WHERE date >= "now-7d"
-- Transactions per channel, per day
SELECT { channel_id @map {date @date_histogram }} FROM UserEvent
WHERE name = "$transaction_confirmed"
-- Forcing a monthly interval
SELECT { channel_id @map {date @date_histogram(interval: "1M") } } FROM UserEvent
WHERE name = "$transaction_confirmed"
```
{% hint style="info" %}
* `@map` **does not count null values**.
* `@map` returns **50** buckets by default, up to a maximum of **50,000**: `SELECT { name @map(limit:200) } FROM UserEvent`.
* **Bucket directives cannot be used with metrics** on the same field.
{% endhint %}
### Aliases
An alias names a field expression in the output - necessary as soon as you aggregate the same field twice.
```sql
SELECT {
numberOfChannels: channel_id @cardinality
averageDuration: duration @avg
minimumDuration: duration @min
} FROM UserEvent
```
## JOIN
Adds predicates from another object, after the `FROM`/`WHERE` clauses. Several joins can appear in the same query.
```sql
-- $transaction_confirmed activity of UserPoints in segment 1234
SELECT { id }
FROM ActivityEvent WHERE name=="$transaction_confirmed"
JOIN UserPoint WHERE segments { id="1234" }
```
## LIMIT
```sql
SELECT { id } FROM ActivityEvent WHERE name=="$transaction_confirmed" LIMIT 5
```
The Query Tool returns **10** elements by default; `LIMIT` overrides that.
{% hint style="danger" %}
* **`LIMIT` cannot return more than 10,000 elements.** The query fails if you try.
* **`LIMIT` is ignored during segment calculation.** A `USER_QUERY` segment created with a `LIMIT` returns **every** UserPoint matching the `WHERE` clause.
{% endhint %}
`LIMIT` is also ignored when using `@count` or `@cardinality`:
```sql
SELECT @count{ } FROM ActivityEvent WHERE name=="$transaction_confirmed" LIMIT 5
-- Returns the count of ALL matching activities
```
{% hint style="info" %}
To extend the number of buckets returned by an aggregation directive, use the directive's own `limit` parameter - `@map(limit:200)` - not the `LIMIT` clause.
{% endhint %}
## Executing a query
```
POST https://api.mediarithmics.com/v1/datamarts/:datamart_id/query_executions/otql?use_cache=true
```
The body is the OTQL query as a string.
The response carries the result plus useful metadata: `took`, `timed_out`, `result_type`, `precision`, `sampling_ratio`, `rows`, and `cache_hit`.
A `503` with `SERVICE_UNAVAILABLE` means retry later.
### Query cache
With `use_cache=true`, the system returns the query from the cache when available. Check the `cache_hit` property of the response to know whether it did.
{% hint style="info" %}
The cache expiration delay is **12 hours**.
{% endhint %}
Without the parameter, or with `use_cache=false`, the cache is skipped: the query executes and its result is **not stored**. You cannot use this to force a cache update.
{% hint style="warning" %}
The cache is keyed on the query text, not on what the query means. `SELECT ... WHERE ts >= "now-1h"` returns the **same** result now, in five minutes, and for the next hour when cached - even though `now` has moved.
{% endhint %}
## Optimisation
The engine tries to optimise queries automatically before running them - for instance rewriting multiple `OR` operators as an `IN` when that is better. Writing `IN` yourself is still clearer, and guaranteed.
## Related pages
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.
Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:
```
GET https://docs.mediarithmics.io/setup-and-monitor/operate-and-monitor/query-your-datamart/otql/otql-documentation.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.