> ## Documentation Index > Fetch the complete documentation index at: https://docs.ditto.live/llms.txt > Use this file to discover all available pages before exploring further. # Operator expressions > Operators perform a specific operation on the input values or expressions. ## Operator Precedence Understanding operator precedence is crucial for writing correct DQL expressions. Operators with higher precedence are evaluated first. Use parentheses to override the default precedence. | **Operator** | **Precedence** | **Description** | | -------------------------------- | -------------- | -------------------------------- | | `*`, `/`, `%` | 40 | Multiplication, division, modulo | | `+`, `-` (binary) | 30 | Addition, subtraction | | `>>`, `<<` | 22 | Bit shift operations | | `=`, `<`, `>`, `<=`, `>=`, `NEG` | 20 | Comparison operators | | `BETWEEN` | 20 | Range comparison | | `IN` | 20 | Membership test | | `LIKE`, `ILIKE`, `SIMILAR TO` | 19 | Pattern matching | | `IS` | 17 | Type/null checking | | `NOT` | 15 | Logical negation | | `AND` | 10 | Logical AND | | `XOR` | 7 | Logical exclusive OR | | `OR` | 5 | Logical OR | **Examples:** ```sql DQL theme={null} -- Without parentheses (precedence applies) SELECT * FROM products WHERE price > 100 AND category = 'electronics' OR featured = true -- Equivalent to: (price > 100 AND category = 'electronics') OR featured = true -- With parentheses (overriding precedence) SELECT * FROM products WHERE price > 100 AND (category = 'electronics' OR featured = true) -- Different result: price > 100 AND (category matches either condition) -- Arithmetic precedence SELECT price * 1.1 + 5 FROM products -- Equivalent to: (price * 1.1) + 5 (multiplication before addition) ``` When in doubt about precedence, use parentheses to make your intentions explicit. This improves code readability and prevents subtle bugs. ## Arithmetic Operators Arithmetic operators are used to perform operations on numeric values. | **Operator** | **Purpose** | **Example** | | :----------- | :--------------------------------------------- | :---------------------------- | | - | A unary operator that denotes a negative value | `WHERE field_name = -1` | | +, - | As binary operators, they add or subtract | `WHERE field_name + 1 > 2` | | \* | Multiply | `WHERE field_name * 2 > 2` | | / | Divide | `WHERE field_name / 2 > 1` | | % | Modulo | `WHERE field_name % 2 > 1` | | abs(x) | Absolute value | `WHERE abs(field_name) = 1` | | ceil(x) | Next whole number | `WHERE ceil(field_name) > 1` | | floor(x) | Previous whole number | `WHERE floor(field_name) > 1` | ## String Operators Performs various operations on `string` scalar types: | **Operator** | **Purpose** | **Example** | | :---------------------------------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | :-------------------------------------------------- | | \|\| | String concatenation | `WHERE field_name \|\| 'world' = 'hello world'` | | concat(str, ...) | String concatenation | `WHERE concat(field_name, 'world') = 'hello world'` | | contains(str, substr) | Returns true if `substr` is contained in `str`, otherwise returns false | `WHERE contains(field_name, 'hello')` | | starts\_with(str, substr) | Returns true if `str` starts with `substr`, otherwise returns false | `WHERE starts_with(field_name, 'hello')` | | ends\_with(str, substr) | Returns true if `str` ends with `substr`, otherwise returns false | `WHERE ends_with(field_name, 'world')` | | byte\_length(str) | Returns the length of `str` in bytes. Strings in Ditto are encoded in UTF-8, so a character can take up to 4 bytes. | `WHERE byte_length(field_name) > 4` | | char\_length(str) character\_length(str) | Returns the number of characters in `str`. | `WHERE char_length(field_name) > 4` | | str LIKE 'substr%' | Returns true if specific character string matches the specified pattern.
Available in SDK 4.10 onwards. | `WHERE field_name LIKE 'hel%'` | | str ILIKE 'substr%' | Returns true if specific character string matches the specified pattern. Case-insensitive variant of `LIKE`
Available in SDK 4.10 onwards. | `WHERE field_name ILIKE 'hel%'` | | str SIMILAR TO 'pattern' | Like `LIKE` but in addition to the `%` and `_` wildcards, also supports regular expression operators such as `\|` (alternatives), `[chars]` (character lists), and `\<`/`\>` (word boundaries).
Available in SDK 4.11 onwards. | `WHERE field_name SIMILAR TO '%(red\|blue)%'` | | regexp\_like(source, pattern\[, flags]) | Returns true if source matches the regular expression specified by pattern
Available in SDK 4.9 onwards. | `WHERE regexp_like(, , '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` |