, 'i')` |
| substr(str, start\[, len]) | Returns the portion of `str` starting from the zero-based character index `start`, optionally limited to `len` characters | `WHERE substr(field_name, 0, 5) = 'hello'` |
| pos(str, substr) | Returns the zero-based character index at which `substr` is first found in `str`. Returns `-1` if not found. Use `>= 0` to test if the substring exists anywhere. | `WHERE pos(field_name, 'world') >= 0` |
| upper(str) | Returns `str` converted to uppercase | `WHERE upper(field_name) = 'HELLO'` |
| lower(str) | Returns `str` converted to lowercase | `WHERE lower(field_name) = 'hello'` |
| lpad(str, len\[, char]) | Returns `str` padded on the left to `len` characters with space or `char`. Only the first character in `char` is used | `WHERE lpad(field_name, 10, '0') = '0000001234'` |
| rpad(str, len\[, char]) | Returns `str` padded on the right to `len` characters with space or `char`. Only the first character in `char` is used | `WHERE rpad(field_name, 10, 'x') = '1234xxxxxx'` |
| ltrim(str\[, char-list]) | Trims whitespace or the `char-list` from the start of `str` | `WHERE ltrim(field_name, ' ') = 'hello'` |
| rtrim(str\[, char-list]) | Trims whitespace or the `char-list` from the end of `str` | `WHERE rtrim(field_name, ' ') = 'hello'` |
| trim(str\[, char-list]) | Trims whitespace or the `char-list` from either end of `str`. Note: To invoke the function directly, quote the name: `` `trim`(str) `` | `WHERE` `` `trim`(field_name) = 'hello' `` |
| TRIM(\[LEADING\|TRAILING\|BOTH] expr FROM expr) | Alternative syntax for trim functions: `TRIM(LEADING ' ' FROM str)` = `ltrim(str, ' ')` | `WHERE TRIM(BOTH ' ' FROM field_name) = 'hello'` |
| repeat(str, count) | Returns the input `str` repeated `count` times | `WHERE repeat('x', 5) = 'xxxxx'` |
| split(str, delim) | Returns an array of the parts of `str` delimited by `delim`. An empty string `delim` returns each character as its own array element | `WHERE array_length(split(field_name, ',')) > 2` |
| joinstr(sep, val\[, val...]) | Returns a string of the `val` arguments separated by `sep`. Non-string values (except null/missing) are converted to strings. Array elements are joined individually; nested arrays are flattened. `null` and `missing` values short-circuit to return `null`/`missing` respectively. | `SELECT joinstr(', ', tags) FROM cars` |
| POSITION(substr IN str) | Alternative syntax for `pos()`: returns the zero-based index of `substr` in `str`. Returns `-1` if not found. | `WHERE POSITION('world' IN field_name) >= 0` |
## Scalar Type Operators
Indicates which scalar type to interact with:
| **Operator** | **Purpose** | **Example** |
| :------------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :--------------------------------------- |
| is\_boolean(x) | Boolean type test | `WHERE is_boolean(field_name)` |
| is\_number(x) | Float, Int, or UInt type test | `WHERE is_number(field_name)` |
| is\_string(x) | String type test | `WHERE is_string(field_name)` |
| type(x) | Returns a string name representing the type. Returns: `boolean`, `string`, `integer`, `float`, `object`, `array`, `binary`, `null`, `missing`. | `WHERE type(field_name) = 'string'` |
| json\_type(x) | Returns the JSON type name of the value. Returns: `boolean`, `string`, `number`, `object`, `array`, `binary`, `null`. Returns `"null"` for `MISSING` values.
Available in SDK 4.5 onwards. | `WHERE json_type(field_name) = 'number'` |
Following is the mapping between data types and scalar types; all of which are case-sensitive:
| `REGISTER` | `MAP` | `ATTACHMENT` |
| --------------------------------------------------------------------------------------------------------------- | -------- | ------------ |
| \* `null`
\* `boolean`
\* `number`
\* `binary`
\* `string`
\* `array`
\* `object` | `object` | `object` |
## Conversion Operators
Converts between different value types.
| **Operator** | **Purpose** | **Example** |
| :--------------------- | :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :----------------------------------------------------------------- |
| deserialize\_json(str) | Returns the JSON string deserialized into an `object`. Can be used in `INSERT`, `UPDATE`, and `WHERE` clauses. | `WHERE deserialize_json('{"field_name": "blue"}') = 'blue'` |
| serialize\_json(value) | Returns a JSON-encoded string representation of the input value.
Available in SDK 5.0 onwards. | `SELECT serialize_json({"a":[1,2,3]})` returns `"{\"a\":[1,2,3]}"` |
| cast(v, type) | Casts from one value type to another if possible. `type` can be `'string'`, `'integer'`, `'float'`, `'boolean'`, or `'binary'` (prefix abbreviations like `'int'` and `'bool'` are also accepted). Composite types (objects and arrays) can be cast to `'string'` to produce a JSON-encoded representation. Use `TYPE(expr)` to match another field's type, e.g. `cast(field_a, TYPE(field_b))`. Also supports `CAST(v AS type)` syntax. Returns `MISSING` if conversion isn't possible. | `SELECT cast(field_name, 'integer') FROM cars` |
## Array Operators
The following table provides an overview of interactions with `array`. Arrays can be either scalar values in a `REGISTER` or an input via an argument.
| **Operator** | **Purpose** | **Example** |
| :---------------------------- | :--------------------------------------------------------------------------- | :------------------------------------------ |
| array\_contains(array, value) | Returns true if the `array` contains the `value`, otherwise returns false | `WHERE array_contains(:your_array, 'blue')` |
| array\_contains\_null(array) | Returns true if the `array` contains a `NULL` value, otherwise returns false | `WHERE array_contains_null(field_name)` |
| array\_length(array) | Returns the length of the array. | `WHERE array_length(field_name) > 0` |
## Date Operators
| **Operator** | **Purpose** | **Example** |
| :----------------------------------------------------- | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :--------------------------------------------------------------------------------------------------------------------------------------------------- |
| date\_cast(string\[,format]) | Converts a string representation of a date (in [format](#date-format-specification)) to an epoch millisecond (UTC) value. | `WHERE date_cast(t.tm,"hh:mm:ss") >= 8*60*60*1000` |
| tz\_offset(string\[,format]) | Returns the time zone offset (in minutes) from the string representation of a date (in [format](#date-format-specification)). | `WHERE tz_offset(t.dt) < tz_offset("America/New_York","TZN")` |
| clock(\[format\[,tz]]) | Reports the instant of the function’s execution, as an epoch millisecond (UTC) value or as a string in the given [format](#date-format-specification). | `SELECT clock() FROM system:dual` |
| date\_format(date, format\[, tz]) | Returns a string representation of date in the given [format](#date-format-specification), optionally adjusting the time zone. | `SELECT date_format(ms(t.dt_no_tz,"DD.MM.YYYY hh:mm:ss"), "", "+0530") FROM t`
`SELECT date_format(timestamp_ms,"") AS iso_string FROM t` |
| date\_add(date, part, count\[, format\[, tz]]) | Adds the count ‘part\[1]’s to the input date, returning a number for numeric date input and a string for string date input, unless [format](#date-format-specification) is provided. If tz is provided, the result is converted from the input time zone to the specified time zone. Negative count values subtract from the input date. | `SELECT date_add(coll.date, "day", 10) FROM coll` |
| date\_sub(date, part, count\[, format\[, tz]]) | Identical to date\_add but count functions with the sign inverted: `date_add(...,-1)` is equivalent to `date_sub(...,1)`. | `SELECT date_sub(c.date, "day", 10) FROM c` |
| date\_diff(date1, date2, part) | Reports the number (integer) of part elements between the two input dates. | `WHERE date_diff(c.dt, c.stamp, "year") < 2` |
| date\_part(date, part) | Reports the part\[2] of the date. | `SELECT date_part(c.dt, "day") FROM c` |
| date\_trunc(date, part) | Truncates the input date to the part\[1], zeroing all subsequent fields. Output is numeric for numeric input and string (ISO-8601 format) for string input. | `WHERE t.stamp >= date_trunc(clock(), "mon")` |
| date\_range(start, end, part, count\[, format\[, tz]]) | Returns an array of dates from `start` (inclusive) up to but not including `end` (exclusive) in `count` part\[1] steps (e.g. one date per month), optionally in a specific [format](#date-format-specification) and time zone.
Available in SDK 4.11 onwards. | `SELECT date_range(‘2025-01-01’, ‘2026-01-01’, ‘mon’, 1) FROM system:dual` |
\[1] Parts: year, mon/month, day, hour, min/minute, sec/second, ms/millis/millisecond.
\[2] All in \[1] plus: weekday (ISO-8601 - Monday is 1), monthname (English full month name only) & tz/timezone (string containing ±hh:mm).
### Date format specification
Date formats may be specified using SQL-like representative syntax, or using `strftime`/`date`-like syntax with percent-sign introduced formatting specifiers (a.k.a. "percent-style").
If passed as an empty string, [ISO-8601](https://www.iso.org/iso-8601-date-and-time-format.html) format is assumed. e.g. `2026-01-31T22:15:30+02:00`
The percent-style formatting is defined by the Rust `chrono` package:
* [Format specifiers](https://docs.rs/chrono/latest/chrono/format/strftime/index.html#specifiers)
* This format is similar to that used by the Unix/Linux `date` command.
* Note that `%.f` and `%.3f` formats are equivalent as the date functions operate with millisecond precision and `%.f` essentially picks between `%.3f` (millisecond), `%.6f` (microsecond) and `%.9f` (nanosecond) based on the precision.
The common SQL-like style specifies the format using representative character sequences. Similar dialects can be found in many SQL implementations. All numeric fields in this format are zero-padded. The formatting sequences are:
| **Sequence** | **Element** | **Example** | **Equivalent** |
| :----------- | :----------------------------------- | :-------------- | :------------- |
| `YYYY` | Century & year | `2026` | `%Y` |
| `CC` | Century | `20` | `%C` |
| `YY` | Year | `26` | `%y` |
| `MM` | Month | `01` | `%m` |
| `DD` | Day | `31` | `%d` |
| `hh` | Hours\[1] | `14` | `%H` |
| `mm` | Minutes | `30` | `%M` |
| `ss` | Seconds | `59` | `%S` |
| `.s`/`.sss` | Fractions of a second\[2] | `.010` | `%.f`/`%.3f` |
| `TZD` | Time zone displacement | `-0500` | `%z` |
| `TZN` | Time zone name | `Europe/London` | `%Z` |
\[1] 24-hour format only. For 12-hour format the percent style must be used.
\[2] Since date functions do not operate on fractions beyond milliseconds, these formats can be considered aliases and be used interchangeably.
#### Examples
* `date_format(timestamp_ms,"")` - convert an epoch millisecond value to an ISO-8601 string.
* `date_format(timestamp_ms,"YYYY-MM-DD")` - extract just the date portion of an epoch millisecond timestamp value as a string.
* `date_format(timestamp_ms,"%T")` - extract just the time portion of an epoch millisecond timestamp value as a string.
## Object Operators
The following table provides an overview of interactions with `object`. Objects can be either scalar values in a `REGISTER`, `MAP`, `Attachment`, or an input via an argument.
| **Operator** | **Purpose** | **Example** |
| :----------------------------------------------------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :------------------------------------------------------------------ |
| object\_length(object) | Returns the number of key-value pairs in the top-level of the object | `WHERE object_length(field_name) > 0` |
| object\_keys(object) | Returns the top-level keys (field names) of a given object as an array.
Available in SDK 4.10 onwards. | `WHERE array_contains(object_keys(field_name), 'field_i_want')` |
| object\_values(object) | Returns the top-level values of a given object as an array.
Available in SDK 4.10 onwards. | `WHERE array_contains(object_values(value_name), 'value_i_want')` |
| object\_set(object, field, value\[, replace-existing]) | Returns an object with the field set to the provided value. If `replace-existing` is `false` (default), an error is raised when overwriting an existing field. Pass `true` to silently replace.
Available in SDK 4.11 onwards. | `SELECT object_set(field_name, 'new_key', 'value', true) FROM cars` |
| object\_unset(object, field\[, ignore-missing]) | Returns an object with the specified field removed. If `ignore-missing` is `false` (default), an error is raised if the field doesn't exist. Pass `true` to silently ignore. | `SELECT object_unset(field_name, 'old_key', true) FROM cars` |
| object\_concat(object1, object2\[, ...]) | Returns an object that merges the input objects' top-level fields, with the last specified winning conflicts. | `SELECT object_concat(field1, field2) FROM cars` |
| object\_rename(object, old, new) | Returns an object with the field `old` renamed to `new`. Raises an error if `old` doesn't exist. If `new` already exists, it is silently overwritten. | `SELECT object_rename(field_name, 'old_key', 'new_key') FROM cars` |
| object\_content(object\[, config...]) | Returns the object content based on configuration options. See detailed description below.
Available in SDK 4.11 onwards. | `SELECT object_content(field_name, 'keys', 'nested') FROM cars` |
| object\_size(object) | Returns the approximate size of `object`. This is just a rough guide for comparing against other `object_size` results | `WHERE object_size(field_name) > 1000` |
### object\_content() Configuration
The `object_content()` function accepts configuration options to control output format:
**Configuration can be an object with optional fields:**
* `output`: `'fields'` (default), `'keys'`, or `'values'`
* `nested`: `'no'` (default), `'yes'`, or `'only'`
* `subscripts`: `false` (default) or `true`
**Or individual strings:**
* `'fields'`, `'keys'`, `'values'`
* `'nested'`, `'nested-only'`
* `'subscripts'`
**Output modes:**
* `fields`: An array of objects each containing a single field & value pair
* `keys`: An array of string keys (same as `object_keys()`)
* `values`: An array of values (same as `object_values()`)
**Nesting modes:**
* `no` / not specified: Don't recursively process fields
* `nested`/`yes`: Report the parent field then recursively process its contents
* `nested-only`/`only`: Don't report the parent field unless recursive processing yields no results
**Subscripts** (applies to array processing only):
* `true`: Individual elements are reported
* `false` (default): Only unique elements are reported with a subscript of `*`
Note: `subscripts` is a boolean value in the config object. The string shortcut `'subscripts'` sets it to `true`.
## Duration Operators
Duration operators work with duration strings and convert between different time units.
Available in SDK 4.12 onwards.
| **Operator** | **Purpose** | **Example** |
| :----------------------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | :----------------------------------------------------- |
| duration\_cast(str\[, unit-str]) | Returns the integer number of `unit-str` units (or milliseconds if not specified) represented by the input duration string. Units: "seconds", "milliseconds", "ms", "microseconds", "us", "μs", "nanoseconds", "ns" | `WHERE duration_cast('1h23m54.07s', 'seconds') = 5034` |
| duration\_format(value\[, unit-str]) | Returns a string with the input `value` in `unit-str` units (or milliseconds if not specified) as a duration. The smallest unit reported is seconds with fractional seconds | `WHERE duration_format(90000, 'ms') = '1m30s'` |
## Collection Operators
The following table provides the collection operators for comparing if a given value is equal to any of the values in a list:
| **Operator** | **Purpose** | **Example** |
| :----------------- | :------------------ | :---------------------------------------- |
| IN (x, y, ...) | Membership test | `WHERE department IN ('HR', 'Sales')` |
| NOT IN (x, y, ...) | Non-membership test | `WHERE department NOT IN ('HR', 'Sales')` |
## Array and Object Literals
Since version 4.8 of the Ditto SDK, Array and object literals are supported and can be used inline.
```sql SQL theme={null}
SELECT * FROM your_collection_name WHERE field1 = [0, 1]
```
If using a version prior to 4.8, use arguments to pass in your array or object instead.
```sql SQL theme={null}
SELECT * FROM your_collection_name WHERE field1 = :your_array
```
## Array and Object Transformation Expressions
`ARRAY` and `OBJECT` transformation expressions are available in SDK v5+.
DQL provides powerful transformation expressions that allow you to create new arrays and objects by iterating over source collections and applying transformations, filters, and mappings.
### ARRAY Transformation
The `ARRAY` transformation expression creates a new array by evaluating an expression for each element in a source array or object.
**Syntax:**
```sql DQL theme={null}
ARRAY value_expr FOR [name_var:]value_var IN|WITHIN source [WHEN condition] END
```
![Array Transform Syntax Diagram]()
**Components:**
* `value_expr` - Expression evaluated for each element (result added to output array unless `MISSING`)
* `name_var` - Optional identifier holding the array index (for arrays) or field name (for objects)
* `value_var` - Identifier holding the element value
* `source` - Expression evaluating to an `ARRAY` or `OBJECT`
* Returns `MISSING` if source is `MISSING`
* Returns `NULL` if source is any other non-array/object type
* `condition` - Optional filter; element included only if condition evaluates to `true`
* `IN` - Process immediate content only
* `WITHIN` - Recursively process nested arrays/objects
**Examples:**
```sql DQL theme={null}
-- Filter even numbers and transform to objects
ARRAY {"index":i,"val":v} FOR i:v IN [1,2,3] WHEN v%2 = 0 END
-- Result: [{"index":1,"val":2}]
-- Convert array elements to strings, excluding elements with a value of 1
ARRAY cast(v,'string') FOR v IN [1,2,3] WHEN v != 1 END
-- Result: ["2","3"]
-- Transform string characters based on position
ARRAY CASE WHEN i%2 = 0 THEN "foo" ELSE "bar" END FOR i:v IN split("hello","") END
-- Result: ["foo","bar","foo","bar","foo"]
-- Extract values from an object
ARRAY v FOR n:v IN {"a":"one","b":"two"} END
-- Result: ["one","two"]
-- Recursively flatten nested arrays
ARRAY v FOR v WITHIN [[1,2],3] END
-- Result: [[1,2],1,2,3]
-- Use in SELECT to transform document fields
SELECT ARRAY price * 0.9 FOR price IN prices END AS discounted_prices
FROM products
```
### OBJECT Transformation
The `OBJECT` transformation expression creates a new object by evaluating name and value expressions for each element in a source array or object.
**Syntax:**
```sql DQL theme={null}
OBJECT name_expr:value_expr FOR [name_var:]value_var IN|WITHIN source [WHEN condition] END
```
![Object Transform Syntax Diagram]()
**Components:**
* `name_expr` - Expression for field name (must evaluate to `string`)
* `value_expr` - Expression for field value (field added only if not `MISSING`)
* `name_var` - Optional identifier holding the array index or field name
* `value_var` - Identifier holding the element value
* `source` - Expression evaluating to an `ARRAY` or `OBJECT`
* Returns `MISSING` if source is `MISSING`
* Returns `NULL` if source is any other non-array/object type
* `condition` - Optional filter; element included only if condition evaluates to `true`
* `IN` - Process immediate content only
* `WITHIN` - Recursively process nested objects
**Duplicate Names:** When multiple elements produce the same field name, later values overwrite earlier ones without warning. This applies to both `IN` and `WITHIN` processing.
**Examples:**
```sql DQL theme={null}
-- Transform object keys to uppercase, filter by value length
OBJECT upper(n):v FOR n:v IN {"a":"one","b":"two","c":"three"} WHEN len(v) = 3 END
-- Result: {"A":"one","B":"two"}
-- Convert array to object with generated field names
OBJECT "field_"||cast(i,"string"):v FOR i:v IN [1,2,3] END
-- Result: {"field_0":1,"field_1":2,"field_2":3}
-- Filter object by value type
OBJECT n:v FOR n:v IN {"a":1,"b":[],"c":2} WHEN type(v) != 'array' END
-- Result: {"a":1,"c":2}
-- Process nested object (WITHIN flattens structure)
OBJECT n:v FOR n:v WITHIN {"a":{"b":1}} END
-- Result: {"a":{"b":1},"b":1}
-- Use in SELECT to filter object fields (alternative to object_unset)
SELECT OBJECT n:v FOR n:v IN u WHEN n NOT IN ("salary","bonus") END
FROM users u
```
### Use Cases
**Data Transformation:**
```sql DQL theme={null}
-- Transform array of prices with tax calculation
SELECT ARRAY price * 1.08 FOR price IN item_prices END AS prices_with_tax
FROM orders
```
**Filtering and Mapping:**
```sql DQL theme={null}
-- Extract active items only
SELECT ARRAY item FOR item IN items WHEN item.status = 'active' END AS active_items
FROM inventory
```
**Restructuring Data:**
```sql DQL theme={null}
-- Convert array to lookup object
SELECT OBJECT item._id:item.name FOR item IN catalog END AS id_to_name_map
FROM product_catalog
```
**Nested Processing:**
```sql DQL theme={null}
-- Flatten nested tag arrays
SELECT ARRAY tag FOR tag WITHIN categories END AS all_tags
FROM articles
```
## Array and Object Search Expressions
DQL provides search expressions that test whether elements in arrays or objects satisfy specified conditions. These are particularly useful in `WHERE` clauses to filter documents based on nested data.
### Syntax
```sql DQL theme={null}
ANY [name_var:]value_var IN|WITHIN expression SATISFIES condition END
EVERY [name_var:]value_var IN|WITHIN expression SATISFIES condition END
ANY AND EVERY [name_var:]value_var IN|WITHIN expression SATISFIES condition END
```
![Array Object Search Syntax Diagram]()
**Components:**
* `name_var` - Optional identifier holding the array index (for arrays) or field name (for objects)
* `value_var` - Identifier holding the element value
* `expression` - Expression evaluating to an `ARRAY` or `OBJECT`
* Returns `MISSING` if expression evaluates to `MISSING`
* Returns `NULL` if expression evaluates to any other non-array/object type
* `condition` - Boolean expression tested against each element
* `IN` - Search immediate content only
* `WITHIN` - Recursively search nested arrays/objects
**Search Operators:**
* `ANY` - Returns `true` if at least one element satisfies the condition (short-circuits on first match)
* `EVERY` - Returns `true` if all elements satisfy the condition (empty arrays/objects return `true`)
* `ANY AND EVERY` - Like `EVERY` but returns `false` for empty arrays/objects
### Examples
**Using ANY:**
```sql DQL theme={null}
-- Find documents where any array element is less than zero after the first position
SELECT * FROM test
WHERE ANY n:v IN test.array_field SATISFIES n > 0 AND v < 0 END
-- Find documents where any nested field is NULL
SELECT * FROM test
WHERE ANY v WITHIN test.details SATISFIES v IS NULL END
-- Find orders where any item was modified after the order date
SELECT * FROM orders o
WHERE ANY v IN o.items SATISFIES v.modified > o.order_date END
-- Find products with any tag matching "electronics"
SELECT * FROM products
WHERE ANY tag IN tags SATISFIES tag = 'electronics' END
```
**Using EVERY:**
```sql DQL theme={null}
-- Find documents where all prices are above 100
SELECT * FROM products
WHERE EVERY price IN prices SATISFIES price > 100 END
-- Verify all items in an order are available
SELECT * FROM orders
WHERE EVERY item IN items SATISFIES item.status = 'available' END
-- Check that all nested values are positive
SELECT * FROM data
WHERE EVERY v WITHIN measurements SATISFIES v > 0 END
```
**Using ANY AND EVERY:**
```sql DQL theme={null}
-- Find documents with at least one item, and all items are approved
-- (Empty items array would fail this check)
SELECT * FROM orders
WHERE ANY AND EVERY item IN items SATISFIES item.approved = true END
-- Ensure array is non-empty and all values are within range
SELECT * FROM data
WHERE ANY AND EVERY v IN values SATISFIES v BETWEEN 0 AND 100 END
```
### IN vs WITHIN
The difference between `IN` and `WITHIN` is crucial:
**IN - Direct Search:**
```sql DQL theme={null}
-- Searches only the immediate array elements
ANY v IN [1, 2, [3, 4]] SATISFIES v = 3 END
-- Result: false (3 is nested inside [3,4])
```
**WITHIN - Recursive Search:**
```sql DQL theme={null}
-- Searches recursively through all nested levels
ANY v WITHIN [1, 2, [3, 4]] SATISFIES v = 3 END
-- Result: true (finds 3 in nested array)
-- With objects
ANY v WITHIN {"a": {"b": 1}} SATISFIES v = 1 END
-- Result: true (recursively finds 1 in nested object)
```
**WITHIN with IS MISSING:** Be careful when using `WITHIN` with `IS MISSING` predicates:
```sql DQL theme={null}
ANY x WITHIN [{'a':1}] SATISFIES x.a IS MISSING END
-- Result: true
-- This is true because WITHIN:
-- 1. First binds {'a':1} to x, evaluates x.a IS MISSING (false)
-- 2. Then binds 1 (nested value) to x, evaluates x.a IS MISSING (true - 1 has no 'a' property)
```
Use `IN` instead of `WITHIN` when checking for missing fields to avoid unexpected recursion.
### Common Patterns
**Filtering by Array Contents:**
```sql DQL theme={null}
SELECT * FROM products
WHERE ANY category IN categories SATISFIES category LIKE 'electronics%' END
```
**Validating All Elements:**
```sql DQL theme={null}
SELECT * FROM shipments
WHERE EVERY package IN packages SATISFIES package.weight < 50 END
```
**Non-Empty Array with All Valid:**
```sql DQL theme={null}
SELECT * FROM orders
WHERE ANY AND EVERY item IN items SATISFIES item.quantity > 0 END
```
**Searching Nested Structures:**
```sql DQL theme={null}
SELECT * FROM documents
WHERE ANY v WITHIN metadata SATISFIES v = 'confidential' END
```
## Comparison Operators
The comparison operators fall into one of two sub-categories:
* Missing value comparisons
* Regular value comparisons
DQL has two ways of representing missing information in an object:
* The presence of the field with a `NULL` for its value (as in SQL)
* The absence of the field (which JSON permits)
The following table provides operators for comparing if a given value is equal to any of the values in a list:
`NULL` and `UNKNOWN` are synonym keywords and provide the same behavior.
If a field doesn't exist in a document, any predicate using that field evaluates to MISSING (which is treated as false in WHERE clauses), except when using the IS MISSING or IS NOT MISSING operators which explicitly check for field existence.
| **Operator** | **Purpose** | **Example** |
| :------------- | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :-------------------------------- |
| IS NULL | Returns true if the value is NULL, otherwise returns false | `WHERE field_name IS NULL` |
| IS NOT NULL | Returns true if the value is not NULL, otherwise returns false | `WHERE field_name IS NOT NULL` |
| IS MISSING | Returns true if the field is missing in a given document, otherwise returns false | `WHERE field_name IS MISSING` |
| IS NOT MISSING | Returns true if the field is not missing in a given document, otherwise returns false | `WHERE field_name IS NOT MISSING` |
| IS UNKNOWN | Returns true if the value is NULL, otherwise returns false | `WHERE field_name IS UNKNOWN` |
| IS NOT UNKNOWN | Returns true if the value is not NULL, otherwise returns false | `WHERE field_name IS NOT UNKNOWN` |
| = | Equality test | `WHERE field_name = 100` |
| == | Equality test | `WHERE field_name == 100` |
| != | Inequality test | `WHERE field_name != 100` |
| \<> | Inequality test | `WHERE field_name <> 100` |
| \< | Less than | `WHERE field_name < 100` |
| > | Greater than | `WHERE field_name > 100` |
| \<= | Less than or equal to | `WHERE field_name <= 100` |
| >= | Greater than or equal to | `WHERE field_name >= 100` |
| BETWEEN | Inclusive range test. `expr BETWEEN low AND high` is equivalent to `expr >= low AND expr <= high`. Note that the order matters: `BETWEEN 1 AND 10` and `BETWEEN 10 AND 1` are not equivalent. Can be negated with `NOT`: `expr NOT BETWEEN low AND high`. | `WHERE price BETWEEN 10 AND 100` |
### **Comparison Operations with NULL**
In DQL, `NULL` represents missing or unknown data. It's not a value in the way that `1` or `'text'` are values. Any comparison operation that includes `NULL` will result in `NULL`.
| **Equation** | **Result** |
| :------------- | :--------- |
| `NULL = NULL` | `NULL` |
| `NULL <> NULL` | `NULL` |
| `1 = NULL` | `NULL` |
| `1 > NULL` | `NULL` |
## Conditional Operators
Conditional Operators allow you to express conditional logic and handle NULL/MISSING values within your DQL queries.
| **Operator** | **Purpose** | **Example** |
| :--------------------------------------------------------------- | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :------------------------------------------------------------------------------------------------ |
| coalesce(v1, v2, ...) | Returns the first non-null, non-missing value or null if none exists. Synonym for `ifmissingornull` | `WHERE coalesce(field_name_1, field_name_2, field_name_3) = 'foo'` |
| decode(input, comp1, res1, \[comp2, res2...] \[, default value]) | Compares the input against each `comp` parameter, and if they match type and value, returns the corresponding `res` value. A NULL input matches a NULL `res`. If no matches are found, the `default value` is returned (which defaults to `NULL`). A MISSING input returns MISSING regardless | `WHERE decode(field_name, 'foo', 'I found foo', 'bar', 'I found bar', 'I found nothing') = 'foo'` |
| nvl(val, res1\[, res2]) | If three arguments: returns `res1` if `val` is not null and `res2` otherwise. If two arguments: returns `val` if not null and `res1` otherwise | `WHERE nvl(field_name, 0) > 10` |
| isnull(v) | Returns true if `v` is null, otherwise false | `WHERE isnull(field_name)` |
| ismissing(v) | Returns true if `v` is missing, otherwise false | `WHERE ismissing(field_name)` |
| ismissingornull(v) | Returns true if `v` is missing or null, otherwise false | `WHERE ismissingornull(field_name)` |
| ifnull(v, v \[, ...]) | Returns the first `v` to not be null, otherwise null | `WHERE ifnull(field1, field2, 'default') = 'default'` |
| ifmissing(v, v \[, ...]) | Returns the first `v` to not be missing, otherwise null | `WHERE ifmissing(field1, field2, 'default') = 'default'` |
| ifmissingornull(v, v \[, ...]) | Returns the first `v` to not be missing or null. Synonym for `coalesce` | `WHERE ifmissingornull(field1, field2, 'default') = 'default'` |
| nullif(v1, v2) | Returns null if `v1` equals `v2`, otherwise `v1` or null if either is null or missing | `WHERE nullif(field_name, 'ignore') IS NOT NULL` |
| missingif(v1, v2) | Returns missing if `v1` equals `v2`, otherwise `v1` or missing if either is null or missing | `WHERE missingif(field_name, 'ignore') IS NOT MISSING` |
## CASE Expressions
CASE expressions provide conditional logic similar to if-then-else constructs. DQL supports both simple and searched CASE expressions.
### Simple CASE
Compares an expression against multiple values:
```sql DQL theme={null}
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
[ELSE default_result]
END
```
![Simple CASE Syntax Diagram]()
When the result of `expression` matches a `value`, the corresponding `result` is returned. If there is no match and there is no `ELSE` clause, the result is `NULL`. The first branch with a matching value is used.
**Example:**
```sql DQL theme={null}
SELECT
color,
CASE color
WHEN 'blue' THEN 'ocean'
WHEN 'red' THEN 'fire'
WHEN 'green' THEN 'forest'
ELSE 'unknown'
END AS theme
FROM cars
```
### Searched CASE
Evaluates multiple conditions:
```sql DQL theme={null}
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
[ELSE default_result]
END
```
![Searched CASE Syntax Diagram]()
When a `condition` evaluates to true, the corresponding `result` is returned. If there is no match and there is no `ELSE` clause, the result is `NULL`. The first branch with a true condition is used.
**Example:**
```sql DQL theme={null}
SELECT
year,
mileage,
CASE
WHEN year > 2020 AND mileage < 20000 THEN 'new'
WHEN year > 2015 AND mileage < 80000 THEN 'good'
WHEN year > 2010 THEN 'fair'
ELSE 'old'
END AS condition
FROM cars
```
## Logical Operators
Logical operators perform logical `NOT`, `AND`, and `OR` operations over Boolean values (`TRUE` and `FALSE`), plus `NULL` and `MISSING`.
| **Operator** | **Purpose** | **Example** |
| :----------- | :------------------------------------------------------------------------- | :----------------------------------------- |
| NOT | Returns true if the following condition is false, otherwise returns false. | `WHERE NOT field_name = true` |
| AND | Returns true if both branches are true, otherwise returns false | `WHERE field_1 = true AND field_2 = false` |
| OR | Returns true if one branch is true; otherwise, returns false | `WHERE field_1 = true OR field_2 = false` |
### NOT **Truth Table**
| **Value** | **Result** |
| :-------- | :--------- |
| True | False |
| False | True |
| NULL | NULL |
### AND **Truth Table**
| | **True** | **NULL** | **False** |
| :-------- | :------- | :------- | :-------- |
| **True** | True | NULL | False |
| **NULL** | NULL | NULL | False |
| **False** | False | False | False |
### OR **Truth Table**
| | **True** | **NULL** | **False** |
| :-------- | :------- | :------- | :-------- |
| **True** | True | True | True |
| **NULL** | True | NULL | NULL |
| **False** | True | NULL | False |
## Aggregate Functions
DQL provides aggregate functions that operate on groups of documents to produce summary values. For complete documentation on using aggregates with GROUP BY, see [SELECT - Aggregate Functions](/dql/select#aggregate-functions).
| **Function** | **Purpose** | **Example** |
| :----------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------- | :------------------------------- |
| COUNT(\[DISTINCT] expr) | Counts items for which `expr` does not evaluate to NULL, MISSING, or FALSE. Use `COUNT(*)` to count all documents | `SELECT COUNT(*) FROM cars` |
| SUM(\[DISTINCT] expr) | Sums numeric values. Non-numeric values are silently ignored | `SELECT SUM(price) FROM cars` |
| AVG(\[DISTINCT] expr) | Returns the average of numeric values. Non-numeric values are silently ignored | `SELECT AVG(mileage) FROM cars` |
| MIN(expr) | Returns the minimum value. Values are compared according to Ditto type ordering rules | `SELECT MIN(year) FROM cars` |
| MAX(expr) | Returns the maximum value. Values are compared according to Ditto type ordering rules | `SELECT MAX(year) FROM cars` |
| MID(\[DISTINCT] expr) | Returns the arithmetic midpoint between `MIN(expr)` and `MAX(expr)`, i.e. `(min + max) / 2`. Not the same as `MEDIAN`. Non-numeric values are silently ignored | `SELECT MID(price) FROM cars` |
| MEDIAN(\[DISTINCT] expr) | Returns the positional median value (the middle value when sorted). Non-numeric values are silently ignored | `SELECT MEDIAN(price) FROM cars` |
When `DISTINCT` is specified, only distinct values are considered in the calculations. This requires maintaining a record of all distinct values encountered, which increases memory requirements for large result sets.
Aggregate functions form a "dam" in the execution pipeline - all documents must be processed before results can be returned. This is different from non-aggregate queries which can stream results.
## Miscellaneous Functions
| **Function** | **Purpose** | **Example** |
| :-------------- | :--------------------------------------------------------------------------------------------------------------------------------------- | :--------------------------------------- |
| request\_info() | Returns an object with metadata about the active request: `app_id`, `request_id`, and `start_time`.
Available in SDK 4.12 onwards. | `SELECT request_info() FROM system:dual` |
# PROFILE
Source: https://docs.ditto.live/dql/profile
The PROFILE statement helps you analyze query performance by providing an annotated query plan with execution statistics.
The `PROFILE` statement executes a `SELECT` query and appends detailed profiling information to the result set, including the query execution plan annotated with document counts and timing data.
Profiling information is stored in the virtual collection `system:completed_requests`. You can also query profiling and request history data directly using the virtual collections `system:active_requests` and `system:request_history`. See [Virtual Collections](/dql/virtual-collections#systemactive_requests--systemrequest_history) for more details.
## Syntax
```sql DQL theme={null}
PROFILE select_statement
```
![PROFILE Syntax Diagram]()
## How It Works
When you execute a `PROFILE` statement:
1. The query runs normally and returns all result documents
2. An additional result is appended containing the `system:completed_requests` cache entry
3. This entry includes the query plan with profiling information (document counts, timing)
## Using PROFILE
```sql DQL theme={null}
PROFILE SELECT * FROM cars WHERE color = 'blue' AND year > 2020
```
This executes the query and returns:
* All matching documents
* A final result containing the annotated query plan with profiling data
## Profiling Information
The profiling result includes:
* **Document counts**: How many documents were processed at each step of the plan
* **Timing information**: How long each operation took
* **Query plan structure**: The full execution plan showing scans, filters, projections, etc.
* **Index usage**: Which indices (if any) were used
## Example Output Structure
The profiling information is returned as the final result in the result set and typically includes:
```json theme={null}
{
"plan": {
"#operator": "project",
"document_count": 42,
"elapsed_time_ms": 5.2,
"children": [
{
"#operator": "filter",
"document_count": 42,
"elapsed_time_ms": 2.1,
"children": [
{
"#operator": "indexScan",
"index": "ix_color_year",
"document_count": 50,
"elapsed_time_ms": 1.5
}
]
}
]
}
}
```
## Using Profiling Data
Profiling data helps you:
1. **Identify bottlenecks**: See which operations are taking the most time
2. **Verify index usage**: Confirm your query is using the expected indices
3. **Understand query execution**: See how the planner executes your query
4. **Optimize performance**: Make informed decisions about index creation and query structure
## Code Examples
```swift Swift theme={null}
let results = await ditto.store.execute(
query: "PROFILE SELECT * FROM cars WHERE color = 'blue' AND year > 2020"
)
// The last item in results will contain the profiling information
for (index, item) in results.enumerated() {
if index == results.count - 1 {
// This is the profiling data
print("Profiling info: \(item)")
} else {
// These are regular result documents
print("Result: \(item)")
}
}
```
```kotlin Kotlin theme={null}
val results = ditto.store.execute(
"PROFILE SELECT * FROM cars WHERE color = 'blue' AND year > 2020"
)
// The last item in results will contain the profiling information
results.forEachIndexed { index, item ->
if (index == results.size - 1) {
// This is the profiling data
println("Profiling info: $item")
} else {
// These are regular result documents
println("Result: $item")
}
}
```
```javascript JS theme={null}
const results = await ditto.store.execute(
"PROFILE SELECT * FROM cars WHERE color = 'blue' AND year > 2020"
);
// The last item in results will contain the profiling information
results.forEach((item, index) => {
if (index === results.length - 1) {
// This is the profiling data
console.log("Profiling info:", item);
} else {
// These are regular result documents
console.log("Result:", item);
}
});
```
```java Java theme={null}
var results = ditto.store.execute(
"PROFILE SELECT * FROM cars WHERE color = 'blue' AND year > 2020"
);
// The last item in results will contain the profiling information
for (int i = 0; i < results.size(); i++) {
if (i == results.size() - 1) {
// This is the profiling data
System.out.println("Profiling info: " + results.get(i));
} else {
// These are regular result documents
System.out.println("Result: " + results.get(i));
}
}
```
```csharp C# theme={null}
var results = await ditto.Store.ExecuteAsync(
"PROFILE SELECT * FROM cars WHERE color = 'blue' AND year > 2020"
);
// The last item in results will contain the profiling information
for (int i = 0; i < results.Count; i++) {
if (i == results.Count - 1) {
// This is the profiling data
Console.WriteLine($"Profiling info: {results[i]}");
} else {
// These are regular result documents
Console.WriteLine($"Result: {results[i]}");
}
}
```
```cpp C++ theme={null}
auto results = ditto.get_store().execute(
"PROFILE SELECT * FROM cars WHERE color = 'blue' AND year > 2020"
).get();
// The last item in results will contain the profiling information
for (size_t i = 0; i < results.size(); i++) {
if (i == results.size() - 1) {
// This is the profiling data
std::cout << "Profiling info: " << results[i] << std::endl;
} else {
// These are regular result documents
std::cout << "Result: " << results[i] << std::endl;
}
}
```
```rust Rust theme={null}
let results = ditto.store()
.execute_v2((
"PROFILE SELECT * FROM cars WHERE color = 'blue' AND year > 2020",
serde_json::json!({})
))
.await?;
// The last item in results will contain the profiling information
for (index, item) in results.iter().enumerate() {
if index == results.len() - 1 {
// This is the profiling data
println!("Profiling info: {:?}", item);
} else {
// These are regular result documents
println!("Result: {:?}", item);
}
}
```
```dart Dart theme={null}
final results = await ditto.store.execute(
"PROFILE SELECT * FROM cars WHERE color = 'blue' AND year > 2020"
);
// The last item in results will contain the profiling information
for (var i = 0; i < results.length; i++) {
if (i == results.length - 1) {
// This is the profiling data
print("Profiling info: ${results[i]}");
} else {
// These are regular result documents
print("Result: ${results[i]}");
}
}
```
## PROFILE vs #profile Directive
The `PROFILE` statement is equivalent to using the `#profile` directive:
```sql DQL theme={null}
-- These are equivalent
PROFILE SELECT * FROM cars WHERE color = 'blue'
/*+ {"#profile": true} */
SELECT * FROM cars WHERE color = 'blue'
```
## Limitations
* `PROFILE` only supports `SELECT` statements
* The profiling data is appended as the last result, so you need to handle it separately from your query results
* Profiling may add slight overhead to query execution
## See Also
* [EXPLAIN](/dql/explain) - View query execution plans without running the query
* [Directives](/dql/directives) - Control query planner behavior
* [Access Paths](/dql/access-paths) - Understanding query execution strategies
* [Indexing](/dql/indexing) - Creating indices to improve query performance
# Query Syntax (Legacy)
Source: https://docs.ditto.live/dql/query-syntax-legacy
At a high-level, queries operate on collections rather than individual documents. Filter, search, and retrieve specific information based on various criteria using Boolean operators, equal and unequal operators, comparison operators, and match operators.
This article includes an overview of operators and path navigations for building advanced queries in your app, along with real-world examples.
For related information, see the *Platform Manual:*
## Boolean Operators
When a field property is a boolean data type, use explicit true-false values.
Enclose groups of logical operations with parentheses to ensure Ditto interprets your desired logic correctly.
For example, to find documents with an `"isDeleted"` boolean property set to `"true"`:
```sql Query theme={null}
"isDeleted == true"
```
To find documents with an `"isDeleted"` boolean property set to `"false"`:
```sql Query theme={null}
"isDeleted == false"
```
## Equal (==) and Inequality (!=) Operators
To find documents that match a given key-value pair, use the equal operator. Where you specify the key and the number or string value you want to match separated by `==`.
For example, to find documents that have a title equal to `"Harry Potter"`:
```sql Query theme={null}
"title == 'Harry Potter'"
```
If, instead of finding documents that match, you want to find documents that do *not* match a given key-value pair, use the inequality operator. Where you specify the key and the value that you don’t want to match separated by `!=`.
For example, to find documents that are not of the title "Lord of the Rings":
```sql Query theme={null}
"title != 'Lord of the Rings'"
```
## Comparison Operators: (>=)(>)(\<=)(\<)
Compare values in documents, set conditions, retrieve specific documents that meet your criteria, and make logical evaluations using the following operators in your query statements.
Ditto supports parsing ISO-8601 date strings, so you can use ISO-8601 formatted date strings in queries for comparison operations. For more information, see Using ISO-8601 for Date Strings.
For example:
To find documents where `age` field property is less than or equal to the value of `18`:
```sql Query theme={null}
"age <= 18"
```
To find documents where the `age` field property is less than the value of `18`:
```sql Query theme={null}
"age < 18"
```
To find documents where the `age` field property is greater than or equal to the value of `18`:
```sql Query theme={null}
"age >= 18"
```
To find documents where the `age` field property is greater than the value of `18`:
```sql Query theme={null}
"age > 18"
```
## Compound Operators
Perform complex operations in a single executable by using *compound operators*. A compound operator\* \*is a combination of two or more operators in a single executable:
| **SQL** | **Ditto** |
| ------------- | ----------- |
| `AND` | `&&` |
| `OR` | `\|\|` |
| `NOT` | `!` |
| `contains( )` | contains( ) |
### Logical AND Predicate Statements: (&&)
Similar to SQL `AND` statements, use `&&` for a condition that evaluates to true only when *all* of its conditions are set to `true`.
For example, to find documents that have a `theme` field property equal to `"Dark"` and a name field property equal to `"Light"`:
```sql Query theme={null}
"theme == 'Dark' && name == 'Light'
```
### Logical OR Statements: (||)
Similar to SQL `OR` statements, use `||` for a logical *or* predicate statement.
For example, to find documents that are `"Tom"` or `"Arthur"`:
```sql Query theme={null}
"name == 'Tom' || name == 'Arthur'"
```
### Logical NOT Statements: (!)
Similar to SQL `NOT` statements, use `!` for logical *not* predicate statements:
For example, find documents that are neither "Hamilton" nor "Morten":
```sql theme={null}
"name != 'Tom'"
```
### String Operations
Use `starts_with(property, test)` to test if a field property with a string value starts with a test string.
For example, to find documents with a `title` field property that *begins* with `"Lord"`:
```sql Query theme={null}
"starts_with(title, 'Lord')"
```
Use `ends_with(property, test)` to test if a field property with a string value *ends* with a test string.
For example, to find documents with a `title` field property that ends with `"Rings"`:
```sql Query theme={null}
"ends_with(title, 'Rings')"
```
Use `regex(property, test)` to see if a field property with a string value passes a regular expression. For more information, see the official Mozilla Developer Network Docs (MDN) > Regular Expressions.
For example, to find documents containing only upper and lowercase letters, numbers, and underscores:
```sql Query theme={null}
"regex(title, '^([A-Za-z]|[0-9]|_)+$')"
// A title property of "abc129_24A" will pass
// A title property of "abc129_24A 3" will not pass
```
## NULL Values
Use `null` to check for the existence of a value of a given field.
For example, to find documents with a `color` field property that has no value:
```sql Query theme={null}
"color == null"
```
## Array Operators
When handling collections of data that different peers may make concurrent updates to, first consider using an embedded `map` structure. If necessary, use an `array`.
The `array` type in Ditto is a CRDT and behaves differently than the primitive `array` type. For more information, see the *Platform Manual* [Data Types](/dql/types-and-definitions#data-types).
Avoid using `arrays` in Ditto.
Due to potential merge conflicts when offline peers reconnect to the mesh and attempt to sync their updates, especially when multiple peers make concurrent updates to the same item within the `array`.
| **Operator** | **Operation** |
| ------------------------ | ------------------------------- |
| `contains(array, value)` | Checks for value in the `array` |
```sql Query theme={null}
"contains(['blue', 'green'], color]"
```
## Date and Time Formats
When parsing date and time strings, use the ISO-8601 standard format, as follows.
For more information, see *Platform Manual* Using ISO-8601 for Date Strings.
```sql Query theme={null}
"created_at >= '2022-04-29T00:55:31.859Z'"
```
## Field Path Navigation
If fields consist of alphanumeric characters or include underscores, use the following bracket notation to navigate document properties:
```sql Query theme={null}
"work['street-line'] == '678 Johnson Street'"
```
### Valid Path Definitions
`a[0][1]["_123"]`
`a["0"]["1"]`
`[”a”][”0”][”1”]`
### Invalid Path Definitions
`$foo` (\$ isn't a valid character anywhere in the unquoted string)
`a[1st]` (1st isn't a valid unquoted string for field access, as the first character is a number)
`b[2nd]` (same reason as above)
`b[$foo]` (\$ isn't a valid character anywhere in the unquoted string)
`b[foo$]` (same reason as above)
`a["foo"]b` (missing \[""] around b)
***Instead use...***
`["$foo"]`
`a["1st"]`
`b["2nd"]`
`b["$foo"]`
`b["foo$"]`
`b["foo"]["b"]`
# Replacing Live Query Events with Store Observers
Source: https://docs.ditto.live/dql/replacing-live-queries
Learn how to migrate from legacy live query APIs to store observers with the new Differ
With Ditto SDK 4.11+, the new diffing APIs can be used with store observers to produce diffs between query results in consecutive invocations. This guide explains how to use the new diffing APIs to replace usage of the legacy live query APIs.
## Overview
Live queries provide their callback with an `event` parameter that contains a diff of the current live query event against the previous one—a summary of which documents in the result have been:
* **Inserted**: Indexes of new items
* **Updated**: Indexes of items that existed previously, but have changed
* **Deleted**: Indexes of items that were removed
* **Moved**: Pairs of indexes showing items that changed position
Store observers don't have a built-in feature that provides a diff, as generating it is computationally expensive. However, you can use the new `Differ` class to compute one when needed.
## Key Differences
### Legacy Live Query API
* Provides automatic diffing through the `event` parameter
* Maintains `event.oldDocuments` for deleted items (uses memory)
* Includes `event.isInitial` flag for the first callback
* Considers metadata changes as updates
### Store Observer with Differ
* Requires manual diffing using the `Differ` class
* You must maintain previous results yourself
* No built-in initial event detection
* Ignores metadata-only changes when determining updates
## Migration Example
### Before: Using Legacy Live Query API
```javascript JS theme={null}
const appStateLegacy = {
inserted: null,
updated: null,
deleted: null
};
ditto.store
.collection('cars')
.findAll()
.observeLocal((documents, event) => {
if (event.isInitial) {
// Store all initial document IDs in app state
appStateLegacy.inserted = documents.map(doc => doc.value._id);
console.log("Initial live query event", appStateLegacy.inserted);
} else {
appStateLegacy.inserted = event.insertions?.map(
(index) => documents[index].value._id
);
appStateLegacy.updated = event.updates?.map(
(index) => documents[index].value._id
);
appStateLegacy.deleted = event.deletions?.map(
// accessing `event.oldDocuments`
(index) => event.oldDocuments[index].value._id
);
console.log("Live query event: ", JSON.stringify(appStateLegacy, null, 2));
}
});
```
### After: Using Store Observer with Differ
**Important Memory Management**: QueryResults and QueryResultItems should be treated like database cursors. Always extract the data you need and then close/dematerialize them immediately. Never store QueryResultItems directly between observer emissions.
```swift Swift theme={null}
let differ = DittoDiffer()
var previousDocumentIds: [String] = [] // Store only extracted IDs
let observer = ditto.store.registerObserver(
query: "SELECT * FROM cars") { queryResult in
let diff = differ.diff(queryResult.items)
// Extract current document IDs and dematerialize items
let currentDocumentIds = queryResult.items.map { item in
let id = item.value["_id"] as? String ?? "unknown"
item.dematerialize() // Release memory after extracting data
return id
}
// Handle deletions using stored IDs from previous emission
for index in diff.deletions {
let deletedId = previousDocumentIds[index]
print("Deleted car with ID: \(deletedId)")
}
// Handle insertions using current IDs
for index in diff.insertions {
let insertedId = currentDocumentIds[index]
print("Inserted car with ID: \(insertedId)")
}
// Handle updates using current IDs
for index in diff.updates {
let updatedId = currentDocumentIds[index]
print("Updated car with ID: \(updatedId)")
}
// Store only the document IDs for next callback - no live references!
previousDocumentIds = currentDocumentIds
}
```
```kotlin Kotlin theme={null}
// In v5 the differ is internal — the observer owns it. Use the (result, diff)
// overload of registerObserver. Closing the observer frees the differ. The
// suspend handler auto-closes the DittoQueryResult; never store or return
// result.items.
var previousDocumentIds: List = emptyList()
val observer = ditto.store.registerObserver(
"SELECT * FROM cars",
) { result, diff ->
// Extract current document IDs and dematerialize items
val currentDocumentIds = result.items.map { item ->
val id = item.value["_id"].stringOrNull ?: "unknown"
item.dematerialize() // Release memory after extracting data
id
}
// Handle deletions using stored IDs from previous emission
diff.deletions.forEach { index ->
previousDocumentIds.getOrNull(index)?.let { deletedId ->
println("Deleted car with ID: $deletedId")
}
}
// Handle insertions using current IDs
diff.insertions.forEach { index ->
currentDocumentIds.getOrNull(index)?.let { insertedId ->
println("Inserted car with ID: $insertedId")
}
}
// Handle updates using current IDs
diff.updates.forEach { index ->
currentDocumentIds.getOrNull(index)?.let { updatedId ->
println("Updated car with ID: $updatedId")
}
}
// Store only the document IDs for next callback — no live references!
previousDocumentIds = currentDocumentIds
}
```
```javascript JS theme={null}
const differ = new Differ();
let previousDocumentIds = []; // Store only extracted IDs
const changeHandler = (queryResult) => {
const diff = differ.diff(queryResult.items);
// Extract current document IDs and dematerialize items
const currentDocumentIds = queryResult.items.map(item => {
const id = item.value._id || 'unknown';
item.dematerialize(); // Release memory after extracting data
return id;
});
// Handle deletions using stored IDs from previous emission
diff.deletions.forEach(index => {
const deletedId = previousDocumentIds[index];
console.log('Deleted car with ID:', deletedId);
});
// Handle insertions using current IDs
diff.insertions.forEach(index => {
const insertedId = currentDocumentIds[index];
console.log('Inserted car with ID:', insertedId);
});
// Handle updates using current IDs
diff.updates.forEach(index => {
const updatedId = currentDocumentIds[index];
console.log('Updated car with ID:', updatedId);
});
// Store only the document IDs for next callback - no live references!
previousDocumentIds = currentDocumentIds;
}
const observer = ditto.store.registerObserver(
"SELECT * FROM cars",
changeHandler);
```
```java Java theme={null}
DittoDiffer differ = new DittoDiffer();
List previousDocumentIds = new ArrayList<>(); // Store only extracted IDs
DittoStoreObserver observer = ditto.store.registerObserver(
"SELECT * FROM cars",
result -> {
try (result) { // Auto-closes result when done
DittoDiff diff = differ.diff(result.items);
// Extract current document IDs and dematerialize items
List currentDocumentIds = new ArrayList<>();
for (DittoQueryResultItem item : result.items) {
String id = item.getValue().get("_id") != null ?
item.getValue().get("_id").toString() : "unknown";
currentDocumentIds.add(id);
item.dematerialize(); // Release memory after extracting data
}
// Handle deletions using stored IDs from previous emission
for (int index : diff.getDeletions()) {
if (index < previousDocumentIds.size()) {
String deletedId = previousDocumentIds.get(index);
System.out.println("Deleted car with ID: " + deletedId);
}
}
// Handle insertions using current IDs
for (int index : diff.getInsertions()) {
String insertedId = currentDocumentIds.get(index);
System.out.println("Inserted car with ID: " + insertedId);
}
// Handle updates using current IDs
for (int index : diff.getUpdates()) {
String updatedId = currentDocumentIds.get(index);
System.out.println("Updated car with ID: " + updatedId);
}
// Store only the document IDs for next callback - no live references!
previousDocumentIds = currentDocumentIds;
} catch (Exception e) {
// Handle any errors
e.printStackTrace();
}
}
);
```
```csharp C# theme={null}
var differ = new DittoDiffer();
var previousDocumentIds = new List(); // Store only extracted IDs
var observer = ditto.Store.RegisterObserver(
"SELECT * FROM cars",
(result) =>
{
using (result) // Disposes result when done
{
var diff = differ.Diff(result.Items);
// Extract current document IDs and dematerialize items
var currentDocumentIds = result.Items.Select(item =>
{
var id = item.Value.ContainsKey("_id") ?
item.Value["_id"].ToString() : "unknown";
item.Dematerialize(); // Release memory after extracting data
return id;
}).ToList();
// Handle deletions using stored IDs from previous emission
foreach (var index in diff.Deletions)
{
if (index < previousDocumentIds.Count)
{
var deletedId = previousDocumentIds[index];
Console.WriteLine($"Deleted car with ID: {deletedId}");
}
}
// Handle insertions using current IDs
foreach (var index in diff.Insertions)
{
var insertedId = currentDocumentIds[index];
Console.WriteLine($"Inserted car with ID: {insertedId}");
}
// Handle updates using current IDs
foreach (var index in diff.Updates)
{
var updatedId = currentDocumentIds[index];
Console.WriteLine($"Updated car with ID: {updatedId}");
}
// Store only the document IDs for next callback - no live references!
previousDocumentIds = currentDocumentIds;
}
});
```
```cpp C++ theme={null}
DittoDiffer differ;
std::vector previousDocumentIds; // Store only extracted IDs
auto observer = ditto.get_store().register_observer(
"SELECT * FROM cars",
[&](QueryResult result) {
DittoDiff diff = differ.diff(result.items());
// Extract current document IDs and dematerialize items
std::vector currentDocumentIds;
for (auto& item : result.items()) {
auto value = item.value();
std::string id = value.contains("_id") ?
value["_id"].get() : "unknown";
currentDocumentIds.push_back(id);
item.dematerialize(); // Release memory after extracting data
}
// Handle deletions using stored IDs from previous emission
for (size_t index : diff.deletions()) {
if (index < previousDocumentIds.size()) {
std::string deletedId = previousDocumentIds[index];
std::cout << "Deleted car with ID: " << deletedId << std::endl;
}
}
// Handle insertions using current IDs
for (size_t index : diff.insertions()) {
std::string insertedId = currentDocumentIds[index];
std::cout << "Inserted car with ID: " << insertedId << std::endl;
}
// Handle updates using current IDs
for (size_t index : diff.updates()) {
std::string updatedId = currentDocumentIds[index];
std::cout << "Updated car with ID: " << updatedId << std::endl;
}
// Store only the document IDs for next callback - no live references!
previousDocumentIds = currentDocumentIds;
// C++ QueryResult uses RAII - destructor handles cleanup
});
```
```rust Rust theme={null}
let mut differ = DittoDiffer::new();
let mut previous_document_ids: Vec = Vec::new(); // Store only extracted IDs
let observer = ditto.store().register_observer_v2(
"SELECT * FROM cars",
move |result: QueryResult| {
let diff = differ.diff(result.items());
// Extract current document IDs and dematerialize items
let mut current_document_ids: Vec = Vec::new();
for item in result.items() {
let value = item.value();
let id = value.get("_id")
.and_then(|v| v.as_str())
.unwrap_or("unknown")
.to_string();
current_document_ids.push(id);
item.dematerialize(); // Release memory after extracting data
}
// Handle deletions using stored IDs from previous emission
for &index in &diff.deletions {
if let Some(deleted_id) = previous_document_ids.get(index) {
println!("Deleted car with ID: {}", deleted_id);
}
}
// Handle insertions using current IDs
for &index in &diff.insertions {
let inserted_id = ¤t_document_ids[index];
println!("Inserted car with ID: {}", inserted_id);
}
// Handle updates using current IDs
for &index in &diff.updates {
let updated_id = ¤t_document_ids[index];
println!("Updated car with ID: {}", updated_id);
}
// Store only the document IDs for next callback - no live references!
previous_document_ids = current_document_ids;
// Rust QueryResult uses RAII - Drop trait handles cleanup
});
```
```dart Dart theme={null}
final differ = DittoDiffer();
final previousDocumentIds = []; // Store only extracted IDs
final observer = ditto.store.registerObserver(
"SELECT * FROM cars",
onChange: (queryResult) {
final diff = differ.diff(queryResult.items);
// Extract current document IDs
// Note: Flutter SDK doesn't have explicit dematerialize
final currentDocumentIds = queryResult.items.map((item) {
final id = item.value['_id'] as String? ?? 'unknown';
return id;
}).toList();
// Handle deletions using stored IDs from previous emission
for (final index in diff.deletions) {
if (index < previousDocumentIds.length) {
final deletedId = previousDocumentIds[index];
print('Deleted car with ID: $deletedId');
}
}
// Handle insertions using current IDs
for (final index in diff.insertions) {
final insertedId = currentDocumentIds[index];
print('Inserted car with ID: $insertedId');
}
// Handle updates using current IDs
for (final index in diff.updates) {
final updatedId = currentDocumentIds[index];
print('Updated car with ID: $updatedId');
}
// Store only the document IDs for next callback - no live references!
previousDocumentIds = currentDocumentIds;
// Flutter handles cleanup automatically
}
);
```
## Implementation Details
### Handling Deleted and Moved Documents
With store observers, results from the previous event are no longer provided directly. You need to:
1. Store the current results at the end of each callback:
```javascript theme={null}
previousItems = result.items;
```
2. Use the indexes provided in `diff.deletions` or `diff.moves` to access deleted and moved documents from your stored `previousItems`.
### Detecting the Initial Event
The legacy API's `event.isInitial` flag isn't available with the Differ. To detect the first callback:
```javascript theme={null}
if (!previousItems) {
// This is the initial callback
// All items will appear as insertions in the diff
}
```
In the first callback, all items passed to `differ.diff` show up as insertions because the differ is initially empty.
### Metadata Changes
The `Differ` ignores metadata changes when determining updates. For example:
* Changing a document field and then setting it back to its original value will not be considered an update
* This differs from the legacy API which would count any change as an update
## Performance Considerations
Generating diffs may take significant time for very large result sets. Consider whether you truly need diff information for your use case.
## Interactive Example
Try the [interactive example on CodePen](https://codepen.io/pvditto/pen/WbbVWOr) to see both approaches in action. Open your browser console to observe the results.
## Related Documentation
* [Legacy Query Syntax](/dql/query-syntax-legacy)
* [Legacy-to-DQL Adoption Guide](/dql/legacy-to-dql-adoption)
# RETURNING
Source: https://docs.ditto.live/dql/returning
DQL clause for retrieving data from the documents affected by INSERT, UPDATE, DELETE, EVICT and TOMBSTONE statements
`RETURNING` is available in SDK 5.1 and later.
The optional `RETURNING` clause is accepted by the three DML (Data Manipulation Language) statements — [INSERT](/dql/insert), [UPDATE](/dql/update) and [DELETE](/dql/delete)/[EVICT](/dql/evict)/TOMBSTONE — and by no other statement.
A DML statement normally reports only the IDs of the documents it changed. Adding `RETURNING` turns it into a statement that returns a result set built from the affected documents, in the same form as a [SELECT](/dql/select) statement, so you can read the affected data back without issuing a second query.
![RETURNING Syntax Diagram]()
## Documents Projected
Each statement projects the affected documents at a different point in its processing:
| Statement | Documents projected |
| ---------------------------- | ----------------------------------------------------------------------------------------------------------------------------------- |
| `INSERT` | The documents as written, after the insert. Documents skipped by `ON ID CONFLICT DO NOTHING` are not written, so are not projected. |
| `UPDATE` | The documents as they are after the `APPLY`, `SET` and `UNSET` mutators have been applied. |
| `DELETE`/`EVICT`/`TOMBSTONE` | The documents as they were immediately before removal. |
A statement populates either its result set (when `RETURNING` is present) or its list of mutated document IDs (when it is not), never both.
## Projection Syntax
`RETURNING` takes the same [projection](/dql/select#projections) syntax as a `SELECT` statement, with these differences:
* `DISTINCT` may not be specified. The word is taken to be a field name rather than a keyword.
* `RAW` projections are not supported.
* The unqualified `*` wildcard is only accepted as the sole element of the projection list; combining it with further elements is an error. A qualified `alias.*` has no such restriction.
* [Aggregates](/dql/select#aggregate-functions) are permitted and are evaluated over the whole set of affected documents; there is no `GROUP BY` clause. When any element of the projection is an aggregate, the remaining elements must not depend on the documents, otherwise an error is raised.
Default aliases are assigned, and uniqueness of aliases is required, exactly as for a `SELECT` projection.
## Examples
Return the complete documents that were inserted:
```sql DQL theme={null}
INSERT INTO cars DOCUMENTS ({"_id": "123", "color": "blue"})
RETURNING *
```
Return selected fields of the documents that were updated, with their post-update values:
```sql DQL theme={null}
UPDATE cars
SET color = 'red'
WHERE color = 'blue'
RETURNING _id, color
```
Return the documents that were deleted, as they were immediately before removal:
```sql DQL theme={null}
DELETE FROM cars
USE IDS '123', '456'
RETURNING *
```
Return an aggregate over the affected documents rather than the documents themselves:
```sql DQL theme={null}
DELETE FROM cars
WHERE year < 2000
RETURNING COUNT(*) AS removed
```
Use an expression and an alias, exactly as in a `SELECT` projection:
```sql DQL theme={null}
UPDATE cars
APPLY odometer INCREMENT BY 100
WHERE _id = '123'
RETURNING _id, odometer / 1.60934 AS mileage
```
## RETURNING and Legacy DML
`RETURNING` is implemented by the query engine's DML operators only. The legacy (non-operator-model) implementation does not support it, so a statement that combines the two is rejected with the error `RETURNING clause with legacy DML is not supported`.
Legacy handling is selected in either of two ways:
* the [`#disable_dml`](/dql/directives#disable_dml) directive on an individual statement, which disables `RETURNING` for that statement only; or
* the [`DQL_USE_LEGACY_DML`](/dql/alter-system#dql_use_legacy_dml) system parameter, which does the same for every statement.
The same restriction applies to the rest of the syntax that only the operator model implements: `USE IDS` on `UPDATE` and `DELETE`/`EVICT`/`TOMBSTONE`, and [`INSERT` sourced from a `SELECT`](/dql/insert#insert-from-a-select-statement).
`DQL_USE_LEGACY_DML` defaults to `false` in the Edge SDK (small peer) and `true` on Ditto Server (big peer). `RETURNING` is therefore available by default in the SDK, and on Ditto Server only where that parameter has been set to `false`.
## See Also
* [INSERT](/dql/insert) - Inserting documents
* [UPDATE](/dql/update) - Modifying documents
* [DELETE](/dql/delete) - Permanently removing documents
* [EVICT](/dql/evict) - Removing documents locally
* [SELECT](/dql/select#projections) - Projection syntax and aggregates
* [Directives](/dql/directives#disable_dml) - The `#disable_dml` directive
# SELECT
Source: https://docs.ditto.live/dql/select
The `SELECT` operation, once executed, retrieves documents from a collection and uses clauses like `WHERE` to specify conditions for filtering the documents to return.
DQL now supports projections and aggregates in addition to `SELECT *` operations. You can select specific fields, perform calculations, and use aggregate functions like `COUNT`, `SUM`, `AVG`, `MIN`, and `MAX`.
## SELECT Statements
The following syntax outlines the basic structure and optional clauses you can use within your `SELECT` statements:
```sql DQL theme={null}
SELECT [DISTINCT] projection
FROM your_collection_name
[WHERE condition]
[GROUP BY groupby_expression_1, groupby_expression_2, ...]
[HAVING condition]
[ORDER BY orderby_expression_1, orderby_expression_2, ... [ASC|DESC]]
[LIMIT limit_value]
[OFFSET number_of_documents_to_skip]
```
![SELECT Syntax Diagram]()
Where `projection` can be:
* `*` - Returns all fields from the documents
* `field1, field2, ...` - Returns specific fields
* `expression AS alias` - Returns calculated values with custom names
* Aggregate functions like `COUNT(*)`, `SUM(field)`, etc.
For instance, retrieve all documents in the `cars` collection `WHERE` the `color` property is set to the value 'blue' :
```sql DQL theme={null}
SELECT * FROM cars WHERE color = 'blue'
```
## Clauses for Filtering
The following table provides an overview of the different clauses you can use to define specific conditions and calculations within your DQL `SELECT` statements to provide more granular control over your queries:
| **Clause** | **Description** |
| ---------- | ---------------------------------------------------------------------------------------------------------------------- |
| FROM | The required clause specifying the collection containing the documents for retrieval. (See [FROM](/dql/select#from)) |
| JOIN | Correlates documents from a second (or further) collection using an ON condition. (See [Joins](#joins)) |
| WHERE | Applies filtering conditions to restrict the documents included in the result set. (See [WHERE](/dql/select#where)) |
| GROUP BY | Groups documents based on one or more expressions for aggregate calculations. (See [GROUP BY](/dql/select#group-by)) |
| HAVING | Filters grouped results based on aggregate conditions. (See [HAVING](/dql/select#having)) |
| ORDER BY | Specifies the sorting order of the result set based on one or more expressions. (See [ORDER BY](/dql/select#order-by)) |
| LIMIT | Restricts the number of documents included in the result set. (See [LIMIT](/dql/select#limit)) |
| OFFSET | Skips a specific number of documents before returning the result set. (See [OFFSET](/dql/select#offset)) |
### FROM
Required in each `SELECT` statement you write in DQL, the `FROM` element identifies the collection for document retrieval.
```sql DQL theme={null}
SELECT *
FROM your_collection_name
```
![FROM Clause Syntax Diagram]()
![CollectionTerm Syntax Diagram]()
For example, a `SELECT` statement querying documents from the `cars` collection:
```sql DQL theme={null}
SELECT * FROM cars
```
#### USE IDS
The optional `USE IDS` clause allows you to efficiently retrieve specific documents by their IDs without performing a collection scan. This is more performant than using `WHERE _id IN (...)` filters.
**Syntax:**
```sql DQL theme={null}
-- Inline IDs
SELECT * FROM collection_name USE IDS (id1, id2, id3)
-- Using a parameter array
SELECT * FROM collection_name USE IDS LIST :id_array
```
![USE IDS Syntax Diagram]()
**Examples:**
```sql DQL theme={null}
-- Retrieve specific documents by ID
SELECT * FROM cars USE IDS ('123', '456', '789')
-- Using a parameter array (recommended for dynamic queries)
SELECT * FROM cars USE IDS LIST :car_ids
```
```swift Swift theme={null}
let carIds = ["123", "456", "789"]
let results = await ditto.store.execute(
query: "SELECT * FROM cars USE IDS LIST :car_ids",
arguments: ["car_ids": carIds]
)
```
```kotlin Kotlin theme={null}
val carIds = listOf("123", "456", "789")
val results = ditto.store.execute(
"SELECT * FROM cars USE IDS LIST :car_ids",
mapOf("car_ids" to carIds)
)
```
```javascript JS theme={null}
const carIds = ["123", "456", "789"];
const results = await ditto.store.execute(
"SELECT * FROM cars USE IDS LIST :car_ids",
{ car_ids: carIds }
);
```
```java Java theme={null}
List carIds = Arrays.asList("123", "456", "789");
ditto.store.execute(
"SELECT * FROM cars USE IDS LIST :car_ids",
Collections.singletonMap("car_ids", carIds)
);
```
```csharp C# theme={null}
var carIds = new List { "123", "456", "789" };
await ditto.Store.ExecuteAsync(
"SELECT * FROM cars USE IDS LIST :car_ids",
new Dictionary { { "car_ids", carIds } }
);
```
```cpp C++ theme={null}
std::vector carIds = {"123", "456", "789"};
std::map> args;
args["car_ids"] = carIds;
ditto.get_store().execute(
"SELECT * FROM cars USE IDS LIST :car_ids",
args
).get();
```
```rust Rust theme={null}
let car_ids = vec!["123", "456", "789"];
ditto.store().execute_v2((
"SELECT * FROM cars USE IDS LIST :car_ids",
serde_json::json!({
"car_ids": car_ids
})
)).await?;
```
```dart Dart theme={null}
final carIds = ["123", "456", "789"];
await ditto.store.execute(
"SELECT * FROM cars USE IDS LIST :car_ids",
arguments: {"car_ids": carIds}
);
```
**Performance Tip**: Where possible, simple equality filters on `_id` (like `WHERE _id = '123'`) are automatically optimized internally similar to USE IDS. However, for explicit control and guaranteed performance, USE IDS is recommended. See also the `#auto_use_ids` directive in [Directives](/dql/directives).
#### Collection Aliasing
You can assign an alias to a collection in the FROM clause to create shorter, more readable queries. The `AS` keyword is optional:
```sql DQL theme={null}
-- With AS keyword
SELECT * FROM cars AS c WHERE c.color = 'blue'
-- Without AS keyword
SELECT * FROM cars c WHERE c.color = 'blue'
```
Collection aliases are particularly useful in:
* **Complex queries**: Shortening long collection names for readability
* **Qualified field references**: Explicitly referencing fields from a specific collection
* **Query directives**: Specifying collection-level directives (see [Directives](/dql/directives))
```sql DQL theme={null}
-- Using collection alias with qualified field references
SELECT c.make, c.model, c.year
FROM cars c
WHERE c.color = 'blue'
ORDER BY c.year DESC
-- Using collection alias with directives
/*+ {"c": {"#index": "ix_color"}} */
SELECT * FROM cars c WHERE c.color = 'blue'
```
Collection aliases follow the same identifier rules as projection aliases and can also use backticks for special characters or reserved words.
#### JOIN
Joins are only supported for local SDK queries. Joins are not supported in subscription queries or Big Peer/Server queries. See [restrictions](#restrictions) for more details.
The `FROM` clause may be followed by one or more `JOIN` terms to correlate documents from additional collections. Ditto supports `INNER JOIN`, `LEFT OUTER JOIN`, and `RIGHT OUTER JOIN`. Each join requires an access path that isn't a full collection scan on the inner collection (by default); the `ADVISE` statement can recommend appropriate indexes.
```sql DQL theme={null}
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
WHERE o.status = 'paid'
```
For full syntax, join types, index requirements, query plan details, and advanced usage, see [Joins](#joins) below.
### WHERE
The `WHERE` clause filters data based on either an expression or a set of conditions that narrow the result set returned to you:
```sql DQL theme={null}
SELECT *
FROM your_collection_name
WHERE [condition]
```
![WHERE Clause Syntax Diagram]()
For example, here is a basic `SELECT` statement querying documents from the `cars` collection based on a given address:
```sql DQL theme={null}
SELECT * FROM cars
WHERE location.address = '123 Main St, San Francisco, CA 98105'
```
To demonstrate a more complex query, here is a `SELECT` statement that queries using multiple expressions and logical operators to further refine the criteria for document retrieval:
```sql DQL theme={null}
SELECT * FROM cars
WHERE color = 'blue' AND features.trim = 'Standard' OR features.mileage > 10000
```
### ORDER BY
With the `ORDER BY` clause, if you'd like, you can integrate calculations or expressions in your `SELECT` statement. Then sort the resulting documents to return in either ascending (`ASC`) or descending (`DESC`) alphabetical order:
```sql DQL theme={null}
SELECT *
FROM your_collection_name
ORDER BY expression_1, expression_2, ... [ASC|DESC]
```
![ORDER BY Clause Syntax Diagram]()
For example, here is a simple `SELECT` statement that uses the `ORDER BY` clause to query and sort documents from the `cars` collection in descending (`DESC`) alphabetical order based on the field value set for the `color` property:
```sql DQL theme={null}
SELECT *
FROM cars ORDER BY color DESC
```
In this syntax:
* `your_collection_name` is the name of the collection from which you want to retrieve the data.
* `expression_1, expression_2, ...` are the expressions evaluated to sort the result. Expressions are resolved in order.
* `[ASC|DESC]` is an optional parameter that specifies the sort order. If omitted, the default sort order is ascending (`ASC`). To sort in descending order, you can specify `DESC`.
**Example**
In this example, the result set from the query will be sorted in descending order based on the values in the field:
```sql DQL theme={null}
-- Sort by a given field_name
SELECT *
FROM your_collection_name
ORDER BY field_name DESC
```
For instance, here `"blue"` cars return first and other cars sort by the natural order in the collection:
```sql DQL theme={null}
SELECT *
FROM cars
ORDER BY color = 'blue'
```
#### **Sort Order by Object Type**
In DQL, the hierarchy for comparing and sorting objects varies based on the following criteria:
* If ascending (`ASC`) order operations:
* `boolean`
* `number`
* `binary`
* `string`
* `array`
* `object`
* `null`
* `missing`
* If descending (`DESC`) order operations, sorting order is reversed:
* `missing`
* `null`
* `object`
* `array`
* `string`
* `binary`
* `number`
* `boolean`
* If evaluating values, `true` results are prioritized and ordered first followed by `false` results.
#### Expressing Sort Order
Unless explicitly defined as `DESC` in your query, Ditto defaults to sorting in ascending (`ASC`). So, if you want to sort in ascending order, you do *not* have to express that in your query.
### LIMIT
The `LIMIT` clause is used to restrict the number of documents returned by a query, allowing you to specify a maximum limit on the number of documents to be included in the result set:
```sql DQL theme={null}
SELECT *
FROM your_collection_name
LIMIT limit_value
```
In this syntax:
* `your_collection_name` is the name of the collection from which you want to retrieve the data.
* `limit_value` is the maximum number of documents you want to include in the result set.
For example, only return the first 10 documents from the `your_collection_name` collection:
```sql DQL theme={null}
SELECT *
FROM your_collection_name
LIMIT 10
```
### OFFSET
The `OFFSET` clause is used to specify the number of records to skip before starting to return documents from the query result:
```sql DQL theme={null}
SELECT *
FROM your_collection_name
OFFSET number_of_items_to_skip
```
In this syntax:
* `your_collection_name` is the name of the collection from which you want to retrieve the data.
* `number_of_items_to_skip` is the number of items before returning the result set.
Using `OFFSET` with `LIMIT` is a common way to utilize `OFFSET`; for example:
```sql DQL theme={null}
SELECT *
FROM your_collection_name
LIMIT 10
OFFSET 10
```
## Projections
Projections allow you to specify exactly which fields or calculated values to return from your query, rather than returning entire documents. This reduces data transfer and processing overhead.
![Projection Syntax Diagram]()
The same projection syntax is used by the [`RETURNING`](/dql/returning) clause of `INSERT`, `UPDATE` and `DELETE`/`EVICT`/`TOMBSTONE` statements, with a small number of restrictions.
### Basic Field Selection
Select specific fields from documents:
```sql DQL theme={null}
SELECT make, model, year FROM cars
```
### Excluding Fields
To exclude specific fields from a projection, use the `MISSING` keyword. This can be combined with `*` to select all fields except those explicitly excluded:
```sql DQL theme={null}
-- Given a document: { "_id": "abc", "make": "Toyota", "model": "Camry", "color": "blue", "vin": "1234" }
-- Exclude "vin" and "color", keeping everything else:
SELECT cars.*, MISSING vin, MISSING color FROM cars
-- Result: { "_id": "abc", "make": "Toyota", "model": "Camry" }
```
### Aliasing
Use aliases to rename fields in your results:
```sql DQL theme={null}
SELECT make AS manufacturer, model AS car_model FROM cars
```
Default aliases are assigned if not specified:
* Simple field references use the field name as the alias
* Expressions get aliases like `($1)`, `($2)`, etc.
Aliases bind to the element immediately prior in the statement. `SELECT COUNT(*) AS car_count FROM cars` names the output field, but `SELECT COUNT(*) FROM cars AS car_count` aliases the `cars` collection — `COUNT(*)` falls back to its default alias.
#### Alias Constraints
Aliases must follow these rules:
* **Uniqueness**: Each alias in a SELECT list must be unique. Duplicate aliases will raise an error.
* **Identifier Rules**: Aliases must be valid identifiers following field naming conventions.
* **Special Characters**: Use backticks (grave accents) to quote aliases containing special characters or reserved words.
```sql DQL theme={null}
-- Using backticks for special characters
SELECT price AS `final-price`, model AS `car-model` FROM cars
-- Using backticks for reserved words
SELECT status AS `order`, type AS `select` FROM orders
```
#### Alias Scope
Projection aliases defined in the SELECT list can be referenced by the **ORDER BY** clause to sort by an aliased expression:
```sql DQL theme={null}
SELECT make, price * 0.9 AS sale_price FROM cars ORDER BY sale_price DESC
```
`GROUP BY` and `HAVING` do **not** accept projection aliases. They must reference the underlying source expression or aggregate function directly: use `GROUP BY color` (the source field) rather than `GROUP BY c` (a projection alias), and `HAVING COUNT(*) > 5` (the aggregate) rather than `HAVING car_count > 5` (the alias).
### Calculated Fields
Create new fields using expressions:
```sql DQL theme={null}
SELECT make, model, price * 0.9 AS discounted_price FROM cars
```
### DISTINCT Results
Remove duplicate rows from your results:
```sql DQL theme={null}
SELECT DISTINCT color FROM cars
```
**DISTINCT Performance Considerations:**
`DISTINCT` has performance implications you should understand:
**Memory Buffering:**
* All projections must be buffered in memory to enforce uniqueness
* This increases memory requirements proportional to the number of unique results
* Memory usage grows with the result set size
**Redundant Usage:**
* When the `_id` field is included (e.g., `SELECT DISTINCT *`), DISTINCT is redundant
* Each document already has a unique `_id`, making DISTINCT unnecessary
* This adds overhead without benefit - avoid in these cases
**Best Practices:**
* Only use DISTINCT when you truly need unique combinations of projected fields
* Avoid DISTINCT when projecting complete documents (`SELECT DISTINCT *`)
* Consider if your application logic can handle duplicates instead
* For large result sets, evaluate if DISTINCT is worth the memory cost
Projections in a [`RETURNING`](/dql/returning) clause may *not* specify `DISTINCT`; there, the word is taken to be a field name rather than a keyword.
## Aggregate Functions
Aggregate functions perform calculations across multiple documents and return a single result. DQL supports the following aggregate functions:
For a quick reference table of all aggregate functions and their syntax, see [Operator Expressions - Aggregate Functions](/dql/operator-expressions#aggregate-functions).
### COUNT
Count documents or non-null values:
```sql DQL theme={null}
-- Count all documents
SELECT COUNT(*) FROM cars
-- Count non-null values in a field
SELECT COUNT(color) FROM cars
-- Count distinct values
SELECT COUNT(DISTINCT color) FROM cars
```
### SUM
Calculate the sum of numeric values:
```sql DQL theme={null}
SELECT SUM(price) AS total_value FROM cars
-- Sum distinct values only
SELECT SUM(DISTINCT price) FROM cars
```
### AVG
Calculate the average of numeric values:
```sql DQL theme={null}
SELECT AVG(mileage) AS average_mileage FROM cars
```
### MIN and MAX
Find minimum and maximum values:
```sql DQL theme={null}
SELECT MIN(year) AS oldest_year, MAX(year) AS newest_year FROM cars
```
### MID
Calculate the midpoint between minimum and maximum values:
```sql DQL theme={null}
SELECT MID(price) AS midpoint_price FROM cars
-- With DISTINCT
SELECT MID(DISTINCT price) FROM cars
```
The `MID` function returns the midpoint value between `MIN(expr)` and `MAX(expr)`. Non-numeric values are silently ignored.
### MEDIAN
Calculate the median (middle) value:
```sql DQL theme={null}
SELECT MEDIAN(price) AS median_price FROM cars
-- With DISTINCT
SELECT MEDIAN(DISTINCT mileage) FROM cars
```
The `MEDIAN` function returns the middle value when all values are sorted. Non-numeric values are silently ignored.
### Combining Aggregates
Use multiple aggregate functions in a single query:
```sql DQL theme={null}
SELECT
COUNT(*) AS total_cars,
AVG(price) AS avg_price,
MIN(price) AS lowest_price,
MAX(price) AS highest_price
FROM cars
```
### GROUP BY
Group results and calculate aggregates for each group:
```sql DQL theme={null}
SELECT color, COUNT(*) AS car_count, AVG(price) AS avg_price
FROM cars
GROUP BY color
```
![GROUP BY Clause Syntax Diagram]()
When using aggregates with `GROUP BY`, non-aggregate projections must be part of the `GROUP BY` clause:
```sql DQL theme={null}
-- This works: 'make' is in GROUP BY
SELECT make, COUNT(*) AS model_count
FROM cars
GROUP BY make
-- This would fail: 'model' is not in GROUP BY
-- SELECT make, model, COUNT(*) FROM cars GROUP BY make
```
### HAVING
Filter grouped results based on aggregate values:
```sql DQL theme={null}
SELECT color, COUNT(*) AS car_count
FROM cars
GROUP BY color
HAVING COUNT(*) > 5
```
![HAVING Clause Syntax Diagram]()
Aggregate functions form a "dam" in the execution pipeline - all documents must be processed before results can be returned. This differs from non-aggregate queries which can stream results.
### Aggregate Function Behavior
Understanding how aggregate functions work internally helps optimize query performance:
**Memory Requirements:**
* Aggregates accumulate results per group, so memory usage depends on the number of groups
* A small number of groups requires little memory regardless of document count
* A large number of groups increases memory requirements proportionally
* `DISTINCT` aggregates must buffer all distinct values in memory, which can significantly increase memory usage with many unique values
**MISSING Values:**
* If no documents qualify for an aggregate in a group, the result is `MISSING` (absent from results)
* To always include a result, use conditional functions: `SUM(IFMISSING(field, 0))`
* `MIN` and `MAX` silently ignore `MISSING` values
* `COUNT` does not count `NULL`, `MISSING`, or `FALSE` values unless explicitly handled
**Type Handling:**
* `SUM`, `AVG`, `MID`, and `MEDIAN` silently ignore non-numeric values
* `MIN` and `MAX` compare values using Ditto's standard type ordering rules
* `COUNT(*)` is equivalent to `COUNT(true)` with no additional overhead
**Performance Tips:**
* Use `COUNT(*)` instead of `COUNT(field)` when counting all documents
* Avoid `DISTINCT` with aggregates unless necessary due to memory overhead
* Minimize the number of groups in `GROUP BY` for better memory efficiency
* Use `HAVING` to filter groups rather than filtering after aggregation
### Advanced Aggregate Examples
**Handling MISSING Values:**
```sql DQL theme={null}
-- Always return 0 instead of MISSING when no documents match
SELECT IFMISSING(SUM(price), 0) AS total_sales FROM orders WHERE status = 'completed'
-- Use default value for missing fields during aggregation
SELECT SUM(IFMISSING(price, 0)) AS total_with_defaults FROM products
-- Count all non-missing, non-null values regardless of truthiness
SELECT COUNT(NOT ISMISSINGORNULL(rating)) AS rated_items FROM reviews
```
**Conditional Aggregation with CASE:**
```sql DQL theme={null}
-- Sum order values by status within a single query
SELECT
COUNT(*) AS total_orders,
SUM(CASE WHEN status = 'completed' THEN final_price ELSE 0 END) AS completed_value,
SUM(CASE WHEN status = 'pending' THEN price * discount ELSE 0 END) AS pending_value,
SUM(CASE WHEN status = 'cancelled' THEN price ELSE 0 END) AS cancelled_value
FROM orders
-- Calculate weighted averages
SELECT
category,
AVG(CASE WHEN priority = 'high' THEN price * 1.5 ELSE price END) AS weighted_avg_price
FROM products
GROUP BY category
-- Sum with conditional multipliers
SELECT
SUM(CASE
WHEN quantity > 100 THEN price * quantity * 0.9
WHEN quantity > 50 THEN price * quantity * 0.95
ELSE price * quantity
END) AS total_revenue
FROM order_items
```
**Complex Business Logic:**
```sql DQL theme={null}
-- Calculate profit margin by category
SELECT
category,
(SUM(sale_price) - SUM(cost_price)) / SUM(sale_price) * 100 AS profit_margin_pct,
COUNT(*) AS items_sold
FROM sales
WHERE sale_date >= '2024-01-01'
GROUP BY category
HAVING (SUM(sale_price) - SUM(cost_price)) > 1000
-- Customer lifetime value with fallback for missing data
SELECT
customer_id,
COUNT(*) AS total_orders,
SUM(IFMISSING(order_total, 0)) AS lifetime_value,
AVG(IFMISSING(order_total, 0)) AS avg_order_value,
MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id
HAVING SUM(IFMISSING(order_total, 0)) > 500
-- Inventory health metrics
SELECT
warehouse_id,
COUNT(*) AS total_items,
SUM(CASE WHEN stock_level < reorder_point THEN 1 ELSE 0 END) AS items_needing_reorder,
SUM(CASE WHEN stock_level = 0 THEN 1 ELSE 0 END) AS out_of_stock_items,
AVG(IFMISSING(stock_level, 0)) AS avg_stock_level
FROM inventory
GROUP BY warehouse_id
```
**Filtering Within Aggregates:**
```sql DQL theme={null}
-- Count only items matching specific conditions
SELECT
department,
COUNT(*) AS total_employees,
COUNT(CASE WHEN salary > 100000 THEN 1 END) AS high_earners,
AVG(CASE WHEN years_experience > 5 THEN salary END) AS avg_senior_salary
FROM employees
GROUP BY department
-- Calculate percentages within groups
SELECT
region,
COUNT(*) AS total_sales,
SUM(CASE WHEN product_type = 'premium' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS premium_pct
FROM sales
GROUP BY region
```
**Handling NULL and MISSING Gracefully:**
```sql DQL theme={null}
-- Coalesce with multiple fallbacks
SELECT
product_id,
COUNT(*) AS review_count,
AVG(COALESCE(rating, average_category_rating, 3.0)) AS effective_rating
FROM reviews
GROUP BY product_id
-- Conditional counting with null handling
SELECT
status,
COUNT(*) AS total,
COUNT(completed_date) AS completed_with_date,
COUNT(*) - COUNT(completed_date) AS missing_dates
FROM tasks
GROUP BY status
-- Sum with NULL protection
SELECT
order_id,
SUM(IFNULL(item_price * quantity, 0)) AS order_total,
COUNT(*) AS item_count,
SUM(IFNULL(discount_amount, 0)) AS total_discount
FROM order_items
GROUP BY order_id
```
**Multi-level Aggregations:**
```sql DQL theme={null}
-- Nested calculations with aggregates
SELECT
category,
COUNT(*) AS product_count,
SUM(price * inventory_count) AS total_inventory_value,
SUM(price * inventory_count) / COUNT(*) AS avg_value_per_product,
MAX(price) - MIN(price) AS price_range
FROM products
GROUP BY category
HAVING COUNT(*) >= 5
-- Time-based aggregations with conditionals
SELECT
DATE_FORMAT(created_at,'YYYY-MM-DD') AS order_date,
COUNT(*) AS daily_orders,
SUM(IFMISSING(total, 0)) AS daily_revenue,
AVG(CASE WHEN total > 0 THEN total END) AS avg_order_value_excluding_zero
FROM orders
WHERE created_at >= DATE_CAST('2024-01-01','YYYY-MM-DD')
GROUP BY DATE_FORMAT(created_at,'YYYY-MM-DD')
ORDER BY order_date DESC
```
**Working with Arrays and Complex Fields:**
```sql DQL theme={null}
-- Aggregate with array transformations
SELECT
seller_id,
COUNT(*) AS total_products,
AVG(array_length(tags)) AS avg_tags_per_product,
SUM(CASE WHEN array_length(tags) > 5 THEN 1 ELSE 0 END) AS well_tagged_products
FROM products
GROUP BY seller_id
-- Conditional aggregation on nested fields
SELECT
category,
COUNT(*) AS total_items,
AVG(IFMISSING(details.weight, 0)) AS avg_weight,
SUM(CASE WHEN details.fragile = true THEN 1 ELSE 0 END) AS fragile_count
FROM inventory
GROUP BY category
```
## Joins
Joins are available in SDK version 5.1 and later and are supported only on the Ditto SDK (small peer). They are **not** supported in Big Peer (cloud/server) environments. See [Restrictions](#restrictions) for details.
DQL joins let you correlate documents across two or more collections within a single `SELECT` statement. Rather than issuing separate queries and stitching results together in application code, you express the relationship directly in DQL and let the query engine handle it efficiently.
A join appends fields from an additional collection to each row produced by the preceding collection (or join result), based on a condition you supply in the `ON` clause. The engine iterates over the outer (driving) collection (or join result) and, for each outer row, probes the inner (joined) collection using an appropriate access path in a strategy known as a **nested-loop join** (`nlJoin` in `EXPLAIN` output).
**Why use joins?**
* **Avoid data duplication.** Store related data in separate collections and compose views on demand rather than embedding copies everywhere.
* **Cross-collection queries.** Filter, sort, and project fields from multiple collections in one round trip to the store.
* **Richer aggregations.** Group and aggregate across collections — for example, sum order amounts per customer, or count products per order.
**Practical example.** Suppose you maintain a `customers` collection and an `orders` collection. Without joins you would fetch all orders, then issue a second query per customer to look up the name. With a join you express this directly:
```sql DQL theme={null}
SELECT c.name, o.amount, o.status
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
WHERE o.status = 'paid'
ORDER BY c.name, o.amount
```
### Syntax
Each term (collection or prior join) in the [FROM](#from) clause may be followed by a JOIN.
![JoinTerm Syntax Diagram]()
#### Join types
| Keyword | Behaviour |
| ---------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `JOIN` or `INNER JOIN` | Only rows where the `ON` condition evaluates to `true` appear in the result. |
| `LEFT JOIN` or `LEFT OUTER JOIN` | All rows from the left (outer) collection appear; rows with no matching document in the right collection are padded with `MISSING` for the right-side fields. |
| `RIGHT JOIN` or `RIGHT OUTER JOIN` | Supported only as the **first** join term. Internally rewritten to `LEFT OUTER JOIN` with the tables swapped. |
#### ON condition
The `ON` expression may reference any collection alias that has already been introduced in the `FROM` clause — that is, the driving collection and any collections joined before the current term. It may be any valid DQL expression, including compound conditions with `AND` or `OR`.
#### Collection aliases
Aliases are strongly recommended when joining so that field references are unambiguous. Aliases must be unique across all collections in the statement.
```sql DQL theme={null}
-- Recommended: explicit aliases on every collection
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
```
#### USE IDS, USE INDEX, and USE DIRECTIVES
Each collection term in a join may include `USE IDS`, `USE INDEX`, or `USE DIRECTIVES` sub-clauses to control how that collection is accessed. These apply per-collection and are particularly useful for the inner leg of a join.
```sql DQL theme={null}
-- Force a collection scan on the inner leg (see Overriding the index requirement below)
SELECT c.name, o.amount
FROM customers c
JOIN orders o USE INDEX "" ON c.cust_id = o.cust_id
```
### Index requirements
By default, every inner collection in a join **must** be accessed via an index scan (or combine index scans) or an ID scan. Attempting to join without an appropriate access path will produce an error:
```
Query failed: Joining to "orders" disallowed without appropriate index support.
Please run ADVISE for recommendations.
```
You can override this on a per-collection basis by adding `USE INDEX ""` to the join collection term, which explicitly permits a collection scan. See [Overriding the index requirement](#overriding-the-index-requirement) for details.
#### Creating a join index
The join key on the inner (right-hand) collection must be indexed. Create the index before executing a join query:
```sql DQL theme={null}
-- Create an index on the join key in the inner collection
CREATE INDEX ix_orders_cust_id ON orders (cust_id INCLUDE MISSING)
```
The `INCLUDE MISSING` option ensures documents that lack the `cust_id` field are covered by the index and will not be silently omitted from results.
#### Getting index recommendations with ADVISE
If you are unsure which indexes a join requires, run `ADVISE` on the statement. The engine analyses the query and suggests the optimal indexes, including composite covering indexes where beneficial:
```sql DQL theme={null}
ADVISE SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
```
Example output:
```json theme={null}
{
"advice": {
"statement": "SELECT c.name, o.amount FROM customers c JOIN orders o ON c.cust_id = o.cust_id\n",
"suggestedIndexes": [
{
"collection": "orders",
"reason": "equality predicates on `cust_id`; covering index; supports join",
"statement": "CREATE INDEX IF NOT EXISTS adv_orders_cov_cust_id_amount ON default:`orders` (`cust_id` ASC, `amount` ASC)"
}
]
}
}
```
Once all required indexes exist, `ADVISE` confirms this:
```json theme={null}
{
"advice": {
"existingIndexes": [
{
"collection": "orders",
"statement": "CREATE INDEX IF NOT EXISTS ix_orders_cust_id ON default:`orders` (`cust_id` ASC)"
}
],
"outcome": "optimal indexes already exist",
"statement": "..."
}
}
```
### Examples
The examples below use these two collections:
* **`customers`** — `_id`, `cust_id` (integer), `name`, `tier`
* **`orders`** — `_id`, `order_id` (integer), `cust_id` (integer), `amount`, `status`
Assume the following index exists for all examples:
```sql DQL theme={null}
CREATE INDEX ix_orders_cust_id ON orders (cust_id INCLUDE MISSING)
```
#### INNER JOIN
Returns only rows where the ON condition is satisfied in both collections. Customers with no orders and orders with no matching customer are excluded.
```sql DQL theme={null}
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
ORDER BY c.name, o.amount
```
#### LEFT OUTER JOIN
All customers are returned. Where no matching order exists, the order-side fields (`o.amount`) are `MISSING` in the result document.
```sql DQL theme={null}
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
ORDER BY c.name, o.amount
```
You can identify unmatched outer rows by testing for `MISSING`:
```sql DQL theme={null}
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
WHERE o.amount IS MISSING
ORDER BY c.name
```
#### RIGHT OUTER JOIN
Supported only as the first join in the statement, and is internally rewritten to a `LEFT OUTER JOIN` with the tables swapped. All orders are returned; customers with no matching order have `MISSING` name fields.
```sql DQL theme={null}
SELECT c.name, o.amount
FROM customers c
RIGHT JOIN orders o ON c.cust_id = o.cust_id
ORDER BY o.amount
```
#### Post-join WHERE filter
`WHERE` predicates are evaluated after the join. Predicates that reference only one collection are pushed down into the join condition where possible.
```sql DQL theme={null}
SELECT c.name, o.order_id
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
WHERE o.status = 'paid'
ORDER BY c.name, o.order_id
```
#### Multi-expression ON clause
The `ON` clause may contain compound expressions. Conditions that can be evaluated against the outer row alone are filtered early; the remainder form the join condition.
```sql DQL theme={null}
-- Only gold-tier customers join with their orders
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id AND c.tier = 'gold'
ORDER BY c.name, o.amount
```
#### Wildcard projection across joined collections
Use `*` to include all collections, each under its alias:
```sql DQL theme={null}
SELECT *
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
```
Or expand a single collection's fields:
```sql DQL theme={null}
SELECT c.*, o.amount, o.status
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
```
#### Multi-collection chains
You may chain any number of join terms (See [Directives](#directives)). Each subsequent join may reference all previously introduced aliases in its `ON` clause.
```sql DQL theme={null}
-- Three-way join: customers -> orders -> products
SELECT c.name, o.order_id, p.pname
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
JOIN products p ON o.order_id = p.order_id
ORDER BY c.name, o.order_id, p.pname
```
```sql DQL theme={null}
-- Four-way join: customers -> orders -> products -> reviews
-- LEFT JOIN on reviews so products without reviews still appear
SELECT c.name, o.order_id, p.pname, r.rating
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
JOIN products p ON o.order_id = p.order_id
LEFT JOIN reviews r ON p.product_id = r.product_id
ORDER BY c.name, o.order_id, p.pname, r.rating
```
#### Joining on document ID
When the `ON` condition equates a field from the outer collection to `_id` of the inner collection, the planner uses an efficient `idScan` rather than a regular index scan — no additional index is required on the inner collection.
```sql DQL theme={null}
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON o._id = c.latest_order_id
```
### Directives
All [query directives](/dql/directives) are available when using joins. A number are particularly relevant.
#### Limiting the number of join terms
The `#max_joins` global directive limits how many join terms a statement may contain. The default is `10`. Set it to `0` to prohibit joins entirely in a given query.
```sql DQL theme={null}
/*+ {"#max_joins": 2} */
SELECT c.name, o.amount, p.pname
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
JOIN products p ON o.order_id = p.order_id
```
#### Controlling access paths per collection
Use the `USE INDEX` or `USE DIRECTIVES` sub-clause on any collection term to influence the access path for that specific leg of the join.
```sql DQL theme={null}
-- Force the inner leg to use a specific index
SELECT c.name, o.amount
FROM customers c
JOIN orders o USE INDEX 'ix_orders_cust_id' ON c.cust_id = o.cust_id
-- Force an intersect scan by specifying candidate indexes
/*+ {"o": {"#index": ["ix_orders_cust_id", "ix_orders_status"]}} */
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
WHERE o.status = 'paid'
```
The `USE INDEX` and `USE DIRECTIVES` sub-clauses on join collections are collection-level directives and apply to that collection instance only. They follow the same precedence rules as described in [Query Directives](/dql/directives).
#### Overriding the index requirement
You can explicitly permit a collection scan on an inner join leg by supplying an empty string to `USE INDEX`. This overrides the default requirement for indexed access and should only be used when the inner collection is small or when you have a specific reason to avoid an index access path.
```sql DQL theme={null}
SELECT c.name, o.amount
FROM customers c
JOIN orders o USE INDEX "" ON c.cust_id = o.cust_id
```
The override is per-collection. If you have three join terms and supply `USE INDEX ""` on only one, the other two must still have index access paths.
#### Prefer-order with joins
The `#prefer_order` directive applies to the driving collection. It is rarely necessary for joins — the join order is fixed by the statement — but can influence index selection on the outer leg when `ORDER BY` is present.
### Understanding query plans with EXPLAIN
Prefix any join `SELECT` with `EXPLAIN` to inspect the plan without executing the query. The plan reveals which access path the planner chose for each leg of the join — index scan, covering index scan, intersect scan, or ID scan — and confirms whether `LEFT OUTER` semantics are in effect.
See [EXPLAIN — Join plans](/dql/explain#join-plans) for a full description of the `nlJoin` operator, dynamic span bounds, covering and intersect scans, and how `LEFT OUTER`, `RIGHT OUTER`, and multi-collection joins appear in the plan.
### Best practices
1. **Index every join key on the inner collection.** Use `ADVISE` to confirm which indexes are needed.
2. **Use composite covering indexes** that include both the join key and any projected or filtered fields from the inner collection. This eliminates the `fetch` step and reduces document reads.
3. **Order join terms thoughtfully.** Place the most selective (smallest) collection first as the outer (driving) leg to reduce the number of inner-leg probes.
4. **Qualify all field references with aliases.** Unqualified field names in multi-collection queries can be ambiguous.
5. **Use `EXPLAIN` during development** to confirm the planner has chosen index scans rather than collection scans for inner legs.
6. **Use `ADVISE` before deploying** new join queries to ensure optimal indexes are in place.
### Restrictions
* **Not supported in Big Peer environments.** Joins are a Ditto SDK (small peer) feature only.
* **Each inner collection must have an indexed access path** — either an index scan, a covering index scan, an intersect/union scan, or an ID scan. A collection scan on an inner leg is rejected unless explicitly permitted via `USE INDEX ""`.
* **`RIGHT OUTER JOIN`** is only supported as the first join term. Subsequent join terms must use `INNER` or `LEFT OUTER`. The right join is rewritten internally; the rewrite is transparent to the result.
* **The join count limit** defaults to 10 join terms per statement. Adjust with `/*+ {"#max_joins": N} */`.
* **Using joins in `registerSubscription`** will result in an unsupported query error. Joins are supported in `execute` and `registerObserver`.
# SHOW
Source: https://docs.ditto.live/dql/show
The SHOW statement displays the values of configuration parameters in your Ditto environment.
The `SHOW` statement allows you to view configuration parameter values that control various aspects of your Ditto instance's behavior.
## Syntax
```sql DQL theme={null}
SHOW ALL [LIKE pattern | ILIKE pattern [ESCAPE char]]
SHOW parameter_name
```
![SHOW Syntax Diagram]()
## SHOW ALL
Display all configuration parameters and their current values:
```sql DQL theme={null}
SHOW ALL
```
### Filtering with LIKE
You can filter the parameters displayed using pattern matching:
```sql DQL theme={null}
-- Show all parameters containing "query"
SHOW ALL LIKE '%query%'
-- Show all parameters starting with "dql"
SHOW ALL LIKE 'dql%'
-- Case-insensitive pattern matching
SHOW ALL ILIKE 'DQL%'
```
The pattern supports:
* `%` - Matches zero or more characters
* `_` - Matches exactly one character
### Escape Characters
If you need to match literal `%` or `_` characters in parameter names, use the `ESCAPE` clause:
```sql DQL theme={null}
SHOW ALL LIKE 'param\_%' ESCAPE '\'
```
## SHOW Specific Parameter
Display the value of a single configuration parameter:
```sql DQL theme={null}
SHOW dql_strict_mode
```
## Common Configuration Parameters
Some frequently used configuration parameters include:
| **Parameter** | **Description** |
| ----------------- | ------------------------------------------------------------ |
| `dql_strict_mode` | Controls whether strict mode is enabled for type definitions |
| `max_query_time` | Maximum execution time for queries |
Use `SHOW ALL` to see the complete list of available configuration parameters and their current values in your Ditto instance.
## Usage Examples
```swift Swift theme={null}
// Show all parameters
let result = await ditto.store.execute(
query: "SHOW ALL"
)
// Show specific parameter
let strictMode = await ditto.store.execute(
query: "SHOW dql_strict_mode"
)
// Show parameters matching pattern
let dqlParams = await ditto.store.execute(
query: "SHOW ALL LIKE 'dql%'"
)
```
```kotlin Kotlin theme={null}
// Show all parameters
val result = ditto.store.execute("SHOW ALL")
// Show specific parameter
val strictMode = ditto.store.execute("SHOW dql_strict_mode")
// Show parameters matching pattern
val dqlParams = ditto.store.execute("SHOW ALL LIKE 'dql%'")
```
```javascript JS theme={null}
// Show all parameters
const result = await ditto.store.execute("SHOW ALL");
// Show specific parameter
const strictMode = await ditto.store.execute("SHOW dql_strict_mode");
// Show parameters matching pattern
const dqlParams = await ditto.store.execute("SHOW ALL LIKE 'dql%'");
```
```java Java theme={null}
// Show all parameters
var result = ditto.store.execute("SHOW ALL");
// Show specific parameter
var strictMode = ditto.store.execute("SHOW dql_strict_mode");
// Show parameters matching pattern
var dqlParams = ditto.store.execute("SHOW ALL LIKE 'dql%'");
```
```csharp C# theme={null}
// Show all parameters
var result = await ditto.Store.ExecuteAsync("SHOW ALL");
// Show specific parameter
var strictMode = await ditto.Store.ExecuteAsync("SHOW dql_strict_mode");
// Show parameters matching pattern
var dqlParams = await ditto.Store.ExecuteAsync("SHOW ALL LIKE 'dql%'");
```
```cpp C++ theme={null}
// Show all parameters
auto result = ditto.get_store().execute("SHOW ALL").get();
// Show specific parameter
auto strictMode = ditto.get_store().execute("SHOW dql_strict_mode").get();
// Show parameters matching pattern
auto dqlParams = ditto.get_store().execute("SHOW ALL LIKE 'dql%'").get();
```
```rust Rust theme={null}
// Show all parameters
let result = ditto.store()
.execute_v2(("SHOW ALL", serde_json::json!({})))
.await?;
// Show specific parameter
let strict_mode = ditto.store()
.execute_v2(("SHOW dql_strict_mode", serde_json::json!({})))
.await?;
// Show parameters matching pattern
let dql_params = ditto.store()
.execute_v2(("SHOW ALL LIKE 'dql%'", serde_json::json!({})))
.await?;
```
```dart Dart theme={null}
// Show all parameters
final result = await ditto.store.execute("SHOW ALL");
// Show specific parameter
final strictMode = await ditto.store.execute("SHOW dql_strict_mode");
// Show parameters matching pattern
final dqlParams = await ditto.store.execute("SHOW ALL LIKE 'dql%'");
```
## See Also
* [ALTER SYSTEM](/dql/alter-system) - Modify configuration parameters at runtime
* [Strict Mode](/dql/strict-mode) - Understanding DQL\_STRICT\_MODE parameter
# Strict Mode
Source: https://docs.ditto.live/dql/strict-mode
Available in v4.11 and later, strict mode helps Ditto enforce structure and CRDT type safety in your collections.
## Introduction
With strict mode enabled (the default for all Ditto 4.x SDKs), all fields are treated as a [register](/key-concepts/syncing-data#registers) by default.
When enabled, every field in a document must match the collection definition exactly, including its CRDT type (e.g., [map](/key-concepts/syncing-data#maps), [register](/key-concepts/syncing-data#registers), [counter](/key-concepts/syncing-data#counters)).
Disabling strict mode enables new functionality: when set to `false`, **collection definitions are no longer required when using multiple CRDT types.**\
SELECT queries will return and display all fields when strict mode is disabled. This matches the behavior of the legacy query language, objects in INSERT and UPDATE statements are treated as maps. When a field has multiple possible CRDT types, the most recently updated type is chosen.
When strict mode is disabled, Ditto will infer the CRDT type based on the document's shape:
* **Objects** → treated as **CRDT maps**
* **Scalars and arrays** → treated as **Registers**
* **Counters and attachments** → inferred from operations
**Important for Cross-Peer Synchronization**
When peers have different `DQL_STRICT_MODE` settings:
* Data WILL sync between peers, but behavior changes
* When strict mode is `true` (SDK 4.10 and earlier), nested objects default to REGISTER type
* For consistent behavior, either use matching strict mode settings across all peers OR explicitly define MAP types in your collection definitions in 4.10 and below.
See [Cross-Peer Synchronization](#cross-peer-synchronization) for detailed examples.
## Strict Mode Behavior
Strict mode is enabled (set to `true`) by default in SDK 4.x, but will be set to `false` by default in SDK 5.0 and later.
If you are using SDK 4.11+, ensure you have configured strict mode by setting `DQL_STRICT_MODE=false`, before starting your sync.
| **Feature** | **`SDK <4.10: DQL_STRICT_MODE=true`** | **`SDK 4.11+: DQL_STRICT_MODE=false`** |
| ---------------------- | ---------------------------------------- | -------------------------------------- |
| Nested MAPs | Requires explicit collection definitions | Automatically inferred |
| Collection Definitions | Required to use non-register CRDT types | Only required to use register objects |
| Legacy Compatibility | Difficult, due to collection definitions | Supported out of the box |
| Default object type | REGISTER (whole object replacement) | MAP (field-level merging) |
| Nested field updates | Replaces entire object | Merges individual fields |
## HTTP Usage
To use the HTTP API with strict mode disabled, use the `/api/v5/store/execute` endpoint.
### Endpoint Compatibility
| **Endpoint** | **Supports `DQL_STRICT_MODE=false`** | **Use When** |
| ----------------------- | ------------------------------------ | -------------------------------------- |
| `/api/v4/store/execute` | ❌ No | All peers have `DQL_STRICT_MODE=true` |
| `/api/v5/store/execute` | ✅ Yes | All peers have `DQL_STRICT_MODE=false` |
The v5 and v4 HTTP APIs [are compatible](/sdk/latest/release-notes/versioning#synchronization-compatibility), so you can use the v5 endpoint and changes will sync to v4 clients without issues.
**Example HTTP Request:**
```bash theme={null}
# Use v5 endpoint when DQL_STRICT_MODE=false sets `metadata` to MAP type
curl -X POST 'https://{YOUR_CLOUD_URL_ENDPOINT}/api/v5/store/execute' \
-H "Authorization: Bearer YOUR_TOKEN" \
-H "Content-Type: application/json" \
-d '{
"query": "UPDATE orders SET metadata.updatedAt = :date WHERE _id = :id",
"args": {"date": "2025-05-28", "id": "order-1"}
}'
```
[Read more about the HTTP API](/cloud/http-api/getting-started).
## SDK Usage
Strict mode defaults differ by SDK version:
* **SDK 4.x**: `DQL_STRICT_MODE` defaults to `true`
* **SDK 5.0+**: `DQL_STRICT_MODE` defaults to `false`
If you need to change from the default, set `DQL_STRICT_MODE` before starting sync or running any DQL queries or registering observers. Subscriptions can be registered before changing the setting.
```swift Swift theme={null}
// On SDK 4.x, disable strict mode to match the SDK 5 default behavior:
try await ditto.store.execute("ALTER SYSTEM SET DQL_STRICT_MODE = false")
// On SDK 5.0+, enable strict mode to match the SDK 4 default behavior:
try await ditto.store.execute("ALTER SYSTEM SET DQL_STRICT_MODE = true")
```
```kotlin Kotlin theme={null}
// On SDK 4.x, disable strict mode to match the SDK 5 default behavior:
ditto.store.execute("ALTER SYSTEM SET DQL_STRICT_MODE = false")
// On SDK 5.0+, enable strict mode to match the SDK 4 default behavior:
ditto.store.execute("ALTER SYSTEM SET DQL_STRICT_MODE = true")
```
```javascript JS theme={null}
// On SDK 4.x, disable strict mode to match the SDK 5 default behavior:
await ditto.store.execute("ALTER SYSTEM SET DQL_STRICT_MODE = false");
// On SDK 5.0+, enable strict mode to match the SDK 4 default behavior:
await ditto.store.execute("ALTER SYSTEM SET DQL_STRICT_MODE = true");
```
```java Java theme={null}
// On SDK 4.x, disable strict mode to match the SDK 5 default behavior:
ditto.store.execute("ALTER SYSTEM SET DQL_STRICT_MODE = false");
// On SDK 5.0+, enable strict mode to match the SDK 4 default behavior:
ditto.store.execute("ALTER SYSTEM SET DQL_STRICT_MODE = true");
```
```csharp C# theme={null}
// On SDK 4.x, disable strict mode to match the SDK 5 default behavior:
await ditto.Store.ExecuteAsync("ALTER SYSTEM SET DQL_STRICT_MODE = false");
// On SDK 5.0+, enable strict mode to match the SDK 4 default behavior:
await ditto.Store.ExecuteAsync("ALTER SYSTEM SET DQL_STRICT_MODE = true");
```
```cpp C++ theme={null}
// On SDK 4.x, disable strict mode to match the SDK 5 default behavior:
ditto.get_store().execute("ALTER SYSTEM SET DQL_STRICT_MODE = false").get();
// On SDK 5.0+, enable strict mode to match the SDK 4 default behavior:
ditto.get_store().execute("ALTER SYSTEM SET DQL_STRICT_MODE = true").get();
```
```rust Rust theme={null}
// On SDK 4.x, disable strict mode to match the SDK 5 default behavior:
ditto.store()
.execute_v2((
"ALTER SYSTEM SET DQL_STRICT_MODE = false",
serde_json::json!({})
))
.await?;
// On SDK 5.0+, enable strict mode to match the SDK 4 default behavior:
ditto.store()
.execute_v2((
"ALTER SYSTEM SET DQL_STRICT_MODE = true",
serde_json::json!({})
))
.await?;
```
```dart Dart theme={null}
// On SDK 4.x, disable strict mode to match the SDK 5 default behavior:
await ditto.store.execute("ALTER SYSTEM SET DQL_STRICT_MODE = false");
// On SDK 5.0+, enable strict mode to match the SDK 4 default behavior:
await ditto.store.execute("ALTER SYSTEM SET DQL_STRICT_MODE = true");
```
### Subscriptions
Subscriptions behave the same regardless of whether `DQL_STRICT_MODE` is enabled or disabled.
```swift theme={null}
ditto.sync.registerSubscription("SELECT * from orders WHERE _id.restaurantId = :restaurantId", args: [restaurantId: "01234"])
```
### Examples
With `DQL_STRICT_MODE=false`, objects are treated as maps. In the following
examples, `items` is an object so it is treated as a map, with further nested objects, which are also treated as maps.
This nested structure is common in document databases.
```javascript theme={null}
let doc = {
"_id": "my-id",
"regionId": "01234",
"items": {
"shake": {...},
"fries": {...},
}
}
```
We can insert the document into the database using DQL.
```sql theme={null}
INSERT INTO orders DOCUMENTS (:doc)
ON ID CONFLICT DO UPDATE
```
With `DQL_STRICT_MODE=false`, Ditto infers the CRDT type based on the document's shape.\
No collection definition is required.
```swift DQL_STRICT_MODE=false theme={null}
// When strict mode is set to `false`, Ditto infers the CRDT type based on the
// document's shape.
try await ditto.store.execute("ALTER SYSTEM SET DQL_STRICT_MODE = false")
ditto.startSync()
// Therefore we don't need to specify the collection definition in our query.
ditto.store.execute("SELECT * FROM orders WHERE _id = 'my-id'")
{
"_id": "my-id",
"regionId": "01234",
"items": {
"shake": {...},
"fries": {...},
}
}
```
```swift DQL_STRICT_MODE=true theme={null}
// When strict mode is set to `true`, Ditto treats objects as registers, which means that
// every non-register field's type must be specified in the collection definition.
try await ditto.store.execute("ALTER SYSTEM SET DQL_STRICT_MODE = true")
ditto.startSync()
// In this case, the items field is a map, so it must be defined as such in the
// collection definition.
ditto.store.execute("SELECT * FROM COLLECTION orders (items MAP) WHERE _id = 'my-id'")
{
"_id": "my-id",
"regionId": "01234",
"items": {
"shake": {...},
"fries": {...},
}
}
```
* When you define an object like `items: {"shake": ..., "fries": ...}` , **Ditto treats that as a MAP when strict mode is disabled**.
* Whether you use Query Builder or DQL, this structure is preserved and behaves the same on both insert and read.
* This is useful to know if you're working with dynamic key-value structures inside documents.
#### Updating Nested Maps
In 4.11 and above, use `UPDATE` statement to set nested MAPs.
**Using `UPDATE`**
```sql 4.11+ theme={null}
UPDATE orders
SET
metadata.updatedAt = '2025-05-28',
metadata.updatedBy = '67c0faa40054d13a000c614a'
WHERE _id = 'my-id'
```
```sql <4.10 theme={null}
-- Arrow (->) functions have been removed in v5.
UPDATE COLLECTION orders (items MAP)
SET metadata -> (
updatedAt -> '2025-05-28',
updatedBy -> '67c0faa40054d13a000c614a'
)
WHERE _id = 'my-id'
```
#### Updating Nested Maps with Dynamic Keys
Use `INSERT` and `ON ID CONFLICT DO UPDATE` to update nested maps with dynamic
UUIDs. This is the same behavior as using `upsert` in the legacy query builder.
```sql 4.11+ theme={null}
INSERT INTO orders DOCUMENTS (
_id: 'my-id',
items: {
'7c6a163e-3233-46db-a8ba-f604c4b8f': { 'basePrice': 200 },
'7c6a163e-3233-46db-a8ba-f604c4b8fdde': { 'basePrice': 100 }
}
) ON ID CONFLICT DO UPDATE
```
```sql <4.10 theme={null}
INSERT INTO orders (items MAP) DOCUMENTS (
_id: 'my-id',
items: {
'7c6a163e-3233-46db-a8ba-f604c4b8f': { 'basePrice': 200 },
'7c6a163e-3233-46db-a8ba-f604c4b8fdde':{ 'basePrice': 100 }
}
) ON ID CONFLICT DO UPDATE
```
#### Deleting Nested Values
In 4.11 and above with `DQL_STRICT_MODE=false`, use the `UNSET` statement.
```sql 4.11+ theme={null}
UPDATE orders
UNSET items.abc
WHERE _id = 'my-id'
```
```sql <4.10 theme={null}
-- Arrow (->) and tombstone() functions have been removed in v5.
UPDATE COLLECTION orders (items MAP)
SET items -> (
abc -> tombstone()
)
WHERE _id = 'my-id'
```
### Register Objects
A **`REGISTER`** is a data type in Ditto that stores a single scalar value and
uses last-write-wins merge strategy to atomically handle conflicts, rather than maps which use add-wins to create a merged object.
With `DQL_STRICT_MODE=false`, if you want a register object (JSON-like object) data type in DQL, it must be
specified explicitly.
Key characteristics of registers:
* Stores primitive types (string, boolean) or JSON-like objects
* Last-write-wins conflict resolution ensures consistent values across peers
```sql theme={null}
UPDATE COLLECTION orders (updatedAt REGISTER)
SET updatedAt = {
"datetime": "2025-02-28",
"authorId": "67c0faa40054d13a000c614a"
}
WHERE _id = 'my-id'
SELECT * FROM COLLECTION orders (updatedAt REGISTER) WHERE _id = 'my-id'
{
"_id": "my-id"
"regionId": "01234",
"items": {
"shake": {...},
"fries": {...},
"burger": {...}
},
"updatedAt": {
"datetime": "2025-02-28",
"updatedBy": "67c0faa40054d13a000c614a"
}
}
```
For more information on using types and definitions, see [DQL > Types and Definitions](/dql/types-and-definitions).
### Last Write Wins
With `DQL_STRICT_MODE=false`, if there is no collection definition provided,
writing objects will update a map, even if a register already exists for that field in Ditto.
This is called "last write wins" behavior, and it means that the last
operation to write to a field will overwrite any previous values, regardless of the type of the field.
```sql theme={null}
-- Set a register
UPDATE orders
SET items = 'a_string_value'
WHERE _id = 'my-id'
-- Implicitly infers (items MAP) even though there is a register on disk
-- as items is an object
UPDATE menus
SET items.burger = {...}
WHERE _id = 'my-id'
-- When querying, Ditto will return the map
-- as it was updated most recently
SELECT * FROM menus WHERE _id = 'my-id'
{
"_id": "my-id"
"regionId": "01234",
"items": {
"shake": {...},
"fries": {...},
"burger": {...}
}
}
```
If you do not supply the collection definition, a register can be treated as a nested map which can lead to unexpected behavior.
It is recommended that you always supply a collection definition if you want to force Ditto to use a register.
## Cross-Peer Synchronization
### Version Compatibility
| **Scenario** | **v4.10 and earlier** | **v4.11+** |
| --------------------------------------------- | ------------------------------- | ------------------------------------------------ |
| All peers same setting | Requires collection definitions | Works with or without definitions |
| Mixed settings, no definitions | Nested updates may fail | Objects default to REGISTER on strict=true peers |
| Mixed settings, with explicit MAP definitions | Works correctly | Works correctly |
### Mixing Peers with Different Settings
You can mix peers with different strict mode settings, but understanding the behavior is crucial for proper data synchronization:
#### Matched Settings (Recommended)
When all peers use the same `DQL_STRICT_MODE` setting, behavior is predictable:
* With `false`: Objects are treated as MAPs by default, nested updates work as expected
* With `true`: Collection definitions are required, types must be explicitly defined
#### Mismatched Settings (Requires Careful Handling, Avoid Unless Necessary)
When peers have different `DQL_STRICT_MODE` settings:
1. **Data DOES sync** between peers regardless of settings differences
2. **Default type behavior changes**:
* Peer with `DQL_STRICT_MODE=true` treats undefined objects as REGISTERs
* Peer with `DQL_STRICT_MODE=false` treats objects as MAPs
3. **This affects nested field updates significantly**
**Example of the Issue:**
```sql theme={null}
-- Peer A (DQL_STRICT_MODE=false): Updates nested fields
ALTER SYSTEM SET DQL_STRICT_MODE=false
UPDATE cars
SET items.sub1 = 'foo',
items.sub2 = 'bar'
WHERE _id.location = '1234'
-- Peer B (DQL_STRICT_MODE=true): WITHOUT collection definition
SELECT * from cars WHERE _id.location='1234'
{
"_id": {"location": "1234"}
}
-- Result: The entire cars.items object is missing
-- 'foo' and 'bar' values appear not to sync because the object
-- is treated as a REGISTER, not a MAP
```
**Solution Options:**
1. **Option 1: Match strict mode settings across all peers** (simplest)
```swift theme={null}
// All peers use the same setting
await ditto.store.execute("ALTER SYSTEM SET DQL_STRICT_MODE = false")
```
2. **Option 2: Explicitly define MAP types in collection definitions**
```sql theme={null}
-- When peers have different settings, explicitly define MAPs in 4.10 clients
-- Peer B (DQL_STRICT_MODE=true): WITH collection definition
SELECT COLLECTION cars (items MAP)
WHERE _id.location = '1234'
{
"_id": {"location": "1234"},
"items": {
"sub1": 'foo'
"sub2": 'bar'
}
}
```
## Troubleshooting Nested Field Sync Issues
### Common Symptom: "Nested fields are not syncing"
This is often caused by mismatched strict mode settings between peers without explicit MAP definitions.
In this case, the fields have actually all been synchronized correctly, but when querying the data is not displayed as expected.
#### Diagnostic Steps
1. **Check strict mode on all peers:**
```sql theme={null}
-- Run on each peer (4.11+) to check current setting
SHOW DQL_STRICT_MODE
```
2. **Verify your update query:**
```sql theme={null}
-- Example that might "fail" with mismatched settings
UPDATE orders
SET metadata.updatedAt = '2025-05-28',
metadata.updatedBy = 'user123'
WHERE _id = 'order-1'
```
3. **Check how the data appears on each peer:**
```sql theme={null}
-- Peer with DQL_STRICT_MODE=false sees nested updates
SELECT * FROM orders WHERE _id = 'order-1'
-- Returns: metadata: {updatedAt: '2025-05-28', updatedBy: 'user123'}
-- Peer with DQL_STRICT_MODE=true might see entire object replacement
SELECT * FROM orders WHERE _id = 'order-1'
-- Returns: metadata as a single REGISTER value
```
#### Real-World Example Fix
**Problem:** Updating nested fields:
```sql theme={null}
-- v4.11+ or v5 client, DQL_STRICT_MODE=false
UPDATE orders
SET metadata.updatedAt = '2025-05-28',
metadata.updatedBy = 'user123'
table = '12'
WHERE _id ='order-1'
-- v4.10 client, DQL_STRICT_MODE=true
SELECT * FROM orders WHERE _id = 'order-1'
-- Returns: table: '12'
-- Result: 'table' appears, but 'metadata.updatedAt' and 'metadata.updatedBy' do not
```
**Root Cause:** The client has `DQL_STRICT_MODE=true` while the updating peer has `DQL_STRICT_MODE=false`. Without a MAP definition, `metadata` is treated as a REGISTER.
**Solutions:**
```sql theme={null}
-- Solution 1: Add MAP definition on 4.10 client, DQL_STRICT_MODE=true
SELECT * FROM COLLECTION orders (metadata MAP) WHERE _id = 'order-1'
-- Solution 2: Ensure both peers have same strict mode
ALTER SYSTEM SET DQL_STRICT_MODE = false
SELECT * FROM orders WHERE _id = 'order-1'
```
# Types and Definitions
Source: https://docs.ditto.live/dql/types-and-definitions
Ditto Query Language (DQL) offers a set of *data types* designed to accommodate any edge sync scenario.
A data type is different than a standard scalar type by declaring merge behaviors, operations, and the spectrum of scalar types accessible for individual fields:
## Data Types
In DQL, you'll use three data types:`REGISTER`, `MAP`, and `ATTACHMENT` type. By
default, fields in a DQL statement are assigned the `REGISTER` type unless
otherwise specified by way of *type definition*.
Following are the key characteristics:
| **Type** | **CRDT Type** | **Payload** |
| --------------------- | ------------------------------ | ----------- |
| `REGISTER` | Last-write-wins | Any |
| `MAP` | Add-wins | Object |
| `ATTACHMENT` | Last-write-wins | Binary file |
| `COUNTER` | Positive-negative & LWW on Set | Integer |
| `PN_COUNTER` (legacy) | Positive-negative | Integer |
## Data Type Operations
Data types have different operations available.
### **REGISTER Operations**
A Register supports scalar types, including primitive types, such as `string`
and `boolean`, as well as a JSON blob, encapsulating multiple field‑value pairs
that function as a single object. The `REGISTER` can only be set to a specific
field.
For example:
```sql DQL theme={null}
field1 = 1
```
### **MAP Operations**
The `MAP` type supports inserting and tombstoning of fields using the functional
operators. Inserting a field is an implicit operation performed by assigning a
value to a field or a child of the field.
```sql 4.11+ theme={null}
field1.sub1.s_sub1 = 1
```
```sql <=4.10 theme={null}
-- With STRICT_MODE=true, to perform `MAP` operations, use the arrow `->`
-- operator followed by parentheses `()`, which contain one or more operations
-- on child fields of the `MAP`.
field1 -> (
sub1 -> (
s_sub1 = 1
)
)
```
### **ATTACHMENT Operations**
To set the last-write-wins `ATTACHMENT` data type, provide an `ATTACHMENT` object:
```sql DQL theme={null}
field1 = :attachment
```
Read more about [attachments and large binary files](/sdk/latest/crud/working-with-attachments).
### Counter (Settable Counter)
Counters are available on 4.14 and later.
A `COUNTER` is an enhanced version of the PN\_COUNTER that combines positive-negative counter semantics with the ability to explicitly set the counter to a specific value. Like PN\_COUNTER, it's a CRDT type that can be incremented or decremented by any peer, but it also supports a `RESTART` operation that uses last-write-wins semantics.
The counter is an integer value that automatically resolves conflicting increments and decrements from different peers by tracking operations and composing them to provide a final value. When peers perform `RESTART` operations concurrently, the last write wins.
Counters are useful for tracking counts that multiple peers might update simultaneously, such as:
* Like/vote counts with the ability to reset
* Inventory counts that need periodic recalibration
* Session counts that can be initialized to specific values
* Metrics that require both incremental updates and explicit resets
Counter operations (`INCREMENT BY`, `RESTART WITH`, `RESTART`) are specified using the `APPLY` clause, not the `SET` clause. The `APPLY` clause is specifically designed for CRDT operations on special field types like counters.
#### Strict Mode and Type Declarations
The requirement to declare counter types in queries depends on your [DQL\_STRICT\_MODE](/dql/strict-mode) setting:
**When `DQL_STRICT_MODE=true` (default):**
* You must declare counter types in `COLLECTION` definitions for `INSERT`, `UPDATE`, and `SELECT` statements
* Counter fields are only visible in queries when the type declaration is included
**When `DQL_STRICT_MODE=false`:**
* Counter type declarations are **not required** for `SELECT` and `UPDATE` statements using `APPLY`
* Counter type declarations are **still required** for `INSERT` statements
* Counter fields are automatically visible in queries without type declarations
```sql STRICT_MODE=true theme={null}
-- Must specify COUNTER type in all statements
INSERT INTO COLLECTION products (stock_count COUNTER)
VALUES ({ '_id': '123', 'stock_count': 100 })
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count INCREMENT BY 5
WHERE _id = '123'
SELECT * FROM COLLECTION products (stock_count COUNTER)
WHERE _id = '123'
```
```sql STRICT_MODE=false theme={null}
-- Must specify COUNTER type for INSERT
INSERT INTO COLLECTION products (stock_count COUNTER)
VALUES ({ '_id': '123', 'stock_count': 100 })
-- No type declaration needed for UPDATE with APPLY
UPDATE products
APPLY stock_count INCREMENT BY 5
WHERE _id = '123'
-- No type declaration needed for SELECT
SELECT * FROM products WHERE _id = '123'
```
#### Creating Counters
There are two ways to create a counter field:
1. **Using INSERT with type declaration**: You can declare a field as a `COUNTER` in the `COLLECTION` definition when inserting a document. This allows you to initialize the counter with a specific value (type declaration is required for INSERT regardless of strict mode):
```sql DQL theme={null}
INSERT INTO COLLECTION products (stock_count COUNTER)
VALUES ({ '_id': '123', 'name': 'Widget', 'stock_count': 100 })
```
2. **Using APPLY operations**: Counter fields are automatically created when you first use `INCREMENT` or `RESTART` operations on them. This is useful when you want to create documents without counter fields initially:
Do not initialize counter fields by inserting an integer value without declaring the `COUNTER` type. This creates a register field, not a counter.
#### Incrementing Counters
```swift Swift theme={null}
// Create document without counter field
let product = [
"_id": "123",
"name": "Widget"
]
await ditto.store.execute(
query: """
INSERT INTO COLLECTION products
INITIAL DOCUMENTS (:product)
""",
arguments: [ "product": product ])
// Then increment the counter (creates it if doesn't exist)
await ditto.store.execute(
query: """
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count INCREMENT BY 5
WHERE _id = '123'
""")
```
```kotlin Kotlin theme={null}
// Create document without counter field
var product = mapOf(
"_id" to "123",
"name" to "Widget"
)
ditto.store.execute("""
INSERT INTO COLLECTION products
INITIAL DOCUMENTS (:product)
""",
mapOf("product" to product))
// Then increment the counter (creates it if doesn't exist)
ditto.store.execute("""
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count INCREMENT BY 5
WHERE _id = '123'
""")
```
```javascript JS theme={null}
// Create document without counter field
const product = {
_id: "123",
name: "Widget"
};
await ditto.store.execute(`
INSERT INTO COLLECTION products
INITIAL DOCUMENTS (:product)`,
{ product });
// Then increment the counter (creates it if doesn't exist)
await ditto.store.execute(`
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count INCREMENT BY 5
WHERE _id = '123'`);
```
```java Java theme={null}
// Create document without counter field
Map product = new HashMap<>();
product.put("_id", "123");
product.put("name", "Widget");
DittoQueryResult result = (DittoQueryResult) ditto.store.execute(
"INSERT INTO COLLECTION products INITIAL DOCUMENTS (:product)",
Collections.singletonMap("product", product)
);
// Then increment the counter (creates it if doesn't exist)
ditto.store.execute(
"UPDATE COLLECTION products (stock_count COUNTER) " +
"APPLY stock_count INCREMENT BY 5 WHERE _id = '123'"
);
```
```csharp C# theme={null}
// Create document without counter field
var args = new Dictionary();
args.Add("product", new { _id = "123", name = "Widget" });
await ditto.Store.ExecuteAsync(
"INSERT INTO COLLECTION products INITIAL DOCUMENTS (:product)",
args);
// Then increment the counter (creates it if doesn't exist)
await ditto.Store.ExecuteAsync(
"UPDATE COLLECTION products (stock_count COUNTER) " +
"APPLY stock_count INCREMENT BY 5 WHERE _id = '123'");
```
```cpp C++ theme={null}
// Create document without counter field
std::map product;
product["_id"] = "123";
product["name"] = "Widget";
std::map args;
args["product"] = product;
auto result = ditto.get_store().execute(
"INSERT INTO COLLECTION products INITIAL DOCUMENTS (:product)",
args).get();
// Then increment the counter (creates it if doesn't exist)
ditto.get_store().execute(
"UPDATE COLLECTION products (stock_count COUNTER) "
"APPLY stock_count INCREMENT BY 5 WHERE _id = '123'"
).get();
```
```rust Rust theme={null}
// Create document without counter field
let query_result = ditto
.store()
.execute_v2((
"INSERT INTO COLLECTION products INITIAL DOCUMENTS (:product)",
serde_json::json!({
"product": {
"_id": "123",
"name": "Widget"
}
}),
)).await?;
// Then increment the counter (creates it if doesn't exist)
ditto.store()
.execute_v2((
"UPDATE COLLECTION products (stock_count COUNTER) \
APPLY stock_count INCREMENT BY 5 WHERE _id = '123'",
serde_json::json!({}),
)).await?;
```
```dart Dart theme={null}
// Create document without counter field
const product = {
"_id": "123",
"name": "Widget"
};
await ditto.execute("""
INSERT INTO COLLECTION products
INITIAL DOCUMENTS (:product)""",
{"product": product},
);
// Then increment the counter (creates it if doesn't exist)
await ditto.execute("""
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count INCREMENT BY 5
WHERE _id = '123'""",
);
```
To decrement a counter, use a negative value with `INCREMENT`:
```sql DQL theme={null}
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count INCREMENT BY -3
WHERE _id = '123'
```
#### Setting Counter Values with RESTART
The `RESTART` operation allows you to explicitly set a counter to a specific value or reset it to zero. This uses last-write-wins semantics, so if multiple peers restart a counter concurrently, the last write will win.
**Set counter to a specific value:**
```sql DQL theme={null}
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count RESTART WITH 100
WHERE _id = '123'
```
**Reset counter to zero:**
```sql DQL theme={null}
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count RESTART
WHERE _id = '123'
```
```swift Swift theme={null}
// Set counter to specific value
await ditto.store.execute(
query: """
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count RESTART WITH 100
WHERE _id = '123'
""")
// Reset counter to zero
await ditto.store.execute(
query: """
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count RESTART
WHERE _id = '123'
""")
```
```kotlin Kotlin theme={null}
// Set counter to specific value
ditto.store.execute("""
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count RESTART WITH 100
WHERE _id = '123'
""")
// Reset counter to zero
ditto.store.execute("""
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count RESTART
WHERE _id = '123'
""")
```
```javascript JS theme={null}
// Set counter to specific value
await ditto.store.execute(`
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count RESTART WITH 100
WHERE _id = '123'`);
// Reset counter to zero
await ditto.store.execute(`
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count RESTART
WHERE _id = '123'`);
```
```java Java theme={null}
// Set counter to specific value
ditto.store.execute(
"UPDATE COLLECTION products (stock_count COUNTER) " +
"APPLY stock_count RESTART WITH 100 WHERE _id = '123'"
);
// Reset counter to zero
ditto.store.execute(
"UPDATE COLLECTION products (stock_count COUNTER) " +
"APPLY stock_count RESTART WHERE _id = '123'"
);
```
```csharp C# theme={null}
// Set counter to specific value
await ditto.Store.ExecuteAsync(
"UPDATE COLLECTION products (stock_count COUNTER) " +
"APPLY stock_count RESTART WITH 100 WHERE _id = '123'");
// Reset counter to zero
await ditto.Store.ExecuteAsync(
"UPDATE COLLECTION products (stock_count COUNTER) " +
"APPLY stock_count RESTART WHERE _id = '123'");
```
```cpp C++ theme={null}
// Set counter to specific value
ditto.get_store().execute(
"UPDATE COLLECTION products (stock_count COUNTER) "
"APPLY stock_count RESTART WITH 100 WHERE _id = '123'"
).get();
// Reset counter to zero
ditto.get_store().execute(
"UPDATE COLLECTION products (stock_count COUNTER) "
"APPLY stock_count RESTART WHERE _id = '123'"
).get();
```
```rust Rust theme={null}
// Set counter to specific value
ditto.store()
.execute_v2((
"UPDATE COLLECTION products (stock_count COUNTER) \
APPLY stock_count RESTART WITH 100 WHERE _id = '123'",
serde_json::json!({}),
)).await?;
// Reset counter to zero
ditto.store()
.execute_v2((
"UPDATE COLLECTION products (stock_count COUNTER) \
APPLY stock_count RESTART WHERE _id = '123'",
serde_json::json!({}),
)).await?;
```
```dart Dart theme={null}
// Set counter to specific value
await ditto.execute("""
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count RESTART WITH 100
WHERE _id = '123'""",
);
// Reset counter to zero
await ditto.execute("""
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count RESTART
WHERE _id = '123'""",
);
```
#### Combining Counter Operations with Other Updates
You can combine counter operations with other field updates in a single statement:
```sql DQL theme={null}
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count INCREMENT BY 10
SET lastUpdated = '2025-12-16', updatedBy = 'user123'
WHERE _id = '123'
```
#### Querying Counter Values
You can retrieve the current value of a counter using a `SELECT` statement:
```sql DQL theme={null}
SELECT * FROM COLLECTION products (stock_count COUNTER)
WHERE _id = '123'
```
The counter value will appear as a regular integer in the query results:
```json theme={null}
{
"_id": "123",
"name": "Widget",
"stock_count": 105
}
```
#### Counter vs PN\_COUNTER
The key differences between `COUNTER` and `PN_COUNTER`:
| Feature | COUNTER | PN\_COUNTER |
| --------------------- | ------------------- | ----------- |
| Increment/Decrement | ✓ | ✓ |
| Set to specific value | ✓ (RESTART WITH) | ✗ |
| Reset to zero | ✓ (RESTART) | ✗ |
| Conflict resolution | PN + LWW on RESTART | PN only |
| Available since | 4.14+ | 4.11+ |
Use `COUNTER` when you need the ability to explicitly set or reset counter values. Use `PN_COUNTER` only for backward compatibility with older Ditto versions.
### PN Counter Operations (Legacy)
PN Counters are available in 4.11 and later. [Read more](/dql/strict-mode)
Users should use `Counter` which also provide the ability to set the counter value.
A counter is a special type of field that can be incremented or decremented. A
counter is a double-precision floating-point number. In 4.11 and above, ditto
offers PN\_COUNTER, or *positive-negative counter*, which is a CRDT type that can
be incremented or decremented by any peer. Counters automatically resolve
conflicting increments and decrements from different peers by tracking the
operations and composing them to provide a final value.
Counters are useful for tracking counts that multiple peers might update simultaneously, such as:
* Like/vote counts
* Number of views or interactions
Do not initialize counter fields by inserting a double value (e.g., `0.0`). This creates a register field, not a counter. Counter fields are automatically created when you first use `PN_INCREMENT` on them.
To use a counter, apply the `PN_INCREMENT` operation directly on the field. If the field doesn't exist, it will be created as a counter with the increment value. If you need to create a document first, insert it without the counter field or with other fields only:
```swift Swift theme={null}
// Create document without counter field
let product = [
"_id": "123",
"updatedBy": "abc123"
]
await ditto.store.execute(
query: """
INSERT INTO COLLECTION products
INITIAL DOCUMENTS (:product)
""",
arguments: [ "product": product ])
// Then increment the counter (creates it if doesn't exist)
await ditto.store.execute(
query: """
UPDATE products
APPLY in_stock PN_INCREMENT BY 5.0
WHERE _id = '123'
""")
```
```kotlin Kotlin theme={null}
// Create document without counter field
var product = mapOf(
"_id" to "123",
"updatedBy" to "abc123"
)
ditto.store.execute("""
INSERT INTO products
INITIAL DOCUMENTS (:product)
""",
mapOf("product", product))
// Then increment the counter (creates it if doesn't exist)
ditto.store.execute("""
UPDATE products
APPLY in_stock PN_INCREMENT BY 5.0
WHERE _id = '123'
""")
```
```javascript JS theme={null}
// Create document without counter field
const product = {
_id: "123",
updatedBy: "abc123"
};
await ditto.store.execute(`
INSERT INTO products
INITIAL DOCUMENTS (:product)`,
{ product });
// Then increment the counter (creates it if doesn't exist)
await ditto.store.execute(`
UPDATE products
APPLY in_stock PN_INCREMENT BY 5.0
WHERE _id = '123'`);
```
```java Java theme={null}
// Create document without counter field
Map product = new HashMap<>();
product.put("_id", "123");
product.put("updatedBy", "abc123");
DittoQueryResult result = (DittoQueryResult) ditto.store.execute(
"INSERT INTO products INITIAL DOCUMENTS (:product)",
Collections.singletonMap("product", product)
);
// Then increment the counter (creates it if doesn't exist)
ditto.store.execute(
"UPDATE products APPLY in_stock PN_INCREMENT BY 5.0 WHERE _id = '123'"
);
```
```csharp C# theme={null}
// Create document without counter field
var args = new Dictionary();
args.Add("product", new { _id = "123", updatedBy = "abc123" });
await ditto.Store.ExecuteAsync(
"INSERT INTO products INITIAL DOCUMENTS (:product)",
args);
// Then increment the counter (creates it if doesn't exist)
await ditto.Store.ExecuteAsync(
"UPDATE products APPLY in_stock PN_INCREMENT BY 5.0 WHERE _id = '123'");
```
```cpp C++ theme={null}
// Create document without counter field
std::map product;
product["_id"] = "123";
product["updatedBy"] = "abc123";
std::map args;
args["product"] = product;
auto result = ditto.get_store().execute(
"INSERT INTO products INITIAL DOCUMENTS (:product)",
args).get();
// Then increment the counter (creates it if doesn't exist)
ditto.get_store().execute(
"UPDATE products APPLY in_stock PN_INCREMENT BY 5.0 WHERE _id = '123'"
).get();
```
```rust Rust theme={null}
// Create document without counter field
let query_result = ditto
.store()
.execute_v2((
"INSERT INTO products INITIAL DOCUMENTS (:product)",
serde_json::json!({
"product": {
"_id": "123",
"updatedBy": "abc123"
}
}),
)).await?;
// Then increment the counter (creates it if doesn't exist)
ditto.store()
.execute_v2((
"UPDATE products APPLY in_stock PN_INCREMENT BY 5.0 WHERE _id = '123'",
serde_json::json!({}),
)).await?;
```
```dart Dart theme={null}
// Create document without counter field
const product = {
"_id": "123",
"updatedBy": "abc123"
};
await ditto.execute("""
INSERT INTO products
INITIAL DOCUMENTS (:product)""",
{"product": product},
);
// Then increment the counter (creates it if doesn't exist)
await ditto.execute("""
UPDATE products
APPLY in_stock PN_INCREMENT BY 5.0
WHERE _id = '123'""",
);
```
To update a counter, use the APPLY keyword followed by the field name and then
the `PN_INCREMENT` keyword followed by the value. To decrement a counter, use a
negative value.
```sql STRICT_MODE=false theme={null}
UPDATE products
APPLY in_stock PN_INCREMENT BY 1.0
SET updatedBy = 'def456'
WHERE _id = '123'
```
```sql STRICT_MODE=true theme={null}
UPDATE COLLECTION products (in_stock PN_COUNTER)
APPLY in_stock PN_INCREMENT BY 1.0
SET updatedBy = 'def456'
WHERE _id = '123'
```
You can then retrieve the latest value of a counter using a `SELECT` statement:
```sql STRICT_MODE=false theme={null}
SELECT * FROM products
WHERE _id = '123'
```
```sql STRICT_MODE=true theme={null}
SELECT * FROM COLLECTION products (in_stock PN_COUNTER)
WHERE _id = '123'
```
### **Default Value Operation**
Some data types can be set to a default value type using the `default()`
functional operator.
* `REGISTER` → `NULL`
* `AWMAP` → Empty Map `{}`
* `ATTACHMENT` → *NOT SUPPORTED*
* `PN_COUNTER` → *NOT SUPPORTED*
* `COUNTER` → *NOT SUPPORTED*
```sql 4.11+ theme={null}
field1 = default()
```
```sql <4.10 theme={null}
field1 -> default()
```
## Declaring Type Definition
With [strict mode](/dql/strict-mode) enabled, all fields
are treated as a register by default. When enabled, every field in a document
must match the collection definition exactly — including its CRDT type (e.g.,
map, register, counter).
Disabling strict mode enables new functionality: when set to
false, collection definitions are no longer required. SELECT queries will return
and display all fields by default.
![Type Definition Syntax Diagram]()
### Registers
A **`REGISTER`** is a data type in Ditto that stores a single scalar value and
uses last-write-wins merge strategy for handling conflicts.
Key characteristics of REGISTER:
* Stores primitive types (string, boolean) or JSON objects
* Last-write-wins conflict resolution ensures consistent values across peers
With `DQL_STRICT_MODE=false`, if you want to force a JSON Object to use a
REGISTER data type instead of a MAP in DQL, it must be specified explicitly.
```sql theme={null}
UPDATE COLLECTION orders (updatedAt REGISTER)
SET updatedAt = {
"datetime": "2025-02-28",
"authorId": "67c0faa40054d13a000c614a"
}
WHERE _id = 'my-id'
SELECT * FROM COLLECTION orders (updatedAt REGISTER) WHERE _id = 'my-id'
```
The results of the SELECT statement above would be:
```json theme={null}
{
"_id": "my-id"
"regionId": "01234",
"items": {
"shake": {...},
"fries": {...},
"burger": {...}
},
"updatedAt": {
"datetime": "2025-02-28",
"updatedBy": "67c0faa40054d13a000c614a"
}
}
```
If you need to remove a register map, you need to use the `UNSET` statement at the top level. Because a register map is treated the same as a scalar value (such as string, int), you operate on the entire object as a whole, similar to a JSON blob.
```sql theme={null}
UPDATE COLLECTION orders (updatedAt REGISTER)
UNSET updatedAt
WHERE _id = 'my-id'
```
You will receive an error if you attempt to `SET` or `UNSET` a nested key of a register using dot notation.
```sql theme={null}
UPDATE COLLECTION orders (updatedAt REGISTER)
SET updatedAt.datetime = "2025-04-28"
WHERE _id = 'my-id'
-- Unsupported DML operation on REGISTER field "updatedAt"
```
### Non-Registers
In 4.11+ and `DQL_STRICT_MODE=false`, collection definitions for non-registers are no longer required.
[Read more](/dql/strict-mode)
With `DQL_STRICT_MODE=true`, `REGISTER` is the default type in DQL. That means
that you need to specify the type definition when overriding with type `MAP`, `PN_COUNTER`, or
`ATTACHMENT` within your query.
## Document ID Constraints
### Document ID Requirements
Every document in Ditto must have a unique `_id` field that serves as the document's identifier. The following constraints apply:
* **Required**: Every document must have an `_id` field
* **Type**: The `_id` can be a string, number, or other scalar type
* **Uniqueness**: Each `_id` must be unique within its collection
* **Null Restriction**: `null` cannot be used as a document `_id` (enforced in SDK 5.0+)
**Example - Valid Document IDs:**
```sql DQL theme={null}
-- String ID
INSERT INTO cars VALUES ({"_id": "car-123", "color": "blue"})
-- Numeric ID
INSERT INTO cars VALUES ({"_id": 42, "color": "red"})
```
**Example - Invalid Document ID:**
```sql DQL theme={null}
-- This will fail in SDK 5.0+
INSERT INTO cars VALUES ({"_id": null, "color": "green"})
```
Starting in SDK version 5.0, attempting to use `null` as a document `_id` will result in an error. Earlier versions may have allowed this, but it should be avoided for forward compatibility.
DQL type definitions describe the schema of the documents within a specific
collection — defining the field types within the collection and specifying the
assigned data types for each field.
To explicitly declare the type definition as `non-REGISTER` type, add a prefix
of `COLLECTION` and the suffix of `(field1 data_type, field2 data_type, ...)` to
list the fields within the collection and their associated data types:
```sql DQL theme={null}
SELECT *
FROM COLLECTION your_collection_name (field1 MAP, field2 ATTACHMENT)
WHERE field1.rating > 100
```
In this syntax:
```sql DQL theme={null}
... COLLECTION your_collection_name (field1 data_type, field2 data_type, ...) ...
```
* `COLLECTION` declares that the collection has a type definition
* `your_collection_name` is the name of the collection from which you want to set a definition.
* `(field1 data_type, field2 data_type, ...)` specifies the data type of each field such as `REGISTER` , `MAP`, or `ATTACHMENT`
**SELECT with Definition**
```sql DQL theme={null}
SELECT *
FROM COLLECTION your_collection_name (field1 MAP, field2 ATTACHMENT)
```
**UPDATE with Definition**
```sql DQL theme={null}
UPDATE COLLECTION your_collection_name (field1 MAP, field2 ATTACHMENT)
SET ...
```
**INSERT with Definition**
```sql DQL theme={null}
INSERT INTO COLLECTION your_collection_name (field1 MAP, field2 ATTACHMENT)
DOCUMENTS (...)
```
**MAP Type Specifics**
The `MAP` (Add-Wins Map) contains fields with their own data type. Data types for these fields are defined using parentheses following the `MAP` keyword. For example, `MAP(sub1 data_type, sub2 data_type, ...)`:
```sql DQL theme={null}
... COLLECTION your_collection_name (map_name MAP(sub1 ATTACHMENT, sub2 MAP))
```
**Single MAP**
The syntax for a single `MAP` with all other fields type `REGISTER`:
```sql DQL theme={null}
... COLLECTION your_collection_name (field1 MAP)
```
**Single ATTACHMENT**
The syntax for a single `ATTACHMENT` with all other fields type `REGISTER`:
```sql DQL theme={null}
... COLLECTION your_collection_name (field1 ATTACHMENT)
```
**MAP and ATTACHMENT**
The syntax for a single `MAP` and a single `ATTACHMENT` with all other fields type `REGISTER`:
```sql DQL theme={null}
... COLLECTION your_collection_name (field1 MAP, field2 ATTACHMENT)
```
**Deeply Embedded MAP**
### Disable Strict Mode
In 4.11+ and `DQL_STRICT_MODE=false`, collection definitions are no longer required.
[Read more](/dql/strict-mode)
The syntax for a document hierarchy of depth two — a single `MAP` nested with another `MAP` — with all other fields type `REGISTER`:
```sql DQL theme={null}
... COLLECTION your_collection_name (field1 MAP(sub1 MAP))
```
The syntax for a document hierarchy of depth four with all other fields type `REGISTER`:
```sql DQL theme={null}
... COLLECTION your_collection_name (field1 MAP(sub1 MAP(s_sub1 MAP(s_s_sub1 MAP))))
```
# UPDATE
Source: https://docs.ditto.live/dql/update
The `UPDATE` operation modifies the content of existing documents in a collection.
With an `UPDATE` statement, you can update specific fields within the documents based on specified conditions:
```sql DQL theme={null}
UPDATE your_collection_name
[APPLY field INCREMENT BY value | field RESTART [WITH value], ...]
[SET field1 = value1, field2.subfield = value2, ...]
[UNSET field3, field4, ...]
[WHERE condition]
[ORDER BY expression [ASC|DESC], ...]
[LIMIT limit_value]
[OFFSET offset_value]
[RETURNING projection]
```
![UPDATE Syntax Diagram]()
In this syntax:
* `your_collection_name` is the name of the collection in which you want to update data.
* `APPLY` clause is used for counter operations (optional)
* `SET` clause specifies fields to be updated and their corresponding new values (optional)
* `UNSET` clause specifies fields to be deleted (optional)
* `WHERE` clause filters which documents to update (optional)
* `ORDER BY` clause controls the order in which documents are updated (optional)
* `LIMIT` clause restricts the number of documents updated (optional)
* `OFFSET` clause skips a number of documents before updating (optional)
* `RETURNING` clause returns fields or expressions taken from the updated documents (optional). See [RETURNING](/dql/returning)
At least one of `APPLY`, `SET`, or `UNSET` must be specified in an UPDATE statement.
## Basic UPDATE
Here is an example of a basic UPDATE operation:
```sql DQL theme={null}
UPDATE your_collection_name
SET field1 = 'blue'
WHERE _id = '123'
```
## **UPDATE Multiple Fields**
The following snippet shows an example of using UPDATE to set multiple fields:
```sql DQL theme={null}
UPDATE your_collection_name
SET
field1 = 'blue',
field2 = 0
WHERE _id = '123'
```
![SET Clause Syntax Diagram]()
## UPDATE with Nested Fields
Dot notation is available in 4.11 and later, with DQL\_STRICT\_MODE=false. [Read more](/dql/strict-mode)
When updating fields nested in a `MAP`, specify the field-value pairs you want to update.
For `MAP` syntax, see Ditto Query Language > Types and Definitions > [Map Operations](/dql/types-and-definitions#map-operations).
```sql 4.11+ theme={null}
UPDATE your_collection_name
SET
field2.sub1 = 2,
field2.sub2 = 'spring'
WHERE field1 = 'red'
```
```sql <4.10 theme={null}
-- Arrow functions (->) have been removed in v5
UPDATE COLLECTION your_collection_name (field2 MAP)
SET
field2 -> (
sub1 = 2,
sub2 = 'spring'
)
WHERE field1 = 'red'
```
## UPDATE with deserialize\_json
Starting with SDK 4.8, you can use the `deserialize_json()` function in `UPDATE` statements to set fields from JSON-serialized strings. This is useful when you receive data as JSON strings (for example, from an API response) and want to update specific fields on existing documents.
### Update a single field from a JSON string
You can deserialize a JSON string and use it to set a field value:
```swift Swift theme={null}
await ditto.store.execute(
query: """
UPDATE cars
SET properties = deserialize_json(:jsonData)
WHERE _id = '123'
""",
arguments: [ "jsonData": "{\"color\": \"red\", \"mileage\": 5000}" ])
```
```kotlin Kotlin theme={null}
ditto.store.execute("""
UPDATE cars
SET properties = deserialize_json(:jsonData)
WHERE _id = '123'
""",
mapOf("jsonData" to "{\"color\": \"red\", \"mileage\": 5000}"))
```
```javascript JS theme={null}
await ditto.store.execute(`
UPDATE cars
SET properties = deserialize_json(:jsonData)
WHERE _id = '123'`,
{ jsonData: '{"color": "red", "mileage": 5000}' });
```
```java Java theme={null}
ditto.store.execute(
"UPDATE cars SET properties = deserialize_json(:jsonData) WHERE _id = '123'",
Map.of("jsonData", "{\"color\": \"red\", \"mileage\": 5000}"));
```
```csharp C# theme={null}
var args = new Dictionary();
args.Add("jsonData", "{\"color\": \"red\", \"mileage\": 5000}");
await ditto.Store.ExecuteAsync(
"UPDATE cars"
+ " SET properties = deserialize_json(:jsonData)"
+ " WHERE _id = '123'",
args);
```
```cpp C++ theme={null}
std::map args;
args["jsonData"] = "{\"color\": \"red\", \"mileage\": 5000}";
ditto.get_store().execute(
"UPDATE cars SET properties = deserialize_json(:jsonData) WHERE _id = '123'",
args).get();
```
```rust Rust theme={null}
let query_result = ditto
.store()
.execute_v2((
"UPDATE cars SET properties = deserialize_json(:jsonData) WHERE _id = '123'",
serde_json::json!({
"jsonData": "{\"color\": \"red\", \"mileage\": 5000}"
}),
)).await?;
```
```dart Dart theme={null}
await ditto.store.execute("""
UPDATE cars
SET properties = deserialize_json(:jsonData)
WHERE _id = '123'""",
arguments: {"jsonData": "{\"color\": \"red\", \"mileage\": 5000}"},
);
```
### Update multiple fields from a JSON string
You can also combine `deserialize_json()` with other SET assignments in the same UPDATE:
```swift Swift theme={null}
await ditto.store.execute(
query: """
UPDATE cars
SET
color = deserialize_json(:colorJson),
mileage = 6000
WHERE _id = '123'
""",
arguments: [ "colorJson": "\"red\"" ])
```
```kotlin Kotlin theme={null}
ditto.store.execute("""
UPDATE cars
SET
color = deserialize_json(:colorJson),
mileage = 6000
WHERE _id = '123'
""",
mapOf("colorJson" to "\"red\""))
```
```javascript JS theme={null}
await ditto.store.execute(`
UPDATE cars
SET
color = deserialize_json(:colorJson),
mileage = 6000
WHERE _id = '123'`,
{ colorJson: '"red"' });
```
```java Java theme={null}
ditto.store.execute(
"UPDATE cars SET color = deserialize_json(:colorJson), mileage = 6000 WHERE _id = '123'",
Map.of("colorJson", "\"red\""));
```
```csharp C# theme={null}
var args = new Dictionary();
args.Add("colorJson", "\"red\"");
await ditto.Store.ExecuteAsync(
"UPDATE cars"
+ " SET color = deserialize_json(:colorJson), mileage = 6000"
+ " WHERE _id = '123'",
args);
```
```cpp C++ theme={null}
std::map args;
args["colorJson"] = "\"red\"";
ditto.get_store().execute(
"UPDATE cars SET color = deserialize_json(:colorJson), mileage = 6000 WHERE _id = '123'",
args).get();
```
```rust Rust theme={null}
let query_result = ditto
.store()
.execute_v2((
"UPDATE cars SET color = deserialize_json(:colorJson), mileage = 6000 WHERE _id = '123'",
serde_json::json!({
"colorJson": "\"red\""
}),
)).await?;
```
```dart Dart theme={null}
await ditto.store.execute("""
UPDATE cars
SET
color = deserialize_json(:colorJson),
mileage = 6000
WHERE _id = '123'""",
arguments: {"colorJson": "\"red\""},
);
```
## Deleting Fields
`UNSET` is available in 4.11 and later.
![UNSET Clause Syntax Diagram]()
In Ditto, fields need to be marked as "deleted" for other peers to know the field has been removed.
* When unsetting a `MAP`, all children data types are iteratively unset.
* Fields that are unset are ignored during subsequent DQL statements.
* Calling `UNSET` on a large number of dynamically generated fields (for
example, dynamically created keys in a CRDT map) may cause performance to
degrade due to metadata accumulation over time. Benchmarks for this will
vary depending on your dataset size and query cardinality. You can
mitigate this accumulation by calling `UNSET` on a parent field
(or deleting the document itself).
```sql 4.11+ theme={null}
UPDATE your_collection_name
UNSET field_name
WHERE _id = '123'
```
```sql <4.10 theme={null}
-- Arrow (->) and tombstone() functions have been removed in v5
UPDATE your_collection_name
SET your_field_name -> tombstone()
WHERE _id = '123'
```
## APPLY Clause for Counters
Counters are available in SDK 4.11+ (PN\_COUNTER) and 4.14+ (COUNTER). See [Counter Types](/dql/types-and-definitions#counter-settable-counter) for details.
The `APPLY` clause is used to perform counter operations on COUNTER or PN\_COUNTER fields:
```sql DQL theme={null}
UPDATE COLLECTION collection_name (counter_field COUNTER)
APPLY counter_field INCREMENT BY value
WHERE [condition]
```
![APPLY Clause Syntax Diagram]()
### INCREMENT BY
Increment or decrement a counter (use negative values to decrement):
```sql DQL theme={null}
-- Increment counter
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count INCREMENT BY 5
WHERE _id = '123'
-- Decrement counter
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count INCREMENT BY -3
WHERE _id = '123'
```
### RESTART (COUNTER only)
Reset a counter to zero or set it to a specific value:
```sql DQL theme={null}
-- Reset to zero
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count RESTART
WHERE _id = '123'
-- Set to specific value
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count RESTART WITH 100
WHERE _id = '123'
```
### Combining APPLY with SET
You can combine counter operations with regular field updates:
```sql DQL theme={null}
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count INCREMENT BY 10
SET lastUpdated = '2025-12-16', updatedBy = 'user123'
WHERE _id = '123'
```
## ORDER BY, LIMIT, and OFFSET
UPDATE statements support `ORDER BY`, `LIMIT`, and `OFFSET` clauses to control which documents are updated and in what order.
### ORDER BY
Control the order in which documents are processed for update:
```sql DQL theme={null}
-- Update oldest items first
UPDATE inventory
SET status = 'archived'
WHERE lastActivity < '2024-01-01'
ORDER BY lastActivity ASC
LIMIT 100
```
### LIMIT
Restrict the number of documents updated:
```sql DQL theme={null}
-- Update only the first 10 matching documents
UPDATE products
SET featured = true
WHERE category = 'electronics' AND rating > 4.5
LIMIT 10
```
### OFFSET
Skip a number of documents before updating:
```sql DQL theme={null}
-- Skip first 20 documents, then update next 10
UPDATE tasks
SET priority = 'low'
WHERE status = 'pending'
ORDER BY createdAt DESC
OFFSET 20
LIMIT 10
```
### Combining ORDER BY, LIMIT, and OFFSET
These clauses work together to provide fine-grained control:
```sql DQL theme={null}
-- Update the 10 most expensive items after the first 5
UPDATE products
SET discount = 0.2
WHERE category = 'luxury'
ORDER BY price DESC
OFFSET 5
LIMIT 10
```
`ORDER BY`, `LIMIT`, and `OFFSET` are evaluated after the `WHERE` clause filters documents. This means:
1. Documents are first filtered by the WHERE condition
2. Results are then ordered by ORDER BY
3. OFFSET skips documents from the ordered results
4. LIMIT restricts how many documents are updated
## UPDATE with RETURNING
`RETURNING` is available in SDK 5.1 and later.
Add a `RETURNING` clause to get back the updated documents, rather than just the IDs of the documents that were changed. The documents are projected as they are *after* the `APPLY`, `SET` and `UNSET` mutators have been applied:
```sql DQL theme={null}
-- Return the complete updated documents
UPDATE cars
SET color = 'red'
WHERE color = 'blue'
RETURNING *
-- Return selected fields, with their new values
UPDATE cars
SET color = 'red'
WHERE color = 'blue'
RETURNING _id, color
-- Return the new counter value after an APPLY
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count INCREMENT BY 5
WHERE _id = '123'
RETURNING _id, stock_count
```
For the full projection rules, aggregate support and the restrictions that apply, see [RETURNING](/dql/returning).
# Virtual Collections
Source: https://docs.ditto.live/dql/virtual-collections
Virtual collections for monitoring and reporting in DQL.
A number of virtual collections are available in the system namespace to monitor the DQL environment, peer information, and provide statement diagnostics. They exist solely for local inspection and debugging, providing runtime visibility into the current peer's state, active connections, and query execution details.
These collections are local-only and are not synced to other peers or the cloud.
## system:active\_requests
`ACTIVES` is an alias for `ACTIVE_REQUESTS`.
Reports information on currently executing requests. The information is dynamic and will change as request processing takes place.
```sql theme={null}
SELECT * FROM system:active_requests
```
```json theme={null}
{
"_id": "d43c3bc5-2b79-403c-b5dc-5619f731c77b",
"app_id": "baf481c4-58be-46f1-8a93-eedf382dc371",
"featureFlags": "0x3a",
"plan": {
"#operator": "sequence",
"children": [
{
"#operator": "scan",
"#stats": {
"phaseTimes": {
"exec": 2405,
"recv": 392997
}
},
"collection": "active_requests",
"datasource": "system"
}
]
},
"queryType": "select",
"requestType": "SDK",
"state": "executing",
"text": "select * from system:active_requests",
"times": {
"elapsed": 1058291,
"parse": 170228,
"plan": 81813,
"start": "2026-01-06T16:27:23.069+00:00"
}
}
```
The output includes dynamic timing information and the query execution plan which can be useful when diagnosing issues.
Some points to note about the content:
1. When executing in an environment with multiple subscription servers, only data from the servicing server is reported.
2. It is expected to see any query retrieving data from `active_requests` in the output in "executing" state.
3. Operators will record information whilst processing so it is normal to see changes in the content for `active_requests`.
4. Zero values (times, counts) are omitted from the output.
## system:all\_collections
Reports all collections in all namespaces.
```sql theme={null}
SELECT * FROM system:all_collections
```
```json theme={null}
{
"_id": "default:__presence",
"datasource": "default",
"name": "__presence"
}
{
"_id": "system:active_requests",
"alias": "actives",
"datasource": "system",
"name": "active_requests"
}
{
"_id": "system:all_collections",
"datasource": "system",
"name": "all_collections"
}
{
"_id": "system:collections",
"datasource": "system",
"name": "collections"
}
{
"_id": "system:data_sync_info",
"datasource": "system",
"name": "data_sync_info"
}
{
"_id": "system:dual",
"datasource": "system",
"name": "dual"
}
{
"_id": "system:indexes",
"datasource": "system",
"name": "indexes"
}
{
"_id": "default:my_collection",
"datasource": "default",
"name": "my_collection"
}
{
"_id": "system:request_history",
"datasource": "system",
"name": "request_history"
}
{
"_id": "system:system_info",
"datasource": "system",
"name": "system_info"
}
{
"_id": "default:t1",
"datasource": "default",
"name": "t1"
}
{
"_id": "system:transports_info",
"datasource": "system",
"name": "transports_info"
}
{
"_id": "system:vitals",
"datasource": "system",
"name": "vitals"
}
```
## system:collections
Reports all collections in the `default` namespace.
```sql theme={null}
SELECT * FROM system:collections
```
```json theme={null}
{
"_id": "default:t1",
"datasource": "default",
"name": "t1"
}
{
"_id": "default:__presence",
"datasource": "default",
"name": "__presence"
}
{
"_id": "default:my_collection",
"datasource": "default",
"name": "my_collection"
}
```
## system:data\_sync\_info
Reports statistics related to data sync operations.
```sql theme={null}
SELECT * FROM system:data_sync_info
```
```json theme={null}
{
"_id": "pkAocCgkMCRsRhue6kCR0eIYFYxT3wqVl5-BxJLqCMVgF7H9WOL5I",
"documents": {
"last_update_received_time": -1,
"sync_session_status": "Not Connected",
"synced_up_to_local_commit_id": 545
},
"is_ditto_server": true
}
```
## system:indexes
Reports the user defined indexes (Ditto SDK only).
```sql theme={null}
SELECT * FROM system:indexes
```
```json theme={null}
{
"_id": "my_collection.ix_a",
"collection": "my_collection",
"fields": [
{
"direction": "asc",
"key": [
"a"
]
}
]
}
```
**Format Change in SDK 5.0+**: The index information now includes the `direction` field for each index key, indicating whether the index is sorted in ascending (`"asc"`) or descending (`"desc"`) order. Earlier versions did not include this field.
## system:shared\_statements
Available in SDK version 5.0+
Reports information about statements in the shared statement cache. The shared statement cache stores prepared query plans to avoid re-planning identical queries, improving query performance.
```sql theme={null}
SELECT * FROM system:shared_statements
```
This virtual collection provides visibility into which queries are being cached and can help diagnose query planning behavior.
**Related Configuration:**
* Use the `#reprepare` [directive](/dql/directives) to bypass the cache for specific queries
* Set `dql_default_directives` with `#reprepare` to control caching behavior globally
See [Directives - Default Directives](/dql/directives#default-directives) for more information on controlling statement cache behavior.
## system:dual
Contains a single document with a single field. It can be used to execute and test DQL statements.
```sql theme={null}
SELECT * FROM system:dual
```
```json theme={null}
{
"_id": "1",
"dummy": "X"
}
```
## system:request\_history
Records information about requests that have completed. This is the same basic information as reported by `active_requests`. Information is only recorded when some aspect of the request execution meets the user-configurable qualifiers. (See [configuration](#configuration).)
This is an in-memory cache and is *not* persisted across processes.
```sql theme={null}
SELECT * FROM system:request_history
```
```json theme={null}
{
"_id": "98752fca-f1c7-4970-86ad-127a36712b12",
"app_id": "baf481c4-58be-46f1-8a93-eedf382dc371",
"featureFlags": "0x3a",
"plan": {
"#operator": "sequence",
"children": [
{
"#operator": "indexScan",
"#stats": {
"documentsOut": 100,
"phaseTimes": {
"exec": 1175507,
"recv": 6743617,
"send": 4049802
}
},
"alias": "my_collection",
"collection": "my_collection",
"datasource": "default",
"desc": {
"covering": true,
"index": "ix_a",
"spans": [
[
{
"index_key": {
"direction": "asc",
"include_missing": true,
"key": [
"a"
]
},
"range": {
"high": {
"included": true,
"value": 0
},
"low": {
"included": true,
"value": 0
}
}
}
]
]
}
},
{
"#operator": "filter",
"#stats": {
"documentsIn": 100,
"documentsOut": 100,
"phaseTimes": {
"exec": 449139,
"send": 3478062
}
},
"condition": "(`my_collection`.`a` = 0)"
},
{
"#operator": "groupBy",
"#stats": {
"documentsIn": 100,
"documentsOut": 1,
"phaseTimes": {
"exec": 231392,
"recv": 11864674,
"send": 30878
}
},
"aggregates": [
{
"expr": "true",
"name": "count(true)"
}
],
"keys": []
},
{
"#operator": "projection",
"#stats": {
"documentsIn": 1,
"documentsOut": 1,
"phaseTimes": {
"exec": 8115,
"recv": 12222233,
"send": 33743
}
},
"projections": [
{
"alias": "($1)",
"expression": "count(true)"
}
]
}
]
},
"queryType": "select",
"requestType": "SDK",
"resultCount": 1,
"state": "completed",
"text": "select count(*) from my_collection where a = 0",
"times": {
"elapsed": 14476181,
"parse": 361256,
"plan": 939337,
"start": "2026-01-06T16:33:35.709+00:00"
},
"~qualifier": "threshold"
}
```
The output includes complete timing information and the query execution plan, along with the qualifier that resulted in the request information being captured, all of which can be useful when diagnosing issues.
`request_history` has a configurable maximum number of documents it will keep with oldest being dropped when attempting to add beyond the limit. (See [configuration](#configuration).)
Some points to note about the content:
1. When executing in an environment with multiple subscription servers, only data from the servicing server is reported.
2. There should not be any requests in `request_history` in "executing" state
1. The state should reflect the final state of the request: successful completion or failure.
3. Zero values (times, counts) are omitted from the output.
The cache content may be (selectively) cleared with a `DELETE` statement.
See also: [Diagnosing problems](#diagnosing-problems)
## SYSTEM:SYSTEM\_INFO
Reports a system information dump's contents.
```sql theme={null}
SELECT * FROM system:system_info
```
```text theme={null}
{
"key": "identity_service_metadata",
"namespace": "auth",
"timestamp": 1767716539,
"value": {
"inner": {
"foo": "bar"
},
"user_id": "dev@ditto.live",
"values": [
1,
2,
3
]
}
}
[...]
{
"key": "ditto_sdk_version",
"namespace": "core",
"timestamp": 1767716539,
"value": "0.0.0"
}
[...]
{
"key": "fs_usage_auth",
"namespace": "core",
"timestamp": 1767718398,
"value": 16102
}
[...]
```
## SYSTEM:TRANSPORTS\_INFO
Reports available transports information.
```sql theme={null}
SELECT * FROM system:transports_info
```
```json theme={null}
{
"_id": "discovery_hint",
"value": "Q2CG092BKw"
}
```
## SYSTEM:VITALS
Reports overview statistics about the query engine. It is typically the starting point for general query engine diagnostics.
When executing in an environment with multiple subscription servers, only data from the servicing server is reported.
```sql theme={null}
SELECT * FROM system:vitals
```
```json theme={null}
{
"_id": "1",
"execution_time": {
"max": 5766201,
"mean": 3194134,
"median": 3535687,
"min": 1305173
},
"failed": 1,
"inserts": 11,
"other": 2,
"parse_time": {
"max": 220142,
"mean": 146785,
"median": 166271,
"min": 112400
},
"plan_time": {
"max": 84748,
"mean": 72935,
"median": 73898,
"min": 63048
},
"requests": 31,
"selects": 16,
"updates": 1
}
```
Some points to note about the content:
1. The histograms decay over a 15 minute interval.
2. "other" is all statements not covered by the individual metrics, e.g. `ALTER SYSTEM`
3. All timings are in nanoseconds
## Configuration
Where appropriate, the virtual collections content is determined by configuration settings allowing a user some control over what information is available.
### Request History
The [system:request\_history](#system%3Arequest-history) collection's size is determined by the `DQL_REQUEST_HISTORY_SIZE` system parameter, defaulting to 4096, indicating the number of entries to retain. The larger the size and the larger the statements captured, the greater the memory use.
```sql theme={null}
ALTER SYSTEM SET dql_request_history_size = 5000
```
Which statements qualify for inclusion in the `system:request_history` collection is determined by the `DQL_REQUEST_HISTORY_QUALIFIERS` system parameter which can be set to an object listing all desired qualifiers. Qualifiers not included in the object are removed (or reset).
The following qualifiers can be set:
1. `threshold` - the total execution time in milliseconds over which qualifies the request for inclusion. This is set by default to 1000 (1 second) and is always active. It can be set to 0 to include all statements.
2. `failed` - if a statement's execution fails. `true` by default.
3. `system_delete` - any delete statement run against a `system` namespace collection. `true` by default.
4. `mutation` - if a statement is a mutation of any sort. Not active (`false`) by default.
```sql theme={null}
ALTER SYSTEM SET dql_request_history_qualifiers = { "threshold": 500, "failed": false }
```
### Slow Request Warnings
Ditto can periodically log a warning to the Ditto log for any DQL request that is still running after a configurable threshold, containing the same information reported by [system:active\_requests](#systemactive_requests). This is particularly useful on Small Peers, where querying `system:active_requests` live isn't always practical, and helps distinguish a "hanging" request from one that's simply processing a large or slow statement. This is controlled by the `DQL_SLOW_REQUEST_WARN_SECONDS` system parameter, defaulting to 60 seconds. The warning repeats at this interval for as long as the request keeps running.
```sql theme={null}
ALTER SYSTEM SET dql_slow_request_warn_seconds = 30
```
Set to `0` to disable slow-request warnings.
For more information, see [ALTER SYSTEM](/dql/alter-system#dql_slow_request_warn_seconds).
### Request Timeout
The `DQL_REQUEST_TIMEOUT_SECONDS` system parameter limits how long, in seconds, a DQL request is permitted to run before it is cancelled, defaulting to `0` (no limit).
```sql theme={null}
ALTER SYSTEM SET dql_request_timeout_seconds = 45
```
Cancellation is signalled asynchronously to the execution pipeline rather than interrupting the currently executing operator, so it does not guarantee that execution stops immediately once the limit is reached.
For more information, see [ALTER SYSTEM](/dql/alter-system#dql_request_timeout_seconds).
## Diagnosing problems
### Profile analysis
The profile (`#stats` elements added to the plan) information permits assessment of where in the execution the time was spent and is therefore useful in directing next steps.
`request_history` entries show how many results the statement and each of its operators processed. This may give insight into what may need to be changed in a statement.
Analysis of the profile from multiple runs of the same statement may highlight things like selectivity-skew in filter values.
Any definition of "high" times should be with respect to the volume of data processed.
Some possible indications from profile analysis include:
1. Stalled execution: If a request appears in the `active_requests` output but doesn't appear to be making any progress it likely indicates an issue with the earliest (apart from `sequence`) operator in the plan.
1. Commonly indicated by unchanging document counts anywhere in the profile.
2. Excessive processing: An operator with a high document count potentially indicates this.
1. Possibly a filter isn't as selective as thought, etc.
2. It may also show where "dam" operators are requiring all prior plan processing to complete before the remainder of the plan can run.
1. e.g. a `GROUP BY` needs all input records before it can begin emitting the groups.
3. Data access issues: Underlying collection access issues may be indicated by high scan operator receive times.
1. High `scan` or `fetch` times are potentially indicative of collection access issues.
2. High index scan receive times are potentially indicative of index access issues.
4. Selectivity issues: Indications of index selectivity as compared to the entire predicate's selectivity may be highlighted with a large number of documents being excluded by the `filter` operator.
1. The difference in document in & out counts for the `filter` operator indicate the filtering taking place.
1. A large amount of filtering may not indicate a problem if there are filters that cannot be applied by index scans.
2. A large amount of filtering may indicate the query would benefit from different or additional indexes.
5. Memory requirements: Document counts for operators that must process the entire prior pipeline - such as sorting and grouping - may give an indication of which statements will have larger memory requirements.
Further:
1. Bottlenecks downstream in a plan may be indicated by a high operator `send` time.
1. This may be observed for any operator and attention should be focused on later operators.
2. Bottlenecks upstream in a plan may be indicated by a high operator `recv` time.
1. This may be observed for any operator and attention should be focused on earlier operators.
3. Possible problems in an operator may be highlighted by a high `exec` time.
1. This ***must*** be considered in conjunction with the number of documents being processed.
2. Operator issues should be reported via support channels.
### SYSTEM:ACTIVE\_REQUESTS & SYSTEM:REQUEST\_HISTORY
When initially developing DQL statements the [explain](/dql/explain) statement is used to determine the execution plan a query will use. Once deployed it may not always be possible to revert to examination of such a plan or there might be concern that the plan has materially changed. The `system:active_requests` and `system:request_history` collections contain[1](#diagnose1) instances of the actual execution plan used by a request, annotated with timing and document count information. These can be used to discover where in the execution a statement is taking time and can highlight things like unexpectedly low selectivity of filters or failure to find a suitable index.
Sometimes it may be possible to execute a statement directly when diagnosing issues. The `PROFILE` keyword appends the `request_history` entry (without having to pass qualification) to the statement's results. This allows dynamic access to all the same information as is included in `request_history` without having to capture a live instance of its execution.
```sql DQL theme={null}
PROFILE SELECT make FROM cars WHERE year > 2000
```
```json Output theme={null}
{
"make": "Honda"
}
{
"~request_profile": {
"_id": "da44cc08-4df1-4599-a52f-0d91a6728008",
"app_id": "baf481c4-58be-46f1-8a93-eedf382dc371",
"featureFlags": "0x3a",
"plan": {
"#operator": "sequence",
"children": [
{
"#operator": "scan",
"#stats": {
"documentsOut": 1,
"phaseTimes": {
"exec": 27852,
"recv": 1971169,
"send": 103113
}
},
"alias": "cars",
"collection": "cars",
"datasource": "default"
},
{
"#operator": "filter",
"#stats": {
"documentsIn": 1,
"documentsOut": 1,
"phaseTimes": {
"exec": 36368,
"send": 58399
}
},
"condition": "(`cars`.`year` > 2000)"
},
{
"#operator": "projection",
"#stats": {
"documentsIn": 1,
"documentsOut": 1,
"phaseTimes": {
"exec": 16981,
"recv": 2229883,
"send": 53440
}
},
"projections": [
{
"alias": "make",
"expression": "`cars`.`make`"
}
]
}
]
},
"queryType": "select",
"requestType": "SDK",
"resultCount": 1,
"state": "completed",
"text": "PROFILE SELECT make FROM cars WHERE year > 2000",
"times": {
"elapsed": 4596233,
"parse": 387966,
"plan": 1092304,
"start": "2026-01-07T14:03:45.602+00:00"
}
}
}
```
Producer operators are executed in parallel with their consumers and may show a much higher `"documentsOut"` count than the consumer's `"documentsIn"`. For completed requests, this is an indication that the consumer has stopped processing early and has not processed all of the documents sent by the producer. This might be perfectly normal, for instance when a LIMIT operator has hit the document count and has therefore stopped processing, or it may be that a downstream operator has encountered an error and has stopped the statement execution as a result.
```json theme={null}
[...]
"#operator": "sequence",
"children": [
{
"#operator": "scan",
"#stats": {
[1] "documentsOut": 11,
"phaseTimes": {
"exec": 21792,
"recv": 2595000,
"send": 594043
}
},
"collection": "all_collections",
"datasource": "system"
},
{
"#operator": "limit",
"#stats": {
[1] "documentsIn": 2,
"documentsOut": 1,
"phaseTimes": {
"exec": 9333,
"recv": 3547625,
"send": 145375
}
},
"limit": 1
}
]
},
[...]
```
1. Currently only available for [select](/dql/select) statements.
# Customizing System Settings
Source: https://docs.ditto.live/sdk/latest/sync/using-alter-system
This article provides a high-level overview of the `ALTER SYSTEM` Ditto Query Language (DQL) statement, as well as instructions on how to use it, once offered, to set, reset, and query peer-to-peer system settings.
`ALTER SYSTEM` is an advanced system performance tuning feature for configuring and retrieving *system settings* for your peer-to-peer mesh network.
For information on specific configs supported via `ALTER SYSTEM` reach out to the Ditto team member.
System settings consist of values the Ditto library uses to configure specific behaviors, such as timeouts, resource limits, and so on.
This article provides a high-level overview of the `ALTER SYSTEM` Ditto Query Language (DQL) statement, as well as instructions on how to use it, once offered, to set, reset, and query peer-to-peer system settings.
## Tasks Overview
The following table provides an overview of the various tasks you can perform with the `ALTER SYSTEM` statement:
| **Task** | **Query** |
| -------------------------------------------------------------------------------------------------------------- | -------------------- |
| Retrieve current system settings for all configurations. ([Retrieving Values](#retrieving-values)) | `SHOW ALL` |
| Retrieve current system settings for a specific configuration. ([Retrieving Values](#retrieving-values)) | `SHOW` |
| Change a specific system setting. ([Modifying Values](#modifying-values)) | `ALTER SYSTEM SET` |
| Reset system settings to default configurations. ([Resetting Values to Default](#resetting-values-to-default)) | `ALTER SYSTEM RESET` |
## Retrieving Values
Fetch current setting values for either the entire mesh network or a specific setting:
* To return system-wide configurations, use a `SHOW ALL` statement:
```mysql theme={null}
SHOW ALL
```
* To return only the value for a specific setting, pass the setting you want to retrieve a value for in a `SHOW` statement. For example:
```mysql theme={null}
SHOW REPLICATION_GC_STARTUP_DELAY_SECONDS
```
### Executing SHOW and SHOW ALL
To invoke your statement, call the Execute API method on the `ditto.store` namespace:
```swift Swift theme={null}
let result = try await ditto.store.execute(query: "SHOW ALL")
```
```java Java theme={null}
CompletableFuture result = ditto.getStore().execute("SHOW ALL");
```
### Modifying Values
To modify a system setting, use `ALTER SYSTEM SET`:
Similar to PostgresSQL, DQL supports both the `=` and `TO` syntax for setting system configurations.
Regardless of the syntax you use in your `ALTER SYSTEM SET` query, the documents Ditto returns contain the current value of the setting, identified by the setting name.
```mysql theme={null}
ALTER SYSTEM SET =
```
For example:
```mysql theme={null}
ALTER SYSTEM SET REPLICATION_GC_STARTUP_DELAY_SECONDS = 10
```
### Executing SET
Similar to the execution pattern recommended when configuring transports in your app, invoke your statement modifying system configurations immediately after initializing the `ditto` object. (See [Customizing Transports Configurations](./customizing-transport-configurations))
Ditto stores configuration settings modified by `ALTER SYSTEM` in memory rather than persisting them to disk.
So, while you can execute your statement to modify a setting at any point during your app's lifecycle, changing certain settings within your app may not result in immediate effects.
To invoke the `SET` statement, add a call to the Execute API method on the `ditto.store` namespace.
```swift Swift theme={null}
var result = try await ditto.store.execute(query: "ALTER SYSTEM SET example_parameter = 321")
result.items[0].value["example_parameter"] as? Int // 321
```
```java Java theme={null}
CompletableFuture result = ditto.getStore()
.execute("ALTER SYSTEM SET example_parameter = 321");
result.thenAccept(queryResult -> {
int value = (Integer) queryResult.getItems().get(0).getValue().get("example_parameter"); // 321
});
```
## Resetting Values to Default
To return peer-to-peer system configuration settings to default values, do either of the following:
* To reset all configuration settings:
For example:
```mysql theme={null}
ALTER SYSTEM RESET ALL
```
* To reset a specific configuration setting:
```mysql theme={null}
ALTER SYSTEM RESET
```
Once executed, Ditto returns a document containing current values, identified by their setting name.
## Troubleshooting
Ditto throws an error when an `ALTER SYSTEM` query operation fails to execute due to various issues, including:
* Syntax mistakes, such as a typo, causing parsing of the DQL statement to fail.
* Using an unrecognized setting name in the query (often due to a typo).
* Attempting to set a value outside the acceptable bounds for a setting in an `ALTER SYSTEM SET` query.
* An internal error occurred while processing the query.