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

# MongoDB Aggregation Pipeline to DQL

> A reference mapping MongoDB aggregation pipeline stages and operators to their DQL equivalents, including known gaps.

MongoDB uses a **multi-stage pipeline** model where documents flow through an ordered sequence of transformation stages. DQL uses a single **declarative `SELECT` statement** where filtering, projection, grouping, sorting, and limiting are all expressed at once. There are no stages to compose — the query optimizer decides execution order.

This means most MongoDB aggregations collapse to a single DQL `SELECT`. Operations with no DQL equivalent — joins, window functions, set algebra, advanced math, and several array accumulators — are noted as gaps throughout and collected in the [Known Gaps](#known-gaps) section.

## AI Agent Prompt

Use this prompt with any AI coding assistant to convert a MongoDB aggregation pipeline to DQL.

<Accordion title="Copy AI Agent Prompt (Click to Expand)">
  ```text theme={null}
  Convert the MongoDB aggregation pipeline I provide into a Ditto DQL query. Follow these rules exactly.

  ## Mental model
  MongoDB pipelines are multi-stage. DQL is a single declarative SELECT. Collapse all stages into one query:
    SELECT <projection> FROM <collection> WHERE <filter> GROUP BY <keys> HAVING <agg-filter> ORDER BY <sort> LIMIT <n> OFFSET <skip>

  ## Stage mappings

  $match        → WHERE clause. Full operator support: =, !=, <, >, <=, >=, BETWEEN, IN, NOT IN, LIKE, ILIKE, IS NULL, IS NOT NULL, IS MISSING, IS NOT MISSING.
  $sort         → ORDER BY field [ASC|DESC]
  $limit        → LIMIT n
  $skip         → OFFSET n
  $count        → SELECT COUNT(*) AS <field>

  $project (include)  → SELECT field1, field2
  $project (exclude)  → SELECT coll.*, MISSING field1, MISSING field2 FROM coll   ← requires table alias; bare SELECT * is a syntax error
  $project (compute)  → SELECT expr AS alias
  $addFields / $set   → SELECT coll.*, expr AS new_field FROM coll                ← same qualified-wildcard pattern
  $unset (top-level)  → SELECT coll.*, MISSING field FROM coll
  $unset (nested)     → SELECT object_unset(obj, 'field', true) AS obj FROM coll

  $group        → GROUP BY with aggregates: COUNT(*), COUNT(DISTINCT f), SUM(f), AVG(f), MIN(f), MAX(f), MEDIAN(f)
  $group $addToSet → COUNT(DISTINCT f) only — no array accumulation
  HAVING        → filter on aggregated results after GROUP BY

  $bucket / $bucketAuto →
    SELECT CASE WHEN f < b1 THEN 'l1' WHEN f < b2 THEN 'l2' ELSE 'other' END AS bucket, COUNT(*)
    FROM coll
    GROUP BY CASE WHEN f < b1 THEN 'l1' WHEN f < b2 THEN 'l2' ELSE 'other' END

  ## Expression mappings

  $cond         → CASE WHEN cond THEN a ELSE b END
  $switch       → CASE WHEN cond1 THEN r1 WHEN cond2 THEN r2 ELSE default END   (searched CASE — each branch is a boolean condition)
                → CASE expr WHEN v1 THEN r1 WHEN v2 THEN r2 ELSE default END     (simple CASE — use when all branches compare the same expression to scalar values)
  $ifNull       → ifnull(v1, v2) or coalesce(v1, v2, ...)

  $map          → ARRAY expr FOR v IN source END                          (SDK v5+)
  $filter       → ARRAY v FOR v IN source WHEN condition END              (SDK v5+)
  $arrayElemAt  → array[index]
  $size         → array_length(arr)
  $in (array membership test — is value in array?)  → array_contains(arr, val)
  $in ($match membership test — is field in a list?) → WHERE field IN ('a', 'b', 'c')
  ANY/EVERY     → ANY v IN arr SATISFIES condition END / EVERY v IN arr SATISFIES condition END

  $mergeObjects → object_concat(obj1, obj2, ...)
  $objectToArray → object_content(obj, 'fields')
  $arrayToObject → OBJECT name_expr:value_expr FOR v IN source END        (SDK v5+)
  $getField     → object_set / object_unset / object_keys / object_values

  ## Date functions
  $dateFromString   → date_cast(str, format)          e.g. date_cast('2024-01-15', 'YYYY-MM-DD')
  $dateToString     → date_format(date, format)        e.g. date_format(ts, 'YYYY-MM-DD')
  $dateAdd          → date_add(date, 'day', n)
  $dateSubtract     → date_sub(date, 'day', n)
  $dateDiff         → date_diff(date1, date2, 'day')
  $year/$month/$day → date_part(date, 'year') / date_part(date, 'mon') / date_part(date, 'day')
  $hour/$minute     → date_part(date, 'hour') / date_part(date, 'min')

  ## String functions
  $concat           → concat(s1, s2, ...) or s1 || s2
  $toLower/$toUpper → lower(s) / upper(s)
  $substr           → substr(s, start, len)           zero-based index
  $trim/$ltrim/$rtrim → `trim`(s) / ltrim(s) / rtrim(s)   note: trim requires backtick quoting
  $indexOfCP        → pos(s, substr)                  returns -1 if not found
  $strLenCP         → char_length(s)
  $strLenBytes      → byte_length(s)
  $split            → split(s, delim)
  $regexMatch       → regexp_like(s, pattern[, flags]) or LIKE / ILIKE / SIMILAR TO

  ## Type and cast
  $type          → type(x)        returns: boolean, string, integer, float, object, array, binary, null, missing
  $toString etc. → cast(v, 'string') / cast(v, 'integer') / cast(v, 'float') / cast(v, 'boolean')
                 also: CAST(v AS type) syntax

  ## Arithmetic operators
  MongoDB expression operators map to native DQL operators — use them directly in SELECT projections and WHERE clauses:
  $add        → +
  $subtract   → -
  $multiply   → *
  $divide     → /
  $mod        → %
  $abs        → abs(x)
  $ceil       → ceil(x)
  $floor      → floor(x)

  Comparison expression operators ($eq, $ne, $gt, $gte, $lt, $lte) used inside $project or $addFields map to DQL's native =, !=, >, >=, <, <= operators.

  $expr (using aggregation expressions inside $match) is not needed in DQL — expressions work natively anywhere in a WHERE clause.

  ## Unsupported — if the pipeline uses any of these, do NOT guess a DQL translation. Instead:
  1. State which operator is unsupported.
  2. Describe what it does in the MongoDB pipeline.
  3. Suggest the closest workaround.

  Unsupported operators and their workarounds:
  - $lookup / $graphLookup  → No join support. Use ditto.store.transaction() to query both collections at a consistent snapshot, then join in application code.
  - $unwind (row-multiplication) → No row-multiplying unwind. Use ARRAY transform for in-place reshaping or ANY/EVERY for filtering.
  - $group $push / $addToSet (array) → No array-accumulating aggregate. Query ungrouped and collect values in application code.
  - $group $first / $last / $top / $topN → No ordered-first/last-in-group aggregate.
  - $group $stdDevPop / $stdDevSamp / $percentile → No statistical aggregates.
  - $accumulator / $function → No user-defined functions.
  - $facet  → No single-query multi-facet. Use a read-only ditto.store.transaction() to run multiple aggregations against a consistent snapshot.
  - $merge / $out → Queries are read-only. Use ditto.store.transaction() to aggregate, transform in application code, and write back atomically.
  - $replaceRoot / $replaceWith → No direct equivalent. Select nested fields explicitly.
  - $sample → No random sampling.
  - $unionWith → No collection union.
  - $setWindowFields → No window functions.
  - $reduce → No fold/reduce expression.
  - $concatArrays / $slice / $reverseArray / $sortArray / $zip → No direct array equivalents.
  - $setUnion / $setIntersection / $setDifference → No set algebra on arrays.
  - $replaceOne / $replaceAll → No string replace function.
  - $round / $trunc / $sqrt / $pow / $exp / $ln / $log → No advanced math functions.
  - $sin / $cos / $tan (and other trig) → No trigonometric functions.
  - $geoNear → No geospatial support.
  - $densify / $fill → No time-series gap filling.

  ## Output format
  - Provide the DQL query in a sql code block.
  - If the pipeline collapses cleanly to DQL, show the query with a one-line explanation of each clause.
  - If any part is unsupported, show the DQL for the supported portions (if any) and then list each unsupported operator with its workaround.
  - Do not invent DQL syntax. If you are uncertain whether a function exists, say so.
  ```
</Accordion>

## Quick Reference

The table below covers the most common MongoDB aggregation pipeline stages and expression operators. Where a direct equivalent exists, a DQL example is provided. Where one doesn't exist yet, the notes column says so — if any of those are important to your use case, [reach out to support](https://support.ditto.com) and let us know.

| MongoDB                                             | DQL Equivalent                                              | Notes                                                                                                                                 |
| --------------------------------------------------- | ----------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------- |
| `$match`                                            | `WHERE`                                                     | Full operator parity: `=`, `!=`, `<`, `>`, `BETWEEN`, `IN`, `LIKE`, `IS NULL`, `IS MISSING`, etc.                                     |
| `$project` (include fields)                         | `SELECT field1, field2`                                     | Direct equivalent                                                                                                                     |
| `$project` (exclude fields)                         | `SELECT products.*, MISSING field1 FROM products`           | Wildcard must be qualified — `SELECT *, MISSING field1` is a syntax error                                                             |
| `$project` (computed fields)                        | `SELECT expr AS alias`                                      | Full expression support                                                                                                               |
| `$addFields` / `$set`                               | `SELECT products.*, expr AS new_field FROM products`        | Same pattern — qualified wildcard plus computed fields                                                                                |
| `$unset` (top-level)                                | `SELECT products.*, MISSING field FROM products`            | Same as exclude projection                                                                                                            |
| `$unset` (nested field)                             | `object_unset(obj, 'field')`                                | Use in SELECT projection for nested exclusion                                                                                         |
| `$group`                                            | `GROUP BY`                                                  | Works with `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, `MEDIAN`                                                                              |
| `$group` with `$push`                               | —                                                           | No direct equivalent. No array-accumulating aggregate.                                                                                |
| `$group` with `$addToSet`                           | `COUNT(DISTINCT ...)`                                       | Counts distinct values only; no array-building equivalent                                                                             |
| `$group` with `$first` / `$last`                    | —                                                           | No direct equivalent.                                                                                                                 |
| `$group` with `$stdDevPop` / `$stdDevSamp`          | —                                                           | No direct equivalent.                                                                                                                 |
| `$group` with `$percentile`                         | —                                                           | No direct equivalent.                                                                                                                 |
| `$accumulator` / `$function`                        | —                                                           | No direct equivalent.                                                                                                                 |
| `$sort`                                             | `ORDER BY ... [ASC\|DESC]`                                  | Direct equivalent                                                                                                                     |
| `$limit`                                            | `LIMIT n`                                                   | Direct equivalent                                                                                                                     |
| `$skip`                                             | `OFFSET n`                                                  | Direct equivalent                                                                                                                     |
| `$count`                                            | `SELECT COUNT(*)`                                           | Direct equivalent                                                                                                                     |
| `$unwind`                                           | `ARRAY ... FOR ... IN ... END` / `ANY ... IN ... SATISFIES` | No row-multiplying unwind. Use array transform or search expressions.                                                                 |
| `$lookup`                                           | —                                                           | Use a transaction to query multiple collections at a consistent snapshot — [see example](#simulating-joins).                          |
| `$facet`                                            | Multiple separate queries                                   | Use a read-only transaction to run multiple aggregations against the same snapshot — [see example](#consistent-multi-query-snapshot). |
| `$bucket` / `$bucketAuto`                           | `CASE WHEN ... END` + `GROUP BY`                            | Manual bucketing via CASE expressions                                                                                                 |
| `$replaceRoot` / `$replaceWith`                     | —                                                           | No direct equivalent.                                                                                                                 |
| `$merge` / `$out`                                   | —                                                           | Use a transaction for atomic read-modify-write — [see example](#atomic-read-modify-write).                                            |
| `$sample`                                           | —                                                           | No random document sampling.                                                                                                          |
| `$unionWith`                                        | —                                                           | No collection union.                                                                                                                  |
| `$geoNear`                                          | —                                                           | No geospatial support.                                                                                                                |
| `$densify` / `$fill`                                | —                                                           | No time-series gap filling.                                                                                                           |
| `$redact`                                           | —                                                           | No document-level access control.                                                                                                     |
| `$setWindowFields`                                  | —                                                           | No window functions.                                                                                                                  |
| `$cond`                                             | `CASE WHEN ... THEN ... ELSE ... END`                       | Direct equivalent; DQL CASE is more expressive                                                                                        |
| `$ifNull`                                           | `ifnull(v1, v2, ...)` / `coalesce(...)`                     | Direct equivalent                                                                                                                     |
| `$switch`                                           | Simple or searched `CASE`                                   | Direct equivalent                                                                                                                     |
| `$map`                                              | `ARRAY expr FOR v IN source END`                            | SDK v5+                                                                                                                               |
| `$filter`                                           | `ARRAY v FOR v IN source WHEN condition END`                | SDK v5+                                                                                                                               |
| `$reduce`                                           | —                                                           | No direct equivalent.                                                                                                                 |
| `$arrayElemAt`                                      | `array[index]`                                              | Direct subscript access                                                                                                               |
| `$size` (array)                                     | `array_length(arr)`                                         | Direct equivalent                                                                                                                     |
| `$in` (array membership)                            | `array_contains(arr, val)`                                  | Direct equivalent                                                                                                                     |
| `$concatArrays`                                     | —                                                           | No documented direct equivalent.                                                                                                      |
| `$slice`                                            | —                                                           | No documented direct equivalent.                                                                                                      |
| `$reverseArray`                                     | —                                                           | No array reversal function.                                                                                                           |
| `$sortArray`                                        | —                                                           | No in-expression array sort.                                                                                                          |
| `$zip`                                              | —                                                           | No array zip.                                                                                                                         |
| `$setUnion` / `$setIntersection` / `$setDifference` | —                                                           | No set algebra operators on arrays.                                                                                                   |
| `$mergeObjects`                                     | `object_concat(obj1, obj2, ...)`                            | Direct equivalent                                                                                                                     |
| `$objectToArray`                                    | `object_content(obj, 'fields')`                             | Available in SDK v4.11+                                                                                                               |
| `$arrayToObject`                                    | `OBJECT name_expr:value_expr FOR ... IN ... END`            | Object transformation expression, SDK v5+                                                                                             |
| `$dateFromString`                                   | `date_cast(str, format)`                                    | Direct equivalent                                                                                                                     |
| `$dateToString`                                     | `date_format(date, format)`                                 | Direct equivalent                                                                                                                     |
| `$dateAdd` / `$dateSubtract`                        | `date_add(...)` / `date_sub(...)`                           | Direct equivalent                                                                                                                     |
| `$dateDiff`                                         | `date_diff(date1, date2, part)`                             | Direct equivalent                                                                                                                     |
| `$year`, `$month`, `$day`, etc.                     | `date_part(date, 'year')`                                   | Unified single function                                                                                                               |
| `$regexMatch`                                       | `regexp_like(...)`, `LIKE`, `ILIKE`, `SIMILAR TO`           | Multiple pattern-matching options                                                                                                     |
| `$substr` / `$substrCP`                             | `substr(str, start, len)`                                   | Direct equivalent                                                                                                                     |
| `$concat`                                           | `concat(...)` or `\|\|` operator                            | Direct equivalent                                                                                                                     |
| `$toLower` / `$toUpper`                             | `lower(str)` / `upper(str)`                                 | Direct equivalent                                                                                                                     |
| `$trim` / `$ltrim` / `$rtrim`                       | `` `trim`(str) `` / `ltrim()` / `rtrim()`                   | Direct equivalent; `trim` requires backtick quoting                                                                                   |
| `$type`                                             | `type(x)` / `json_type(x)`                                  | Direct equivalent                                                                                                                     |
| `$toString`, `$toInt`, etc.                         | `cast(v, 'string')` / `CAST(v AS type)`                     | Unified cast function                                                                                                                 |
| `$abs`                                              | `abs(x)`                                                    | Direct equivalent                                                                                                                     |
| `$ceil`                                             | `ceil(x)`                                                   | Direct equivalent                                                                                                                     |
| `$floor`                                            | `floor(x)`                                                  | Direct equivalent                                                                                                                     |
| `$round` / `$trunc`                                 | —                                                           | No rounding or truncation functions.                                                                                                  |
| `$sqrt` / `$pow` / `$exp` / `$ln` / `$log`          | —                                                           | No advanced math functions.                                                                                                           |
| `$sin`, `$cos`, `$tan`, etc.                        | —                                                           | No trigonometric functions.                                                                                                           |
| Pipeline composition (multi-stage)                  | Single declarative `SELECT`                                 | Mental model shift, not a feature gap                                                                                                 |

***

## Filtering

### `$match` → `WHERE`

```json MongoDB theme={null}
{ $match: { status: "active", price: { $gt: 50 } } }
```

```sql DQL theme={null}
SELECT * FROM orders WHERE status = 'active' AND price > 50
```

DQL supports the full comparison operator set: `=`, `!=`, `<`, `>`, `<=`, `>=`, `BETWEEN`, `IN`, `NOT IN`, `LIKE`, `ILIKE`, `SIMILAR TO`, `IS NULL`, `IS NOT NULL`, `IS MISSING`, `IS NOT MISSING`.

```sql DQL theme={null}
-- Range filter
SELECT * FROM products WHERE price BETWEEN 10 AND 100

-- Membership test
SELECT * FROM orders WHERE status IN ('active', 'pending')

-- Pattern match (case-insensitive)
SELECT * FROM users WHERE name ILIKE 'john%'

-- Null / missing checks
SELECT * FROM documents WHERE archived IS NULL
SELECT * FROM documents WHERE tags IS NOT MISSING
```

***

## Projection

### `$project` (include fields)

```json MongoDB theme={null}
{ $project: { name: 1, price: 1 } }
```

```sql DQL theme={null}
SELECT name, price FROM products
```

### `$project` (computed fields)

```json MongoDB theme={null}
{ $project: { name: 1, discounted: { $multiply: ["$price", 0.9] } } }
```

```sql DQL theme={null}
SELECT name, price * 0.9 AS discounted FROM products
```

### `$addFields` / `$set` — add fields while keeping all existing ones

```json MongoDB theme={null}
{ $addFields: { adjusted_price: { $multiply: ["$price", 1.1] } } }
```

```sql DQL theme={null}
SELECT products.*, price * 1.1 AS adjusted_price FROM products
```

<Note>
  `SELECT *, price * 1.1 AS adjusted_price FROM products` is a syntax error. The wildcard must be qualified with the collection name: `SELECT products.*, ...`.
</Note>

### `$project` (exclude fields) / `$unset`

```json MongoDB theme={null}
{ $unset: ["internal_notes", "cost"] }
```

```sql DQL theme={null}
SELECT products.*, MISSING internal_notes, MISSING cost FROM products
```

<Warning>
  `SELECT *, MISSING field1 FROM products` is a syntax error. The wildcard must be qualified with the collection name: `SELECT products.*, MISSING field1 FROM products`. See [Excluding Fields](/dql/select#excluding-fields).
</Warning>

Fields listed as `MISSING` override the wildcard expansion — `products.*` includes the field, but the explicit `MISSING` projection wins and the key is absent from output.

For excluding a **nested field** within an object value, use `object_unset`:

```sql DQL theme={null}
SELECT object_unset(metadata, 'internal', true) AS metadata FROM products
```

***

## Grouping and Aggregation

### `$group`

```json MongoDB theme={null}
{
  $group: {
    _id: "$category",
    total_sales: { $sum: "$price" },
    avg_price: { $avg: "$price" },
    count: { $sum: 1 }
  }
}
```

```sql DQL theme={null}
SELECT
  category,
  SUM(price) AS total_sales,
  AVG(price) AS avg_price,
  COUNT(*) AS count
FROM products
GROUP BY category
```

All standard aggregates are supported: `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, `MEDIAN`, `MID`.

### `HAVING` — filter on aggregated results

```json MongoDB theme={null}
{ $group: { _id: "$category", count: { $sum: 1 } } },
{ $match: { count: { $gt: 5 } } }
```

```sql DQL theme={null}
SELECT category, COUNT(*) AS count
FROM products
GROUP BY category
HAVING COUNT(*) > 5
```

### `$addToSet` / `COUNT(DISTINCT ...)`

DQL supports `COUNT(DISTINCT ...)` for counting unique values but does not accumulate values into an array within a group:

```sql DQL theme={null}
SELECT category, COUNT(DISTINCT supplier_id) AS unique_suppliers
FROM products
GROUP BY category
```

<Warning>
  `$push` and `$addToSet` (as array accumulators in a group) have no DQL equivalent. If you need to collect values into an array per group, restructure the query or handle this in application code.
</Warning>

***

## Sorting, Limiting, and Pagination

### `$sort`, `$limit`, `$skip`

```json MongoDB theme={null}
[
  { $sort: { created_at: -1 } },
  { $skip: 20 },
  { $limit: 10 }
]
```

```sql DQL theme={null}
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 10
OFFSET 20
```

***

## Counting

### `$count`

```json MongoDB theme={null}
{ $count: "total" }
```

```sql DQL theme={null}
SELECT COUNT(*) AS total FROM products
```

With a filter:

```sql DQL theme={null}
SELECT COUNT(*) AS total FROM products WHERE category = 'electronics'
```

***

## Array Operations

### `$unwind` → array transform or search expression

MongoDB's `$unwind` duplicates a document for each array element. DQL has no row-multiplying unwind. Instead:

* **Transform** array contents in-place with the `ARRAY` expression
* **Filter** documents based on array contents with `ANY ... IN ... SATISFIES`

```json MongoDB theme={null}
{ $unwind: "$tags" }
// then: { $match: { tags: "electronics" } }
```

```sql DQL theme={null}
-- Find documents where any tag matches
SELECT * FROM products
WHERE ANY tag IN tags SATISFIES tag = 'electronics' END
```

```sql DQL theme={null}
-- Transform: extract only active line items
SELECT ARRAY item FOR item IN line_items WHEN item.status = 'active' END AS active_items
FROM orders
```

### `$map` → `ARRAY` transformation expression

```json MongoDB theme={null}
{ $project: { prices_with_tax: { $map: { input: "$item_prices", as: "p", in: { $multiply: ["$$p", 1.08] } } } } }
```

```sql DQL theme={null}
SELECT ARRAY price * 1.08 FOR price IN item_prices END AS prices_with_tax
FROM orders
```

### `$filter` → `ARRAY ... WHEN` expression

```json MongoDB theme={null}
{ $project: { active_items: { $filter: { input: "$items", as: "item", cond: { $eq: ["$$item.status", "active"] } } } } }
```

```sql DQL theme={null}
SELECT ARRAY item FOR item IN items WHEN item.status = 'active' END AS active_items
FROM orders
```

### `$arrayElemAt` → subscript operator

```json MongoDB theme={null}
{ $project: { first: { $arrayElemAt: ["$items", 0] } } }
```

```sql DQL theme={null}
SELECT items[0] AS first_item FROM orders
```

### `$size` → `array_length()`

```json MongoDB theme={null}
{ $project: { tag_count: { $size: "$tags" } } }
```

```sql DQL theme={null}
SELECT array_length(tags) AS tag_count FROM products
```

### `$in` (array membership) → `array_contains()`

```json MongoDB theme={null}
{ $project: { has_electronics: { $in: ["electronics", "$tags"] } } }
```

```sql DQL theme={null}
SELECT array_contains(tags, 'electronics') AS has_electronics FROM products
```

<Warning>
  `$concatArrays` and `$slice` have no documented DQL equivalent at this time.
</Warning>

***

## Object Operations

### `$mergeObjects` → `object_concat()`

```json MongoDB theme={null}
{ $project: { merged: { $mergeObjects: ["$defaults", "$settings"] } } }
```

```sql DQL theme={null}
SELECT object_concat(defaults, settings) AS merged FROM users
```

Later arguments win on key conflicts.

### `$objectToArray` → `object_content()`

```json MongoDB theme={null}
{ $project: { pairs: { $objectToArray: "$doc" } } }
```

```sql DQL theme={null}
SELECT object_content(doc, 'fields') AS pairs FROM collection1
```

`object_content()` returns an array of single-key objects. Available in SDK v4.11+. See [Object Operators](/dql/operator-expressions#object-operators) for full configuration options.

### `$arrayToObject` → `OBJECT` transformation expression

```json MongoDB theme={null}
{ $project: { id_map: { $arrayToObject: { $map: { input: "$catalog", as: "v", in: { k: "$$v._id", v: "$$v.name" } } } } } }
```

```sql DQL theme={null}
SELECT OBJECT v._id:v.name FOR v IN catalog END AS id_map
FROM product_catalog
```

Available in SDK v5+.

***

## Conditional Expressions

### `$cond` → `CASE WHEN`

```json MongoDB theme={null}
{ $project: { tier: { $cond: { if: { $gt: ["$price", 100] }, then: "premium", else: "standard" } } } }
```

```sql DQL theme={null}
SELECT
  price,
  CASE WHEN price > 100 THEN 'premium' ELSE 'standard' END AS tier
FROM products
```

Multi-branch:

```sql DQL theme={null}
SELECT
  price,
  CASE
    WHEN price > 100 THEN 'premium'
    WHEN price > 50  THEN 'mid'
    ELSE 'budget'
  END AS tier
FROM products
```

### `$ifNull` → `ifnull()` / `coalesce()`

```json MongoDB theme={null}
{ $project: { display: { $ifNull: ["$nickname", "$name"] } } }
```

```sql DQL theme={null}
SELECT ifnull(nickname, name) AS display_name FROM users
```

`coalesce(v1, v2, ...)` is a synonym that returns the first non-null, non-missing value.

### `$switch` → simple `CASE`

```json MongoDB theme={null}
{ $project: { code: { $switch: { branches: [ { case: { $eq: ["$color","red"] }, then: 1 }, { case: { $eq: ["$color","blue"] }, then: 2 } ], default: 0 } } } }
```

```sql DQL theme={null}
SELECT
  CASE color
    WHEN 'red'  THEN 1
    WHEN 'blue' THEN 2
    ELSE 0
  END AS color_code
FROM cars
```

***

## Bucketing

### `$bucket` / `$bucketAuto` → `CASE` + `GROUP BY`

```json MongoDB theme={null}
{ $bucket: { groupBy: "$price", boundaries: [0, 25, 75, 200], default: "other" } }
```

```sql DQL theme={null}
SELECT
  CASE
    WHEN price < 25  THEN 'budget'
    WHEN price < 75  THEN 'mid'
    WHEN price < 200 THEN 'premium'
    ELSE 'other'
  END AS bucket,
  COUNT(*) AS count
FROM products
GROUP BY
  CASE
    WHEN price < 25  THEN 'budget'
    WHEN price < 75  THEN 'mid'
    WHEN price < 200 THEN 'premium'
    ELSE 'other'
  END
```

***

## Date Functions

| MongoDB                            | DQL                             | Notes                                                                                      |
| ---------------------------------- | ------------------------------- | ------------------------------------------------------------------------------------------ |
| `$dateFromString`                  | `date_cast(str, format)`        | Converts string to epoch milliseconds                                                      |
| `$dateToString`                    | `date_format(date, format)`     | Converts epoch ms to formatted string                                                      |
| `$dateAdd`                         | `date_add(date, part, count)`   | Parts: `year`, `mon`, `day`, `hour`, `min`, `sec`, `ms`                                    |
| `$dateSubtract`                    | `date_sub(date, part, count)`   | Negative count on `date_add` is equivalent                                                 |
| `$dateDiff`                        | `date_diff(date1, date2, part)` | Returns integer count of parts between dates                                               |
| `$year` / `$month` / `$day` / etc. | `date_part(date, 'year')`       | Single function; parts: `year`, `mon`, `day`, `hour`, `min`, `sec`, `weekday`, `monthname` |
| `$dateTrunc`                       | `date_trunc(date, part)`        | Truncates to the given part                                                                |

```sql DQL theme={null}
-- Parse a date string
SELECT date_cast('2024-01-15', 'YYYY-MM-DD') AS ts FROM system:dual

-- Format a stored timestamp
SELECT date_format(created_at, 'YYYY-MM-DD') AS date_str FROM orders

-- Add 7 days
SELECT date_add(created_at, 'day', 7) AS due_date FROM orders

-- Difference between dates in days
SELECT date_diff(due_date, created_at, 'day') AS days_open FROM tasks

-- Extract the year
SELECT date_part(created_at, 'year') AS yr FROM orders
```

***

## String Functions

| MongoDB                       | DQL                                    | Notes                                                      |
| ----------------------------- | -------------------------------------- | ---------------------------------------------------------- |
| `$regexMatch`                 | `regexp_like(str, pattern[, flags])`   | Also: `LIKE`, `ILIKE`, `SIMILAR TO`                        |
| `$regexFind`                  | `regexp_like(...)`                     | Match test only; no capture group extraction               |
| `$substr` / `$substrCP`       | `substr(str, start[, len])`            | Zero-based index                                           |
| `$concat`                     | `concat(str, ...)` or `str1 \|\| str2` | Direct equivalent                                          |
| `$toLower`                    | `lower(str)`                           | Direct equivalent                                          |
| `$toUpper`                    | `upper(str)`                           | Direct equivalent                                          |
| `$trim`                       | `` `trim`(str) ``                      | Backtick quoting required; `ltrim`, `rtrim` also available |
| `$ltrim`                      | `ltrim(str[, chars])`                  | Direct equivalent                                          |
| `$rtrim`                      | `rtrim(str[, chars])`                  | Direct equivalent                                          |
| `$indexOfCP`                  | `pos(str, substr)`                     | Returns zero-based index; `-1` if not found                |
| `$strLenCP`                   | `char_length(str)`                     | Direct equivalent                                          |
| `$strLenBytes`                | `byte_length(str)`                     | Direct equivalent                                          |
| `$split`                      | `split(str, delim)`                    | Returns array of parts                                     |
| `$replaceOne` / `$replaceAll` | —                                      | No string replace function.                                |

```sql DQL theme={null}
-- Regex match (case-insensitive flag)
SELECT regexp_like(name, '^[A-Z]') AS starts_capital FROM users

-- Substring
SELECT substr(name, 0, 3) AS prefix FROM users

-- Concatenation
SELECT concat(first_name, ' ', last_name) AS full_name FROM users

-- Case conversion
SELECT lower(name) AS name_lower, upper(name) AS name_upper FROM users

-- Trim whitespace (note backtick quoting)
SELECT `trim`(name) AS name_trimmed FROM users
```

***

## Type Inspection and Casting

### `$type` → `type()` / `json_type()`

```json MongoDB theme={null}
{ $project: { val_type: { $type: "$value" } } }
```

```sql DQL theme={null}
SELECT type(value) AS val_type FROM data
```

`type()` returns DQL type names (`boolean`, `string`, `integer`, `float`, `object`, `array`, `binary`, `null`, `missing`). `json_type()` returns JSON type names (`boolean`, `string`, `number`, `object`, `array`, `binary`, `null`), returning `"null"` for MISSING values.

### `$toString`, `$toInt`, `$toDouble`, etc. → `cast()`

```json MongoDB theme={null}
{ $project: { price_str: { $toString: "$price" } } }
```

```sql DQL theme={null}
SELECT cast(price, 'string') AS price_str FROM products
```

Supported target types: `'string'`, `'integer'`, `'float'`, `'boolean'`, `'binary'`. Also accepts `CAST(v AS type)` SQL syntax. Returns `MISSING` if conversion is not possible.

***

## Multi-Stage Pipelines → Single SELECT

A MongoDB pipeline composing multiple stages maps to a single DQL `SELECT`:

```json MongoDB theme={null}
[
  { $match: { status: "active" } },
  { $project: { name: 1, category: 1, price: 1 } },
  { $group: { _id: "$category", avg_price: { $avg: "$price" }, count: { $sum: 1 } } },
  { $sort: { avg_price: -1 } },
  { $limit: 5 }
]
```

```sql DQL theme={null}
SELECT
  category,
  AVG(price) AS avg_price,
  COUNT(*) AS count
FROM products
WHERE status = 'active'
GROUP BY category
ORDER BY avg_price DESC
LIMIT 5
```

***

## Transaction Workarounds

Ditto's [`transaction()` API](/sdk/latest/crud/transactions) runs multiple DQL statements in a single atomic block at serializable isolation — either all statements commit or none do. This covers several patterns that have no direct DQL equivalent. Examples below use JavaScript; the same pattern applies in all supported SDKs.

### Simulating Joins

DQL has no join syntax, but a read-only transaction guarantees both queries see the same consistent snapshot of the store. Combine the results in application code:

```js JavaScript theme={null}
const { orders, customer } = await ditto.store.transaction(
  async (tx) => {
    const orders = await tx.execute(
      "SELECT * FROM orders WHERE customer_id = :id",
      { id: customerId }
    );
    const customer = await tx.execute(
      "SELECT * FROM customers WHERE _id = :id",
      { id: customerId }
    );
    return { orders: orders.items, customer: customer.items[0] };
  },
  { hint: "join-simulation", isReadOnly: true }
);
// Join in application code
const enriched = orders.map((o) => ({ ...o, customer }));
```

### Consistent Multi-Query Snapshot

The equivalent of `$facet` — run independent aggregations that are guaranteed to reflect the same point in time:

```js JavaScript theme={null}
const facets = await ditto.store.transaction(
  async (tx) => {
    const byCategory = await tx.execute(
      "SELECT category, COUNT(*) AS count FROM products GROUP BY category"
    );
    const byStatus = await tx.execute(
      "SELECT status, COUNT(*) AS count FROM products GROUP BY status"
    );
    return { byCategory: byCategory.items, byStatus: byStatus.items };
  },
  { hint: "facet-simulation", isReadOnly: true }
);
```

### Atomic Read-Modify-Write

The equivalent of `$merge` / `$out` — aggregate, transform in application code, then write the results back in the same atomic operation:

```js JavaScript theme={null}
await ditto.store.transaction(
  async (tx) => {
    const summary = await tx.execute(
      "SELECT category, COUNT(*) AS count, AVG(price) AS avg_price FROM products GROUP BY category"
    );
    for (const row of summary.items) {
      await tx.execute(
        "INSERT INTO product_summaries VALUES (:doc) ON ID CONFLICT DO UPDATE",
        { doc: { _id: row.category, count: row.count, avg_price: row.avg_price } }
      );
    }
    return "commit";
  },
  { hint: "aggregate-and-write" }
);
```

***

## Known Gaps

The following MongoDB aggregation capabilities have no DQL equivalent.

### Pipeline Stages

| MongoDB                         | Workaround                                                                                                                                              |
| ------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `$lookup`                       | No joins. Use a [transaction](#simulating-joins) to query multiple collections at a consistent snapshot and join in application code.                   |
| `$graphLookup`                  | No graph traversal. Implement multi-hop lookups in application code.                                                                                    |
| `$unionWith`                    | No collection union. Run separate queries and merge results in application code.                                                                        |
| `$unwind` (row multiplication)  | No row-multiplying unwind. Use `ARRAY` transform or `ANY`/`EVERY` search expressions for in-place operations.                                           |
| `$facet`                        | No single-query equivalent. Use a [read-only transaction](#consistent-multi-query-snapshot) to run multiple aggregations against a consistent snapshot. |
| `$replaceRoot` / `$replaceWith` | No direct equivalent. Restructure data at insert time, or select nested fields explicitly.                                                              |
| `$merge` / `$out`               | Queries are read-only. Use a [transaction](#atomic-read-modify-write) to aggregate, transform, and write back atomically.                               |
| `$sample`                       | No random sampling. Fetch a larger result set and sample in application code.                                                                           |
| `$setWindowFields`              | No window functions (`$rank`, `$denseRank`, `$expMovingAvg`, etc.).                                                                                     |
| `$densify` / `$fill`            | No time-series gap filling or null-value backfill.                                                                                                      |
| `$geoNear`                      | No geospatial support.                                                                                                                                  |
| `$redact`                       | No document-level access control expressions.                                                                                                           |

### Grouping and Accumulators

| MongoDB                                   | Workaround                                                                                         |
| ----------------------------------------- | -------------------------------------------------------------------------------------------------- |
| `$push` / `$addToSet` (array accumulator) | No array-building aggregate. Collect values in application code after querying.                    |
| `$first` / `$last` / `$firstN` / `$lastN` | No ordered first/last value within a group. Use a subquery approach or handle in application code. |
| `$top` / `$topN` / `$bottom` / `$bottomN` | No top-N per group. Sort and limit in application code.                                            |
| `$stdDevPop` / `$stdDevSamp`              | No standard deviation aggregates.                                                                  |
| `$percentile`                             | No percentile aggregate.                                                                           |
| `$accumulator` / `$function`              | No user-defined aggregate or expression functions.                                                 |

### Array Operations

| MongoDB                                                                             | Workaround                                                                                  |
| ----------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------- |
| `$reduce`                                                                           | No fold/reduce expression. Implement in application code.                                   |
| `$concatArrays`                                                                     | No array concatenation expression. Combine in application code.                             |
| `$slice`                                                                            | Use subscript `array[index]` for single elements; slice in application code.                |
| `$reverseArray`                                                                     | No array reversal. Reverse in application code.                                             |
| `$sortArray`                                                                        | No in-expression array sort. Sort in application code.                                      |
| `$zip`                                                                              | No array zip. Implement in application code.                                                |
| `$setUnion` / `$setIntersection` / `$setDifference` / `$setEquals` / `$setIsSubset` | No set algebra on arrays. Implement using `ARRAY` transforms and `ANY`/`EVERY` expressions. |

### String Operations

| MongoDB                       | Workaround                                                                           |
| ----------------------------- | ------------------------------------------------------------------------------------ |
| `$replaceOne` / `$replaceAll` | No string replace function. Use `regexp_like` to match; replace in application code. |

### Math Functions

DQL provides `abs()`, `ceil()`, and `floor()`. The following have no equivalent:

| MongoDB                                                           | Workaround                                                                  |
| ----------------------------------------------------------------- | --------------------------------------------------------------------------- |
| `$round` / `$trunc`                                               | Use `floor(x + 0.5)` to approximate rounding; truncate in application code. |
| `$sqrt`                                                           | No square root. Compute in application code.                                |
| `$pow`                                                            | No exponentiation. Compute in application code.                             |
| `$exp` / `$ln` / `$log` / `$log10`                                | No exponential or logarithm functions. Compute in application code.         |
| `$sin`, `$cos`, `$tan`, `$asin`, `$acos`, `$atan`, `$atan2`, etc. | No trigonometric functions. Compute in application code.                    |
| `$degreesToRadians` / `$radiansToDegrees`                         | No angle conversion functions.                                              |