---
url: /guide/query/snapshot-aggregation.md
description: >-
  Apply snapshot aggregation to root documents and collection elements through
  fourteen business scenarios.
---

# Snapshot Aggregation

Snapshot aggregation treats each aggregate's current materialized state as the source of truth and returns dynamic table rows whose columns are group and metric aliases. See [Aggregation Queries](./aggregation-query.md) for the shared AST, aliases, sorting, and structural limits. This page applies that contract to snapshots.

Numeric `FIELD` inputs and arithmetic leaves follow [numeric contributions and precision](./aggregation-query.md#numeric-contributions): each current record contributes only when exactly one stored numeric value remains after nulls are ignored; duplicates count separately. COUNT still counts records. This preserves singleton-array support without changing histogram bucket contracts.

## Capabilities and Entry Points

* **JVM Gateway**: inject the aggregate-scoped `SnapshotQueryGateway<OrderState>` through Spring, build an `AggregationQuery`, and call `query.query(snapshotQueryGateway)`. This Bean executes policies through [QueryGateway](./query-gateway.md); see [Query Backends](./query-backend.md) for the direct Backend Factory bypass boundary.
* **HTTP / OpenAPI**: the example domain publishes `POST /sales-order/snapshot/aggregation`, `POST /tenant/{tenantId}/sales-order/snapshot/aggregation`, and `POST /owner/{ownerId}/sales-order/snapshot/aggregation`. The request body is `AggregationQuery` JSON, and the response can negotiate `application/json` or `text/event-stream`. Use the running service's generated [OpenAPI](../open-api.md) for exact paths and scope parameters.
* **Snapshot API Client**: reactive and synchronous clients use the separate `ReactiveSnapshotAggregationQueryApi` and `SynchronousSnapshotAggregationQueryApi`; they are not folded into the regular snapshot-query interfaces. See the [general API Client guide](./query-api-client.md) for dependencies and invocation.

Every HTTP JSON block below is a request body for one of these `snapshot/aggregation` routes. Results are representative dynamic rows, not fixed business data.

## Field Paths and Counting Units

Without `elements`, the root filter, groups, and metrics use absolute snapshot logical paths such as `state.status`; one record is one current root snapshot document. Business fields under `state` need the corresponding filter, group, or numeric capabilities from the [Query Model Schema (current guidance)](./query-model-schema.md).

After `expand("state.items")`, the counting unit becomes one expanded order item. The first Element path remains absolute, while its filter and all following group, metric, and expression fields are relative to that element. Use `quantity`, `productId`, and `price`, not `state.items.quantity`. Groups only bucket records and do not change the counting unit; `COUNT` always counts the current innermost scope.

```mermaid
flowchart TB
    Unit{"Counting unit"} --> Root["Root snapshot document"]
    Unit --> Item["Expanded state.items"]
    Root --> S1["1 Status breakdown"]
    Root --> S2["2 Filtered KPIs"]
    Root --> S3["3 Numeric ranges"]
    Root --> S4["4 Business-time trend"]
    Root --> S7["7 Multidimensional analysis"]
    Root --> S9["9 Distinct customers and P95 amount"]
    Root --> S10["10 Funnel conditional counts"]
    Root --> S11["11 Attainment and paid AOV"]
    Root --> S12["12 Attainment-threshold filtering"]
    Root --> S13["13 Dense time-series fill"]
    Item --> S5["5 Line-item Top-N"]
    Item --> S6["6 Derived amount"]
    Item --> S8["8 ANY display field"]
    Item --> S14["14 Missing-value bucket"]
```

## Scenario 1: Status Breakdown

**Business question**

How many current order snapshots are in each status?

**Counting unit**

Root snapshot documents; each current order snapshot is counted once.

**Kotlin DSL**

```kotlin
val query = aggregation {
    terms("state.status", "status")
    count("count")
}
```

**HTTP JSON and result interpretation**

```json
{
  "groupBy": [
    {"type": "TERMS", "field": "state.status", "alias": "status"}
  ],
  "metrics": [
    {"type": "COUNT", "alias": "count"}
  ]
}
```

```json
[
  {"status": "PAID", "count": 42},
  {"status": "FAILED", "count": 8}
]
```

`status` is the group column, and `count` is the number of snapshots in each group. `state.status` must have TERMS aggregation capability. For example, Elasticsearch normally needs an aggregatable keyword field; an arbitrary text mapping is not equivalent.

## Scenario 2: Filtered KPIs

**Business question**

How many orders have failed, and how many retries did they average?

**Counting unit**

Failed snapshots matching `state.status = FAILED`; with no group, all failed snapshots are summarized into one row.

**Kotlin DSL**

```kotlin
val query = aggregation {
    filter { "state.status" eq "FAILED" }
    count("failedCount")
    avg("state.retryState.retries", "averageRetries")
}
```

**HTTP JSON and result interpretation**

```json
{
  "filter": {"op": "EQ", "field": "state.status", "value": "FAILED"},
  "metrics": [
    {"type": "COUNT", "alias": "failedCount"},
    {
      "type": "NUMERIC",
      "function": "AVG",
      "expression": {"type": "FIELD", "field": "state.retryState.retries"},
      "alias": "averageRetries"
    }
  ]
}
```

```json
[
  {"failedCount": 8, "averageRetries": 2.5}
]
```

`failedCount` counts failed snapshots. `averageRetries` averages only contributing numeric retry counts and is `null` when no numeric value contributes. The fields need exact-match and numeric-aggregation capability, respectively.

## Scenario 3: Numeric Range Distribution

**Business question**

Which 100-unit ranges contain the current order amounts?

**Counting unit**

Root snapshot documents; each current order snapshot enters one amount bucket.

**Kotlin DSL**

```kotlin
val query = aggregation {
    histogram("state.totalAmount", 100.0, "amountRange")
    count("orderCount")
}
```

**HTTP JSON and result interpretation**

```json
{
  "groupBy": [
    {
      "type": "HISTOGRAM",
      "field": "state.totalAmount",
      "alias": "amountRange",
      "interval": 100
    }
  ],
  "metrics": [
    {"type": "COUNT", "alias": "orderCount"}
  ]
}
```

```json
[
  {"amountRange": 0.0, "orderCount": 12},
  {"amountRange": 100.0, "orderCount": 27}
]
```

`amountRange` is the lower bound of each `100`-wide bucket, and `orderCount` is the number of snapshots in that bucket. `state.totalAmount` must have numeric-histogram capability; a valid JSON shape cannot make an invalid or nonnumeric field aggregatable.

## Scenario 4: Business-Time Trend

**Business question**

How are current order creations distributed across Shanghai business days?

**Counting unit**

Root snapshot documents; each current order snapshot enters one day according to the business field `state.createdAt`.

**Kotlin DSL**

```kotlin
val query = aggregation {
    dateHistogram(
        "state.createdAt",
        AggregationDateUnit.DAY,
        "day",
        ZoneId.of("Asia/Shanghai"),
    )
    count("createdCount")
}
```

**HTTP JSON and result interpretation**

```json
{
  "groupBy": [
    {
      "type": "DATE_HISTOGRAM",
      "field": "state.createdAt",
      "alias": "day",
      "unit": "DAY",
      "timeZone": "Asia/Shanghai"
    }
  ],
  "metrics": [
    {"type": "COUNT", "alias": "createdCount"}
  ]
}
```

```json
[
  {"day": 1787846400000, "createdCount": 31},
  {"day": 1787932800000, "createdCount": 24}
]
```

`day` is the bucket-start epoch milliseconds aligned to `Asia/Shanghai`, and `createdCount` is the number of snapshots in the bucket. `state.createdAt` is a business time defined by order state. Root `createTime` is an event-stream record field and is not part of the `MaterializedSnapshot` root model, so it cannot replace this field. MongoDB must prove a BSON Date or a declared numeric epoch, while Elasticsearch must prove date/date\_nanos or runtime-date capability for a declared epoch. A formatted string does not gain date-histogram capability merely from a date pattern.

## Scenario 5: Line-Item Top-N

**Business question**

Which products have the highest valid purchased quantity in paid orders?

**Counting unit**

Expanded order items; only items with `quantity > 0` from root snapshots whose `state.status` is `PAID` contribute. Multiple items in one order are counted and summed separately.

**Kotlin DSL**

```kotlin
val query = aggregation {
    filter { "state.status" eq "PAID" }
    expand("state.items") { "quantity" gt 0 }
    terms("productId", "productId")
    sum("quantity", "totalQuantity")
    sort { "totalQuantity".desc() }
    limit(10)
}
```

**HTTP JSON and result interpretation**

```json
{
  "filter": {"op": "EQ", "field": "state.status", "value": "PAID"},
  "elements": [
    {
      "path": "state.items",
      "filter": {"op": "GT", "field": "quantity", "value": 0}
    }
  ],
  "groupBy": [
    {"type": "TERMS", "field": "productId", "alias": "productId"}
  ],
  "metrics": [
    {
      "type": "NUMERIC",
      "function": "SUM",
      "expression": {"type": "FIELD", "field": "quantity"},
      "alias": "totalQuantity"
    }
  ],
  "sort": [
    {"field": "totalQuantity", "direction": "DESC"}
  ],
  "limit": 10
}
```

```json
[
  {"productId": "product-1", "totalQuantity": 96.0},
  {"productId": "product-2", "totalQuantity": 71.0}
]
```

Sorting references the metric alias `totalQuantity`, and `limit: 10` makes this a Top-N query. `state.items` must have Element-scope capability; after expansion, `productId` and `quantity` are element-relative paths. Elasticsearch needs the corresponding nested mapping to preserve same-item field correlation. When expensive operators are disabled, HTTP rejects both Elements and metric-alias sorting.

## Scenario 6: Derived Amount Metric

**Business question**

What is each product's net amount, `price × quantity - discount`, across all order items?

**Counting unit**

Expanded order items; derive an amount for each item before summing by product.

**Kotlin DSL**

```kotlin
val query = aggregation {
    expand("state.items")
    terms("productId", "productId")
    sum(
        field("price") * field("quantity") - field("discount"),
        "netAmount",
    )
}
```

**HTTP JSON and result interpretation**

```json
{
  "elements": [
    {"path": "state.items"}
  ],
  "groupBy": [
    {"type": "TERMS", "field": "productId", "alias": "productId"}
  ],
  "metrics": [
    {
      "type": "NUMERIC",
      "function": "SUM",
      "expression": {
        "type": "BINARY",
        "operator": "SUBTRACT",
        "left": {
          "type": "BINARY",
          "operator": "MULTIPLY",
          "left": {"type": "FIELD", "field": "price"},
          "right": {"type": "FIELD", "field": "quantity"}
        },
        "right": {"type": "FIELD", "field": "discount"}
      },
      "alias": "netAmount"
    }
  ]
}
```

```json
[
  {"productId": "product-1", "netAmount": 1280.0},
  {"productId": "product-2", "netAmount": 930.0}
]
```

`netAmount` is the sum of valid numeric-expression contributions in each group. All three operands are relative to one order item and need numeric-aggregation capability. HTTP rejects this non-Field expression when `allow-expensive-operators=false`.

## Scenario 7: Multidimensional Cross-Analysis

**Business question**

How are current orders distributed across the status and channel dimensions?

**Counting unit**

Root snapshot documents; each current order snapshot enters one status-and-channel combination.

**Kotlin DSL**

```kotlin
val query = aggregation {
    terms("state.status", "status")
    terms("state.channel", "channel")
    count("count")
}
```

**HTTP JSON and result interpretation**

```json
{
  "groupBy": [
    {"type": "TERMS", "field": "state.status", "alias": "status"},
    {"type": "TERMS", "field": "state.channel", "alias": "channel"}
  ],
  "metrics": [
    {"type": "COUNT", "alias": "count"}
  ]
}
```

```json
[
  {"status": "PAID", "channel": "APP", "count": 28},
  {"status": "PAID", "channel": "WEB", "count": 14}
]
```

Groups are declared in `status`, then `channel` order, and the two aliases identify each cross bucket. Both fields need TERMS capability; the number of returned combinations remains bounded by query `limit` and the HTTP result limit.

## Scenario 8: Display Fields for Groups

**Business question**

How many order items belong to each product ID, with one product name added for display?

**Counting unit**

Expanded order items; `lineCount` counts order items in each product group, not order snapshots.

**Kotlin DSL**

```kotlin
val query = aggregation {
    expand("state.items")
    terms("productId", "productId")
    any("name", "name")
    count("lineCount")
}
```

**HTTP JSON and result interpretation**

```json
{
  "elements": [
    {"path": "state.items"}
  ],
  "groupBy": [
    {"type": "TERMS", "field": "productId", "alias": "productId"}
  ],
  "metrics": [
    {"type": "ANY", "field": "name", "alias": "name"},
    {"type": "COUNT", "alias": "lineCount"}
  ]
}
```

```json
[
  {"productId": "product-1", "name": "Keyboard", "lineCount": 19},
  {"productId": "product-2", "name": "Mouse", "lineCount": 15}
]
```

`ANY` is suitable only for a display field whose value is stable within each `productId` group. If one product ID has multiple `name` values, the selected non-null value is unstable across executions or backends. For deterministic results, repair the business data or model the name as a deterministic group key instead of relying on `ANY`.

## Scenario 9: Distinct Customers and P95 Amount

**Business question**

How many distinct customers does each order status involve, and what are the P95 and the population standard deviation of those order amounts?

**Counting unit**

Root snapshot documents; each current order snapshot contributes one customer-ID participation value and one amount participation value.

**Kotlin DSL**

```kotlin
val query = aggregation {
    filter { deletion(DeletionState.ACTIVE) }
    terms("state.status", "status")
    distinctCount("state.customerId", "customers")
    percentile("state.totalAmount", 95.0, "p95Amount")
    stddev("state.totalAmount", "amountStddev")
    sort { "customers".desc() }
}
```

**HTTP JSON and result interpretation**

```json
{
  "filter": {"op": "DELETION", "state": "ACTIVE"},
  "groupBy": [
    {"type": "TERMS", "field": "state.status", "alias": "status"}
  ],
  "metrics": [
    {
      "type": "DISTINCT_COUNT",
      "expression": {"type": "FIELD", "field": "state.customerId"},
      "alias": "customers"
    },
    {
      "type": "PERCENTILE",
      "expression": {"type": "FIELD", "field": "state.totalAmount"},
      "percentile": 95,
      "alias": "p95Amount"
    },
    {
      "type": "NUMERIC",
      "function": "STDDEV",
      "expression": {"type": "FIELD", "field": "state.totalAmount"},
      "alias": "amountStddev"
    }
  ],
  "sort": [
    {"field": "customers", "direction": "DESC"}
  ]
}
```

```json
[
  {"status": "PAID", "customers": 35, "p95Amount": 812.5, "amountStddev": 143.2},
  {"status": "FAILED", "customers": 6, "p95Amount": 240.0, "amountStddev": 87.6}
]
```

`customers` is the distinct customer count per group: `DISTINCT_COUNT` deduplicates non-null contribution values only, yields `0` for an empty set, and lets array fields participate element by element, which differs from the `NUMERIC` rule (see [numeric contributions and precision](./aggregation-query.md#numeric-contributions)). `p95Amount` and `amountStddev` follow the same numeric-contribution rule as `SUM`/`AVG` and are `null` when nothing contributes; `amountStddev` is population-standard-deviation and returns `0` for a single value; `p95Amount` is approximated by t-digest. `PERCENTILE` on the MongoDB backend requires server 7.0+. The explicit `deletion` filter matches the Gateway's default `DELETION = ACTIVE`.

## Scenario 10: Funnel: Conditional Counts in One Chart

**Business question**

Without issuing multiple queries, how can one result table show the two-level funnel of "all orders → paid orders"?

**Counting unit**

Root snapshot documents; `orderCount` counts every current snapshot, while `paidCount` counts only snapshots with `state.status = PAID`.

**Kotlin DSL**

```kotlin
val query = aggregation {
    count("orderCount")
    count("paidCount") { "state.status" eq "PAID" }
}
```

**HTTP JSON and result interpretation**

```json
{
  "metrics": [
    {"type": "COUNT", "alias": "orderCount"},
    {"type": "COUNT", "alias": "paidCount", "filter": {"op": "EQ", "field": "state.status", "value": "PAID"}}
  ]
}
```

```json
[
  {"orderCount": 50, "paidCount": 42}
]
```

Both metrics share the same counting unit and root filter; each metric filter applies to its own metric only, so `paidCount ≤ orderCount` always holds. Each funnel level is one independent filtered COUNT — append more levels instead of splitting the funnel into multiple queries. See [Metric Filter](./aggregation-query.md#metric-filter) for empty-match semantics, limits, and version requirements.

## Scenario 11: Attainment Ratio and Paid AOV

**Business question**

In one result table, what is each order status's attainment ratio of paid amount against target amount, and which status has the highest paid AOV?

**Counting unit**

Root snapshot documents; `paid` and `paidAmount` count only snapshots with `state.status = PAID`, `targetAmount` sums every snapshot in the group, and the two derived metrics divide those results after aggregation finishes.

**Kotlin DSL**

```kotlin
val query = aggregation {
    terms("state.status", "status")
    count("paid") { "state.status" eq "PAID" }
    sum("state.totalAmount", "paidAmount") { "state.status" eq "PAID" }
    derived("paidAov") { ref("paidAmount") / ref("paid") }
    sum("state.targetAmount", "targetAmount")
    derived("attainment") { ref("paidAmount") / ref("targetAmount") }
    sort { "paidAov".desc() }
}
```

**HTTP JSON and result interpretation**

```json
{
  "groupBy": [
    {"type": "TERMS", "field": "state.status", "alias": "status"}
  ],
  "metrics": [
    {"type": "COUNT", "alias": "paid", "filter": {"op": "EQ", "field": "state.status", "value": "PAID"}},
    {
      "type": "NUMERIC",
      "function": "SUM",
      "expression": {"type": "FIELD", "field": "state.totalAmount"},
      "alias": "paidAmount",
      "filter": {"op": "EQ", "field": "state.status", "value": "PAID"}
    },
    {
      "type": "DERIVED",
      "alias": "paidAov",
      "expression": {
        "type": "BINARY",
        "operator": "DIVIDE",
        "left": {"type": "METRIC_REF", "metric": "paidAmount"},
        "right": {"type": "METRIC_REF", "metric": "paid"}
      }
    },
    {
      "type": "NUMERIC",
      "function": "SUM",
      "expression": {"type": "FIELD", "field": "state.targetAmount"},
      "alias": "targetAmount"
    },
    {
      "type": "DERIVED",
      "alias": "attainment",
      "expression": {
        "type": "BINARY",
        "operator": "DIVIDE",
        "left": {"type": "METRIC_REF", "metric": "paidAmount"},
        "right": {"type": "METRIC_REF", "metric": "targetAmount"}
      }
    }
  ],
  "sort": [
    {"field": "paidAov", "direction": "DESC"}
  ]
}
```

```json
[
  {"status": "PAID", "paid": 42, "paidAmount": 21420.0, "paidAov": 510.0, "targetAmount": 30000.0, "attainment": 0.714},
  {"status": "FAILED", "paid": 0, "paidAmount": null, "paidAov": null, "targetAmount": 8000.0, "attainment": null}
]
```

`paidAov = paidAmount / paid`, and `attainment = paidAmount / targetAmount`. The FAILED row demonstrates empty-set semantics: no PAID record exists in the group, so `paidAmount` is `null` on an empty set, and any null operand propagates `null` — both derived metrics are therefore `null` (dividing by `paid = 0` yields `null` as well). A derived metric cannot carry a metric filter itself; its combination with metric filtering is to reference filtered metrics, and sort can reference a derived alias directly. HTTP rejects derived metrics as arithmetic expressions when expensive operators are disabled. See [Derived Metrics](./aggregation-query.md#derived-metrics) for reference rules and semantics.

## Scenario 12: Attainment-Threshold Filtering

**Business question**

Which order statuses reach at least 80% attainment of a fixed target (6000) in paid amount, with more than 10 paid orders?

**Counting unit**

Root snapshot documents; `paid` and `paidAmount` count only snapshots with `state.status = PAID`, attainment is computed after aggregation, and having then filters grouped rows by aggregated values.

**Kotlin DSL**

```kotlin
val query = aggregation {
    terms("state.status", "status")
    count("paid") { "state.status" eq "PAID" }
    sum("state.totalAmount", "paidAmount") { "state.status" eq "PAID" }
    derived("attainment") { ref("paidAmount") / constant(6000.0) }
    having {
        ("attainment" gte 0.8) and ("paid" gt 10.0)
    }
    sort { "attainment".desc() }
    limit(20)
}
```

**HTTP JSON and result interpretation**

```json
{
  "groupBy": [
    {"type": "TERMS", "field": "state.status", "alias": "status"}
  ],
  "metrics": [
    {"type": "COUNT", "alias": "paid", "filter": {"op": "EQ", "field": "state.status", "value": "PAID"}},
    {
      "type": "NUMERIC",
      "function": "SUM",
      "expression": {"type": "FIELD", "field": "state.totalAmount"},
      "alias": "paidAmount",
      "filter": {"op": "EQ", "field": "state.status", "value": "PAID"}
    },
    {
      "type": "DERIVED",
      "alias": "attainment",
      "expression": {
        "type": "BINARY",
        "operator": "DIVIDE",
        "left": {"type": "METRIC_REF", "metric": "paidAmount"},
        "right": {"type": "CONSTANT", "value": 6000.0}
      }
    }
  ],
  "having": {"type": "AND", "operands": [
    {"type": "CONDITION", "metric": "attainment", "operator": "GTE", "value": 0.8},
    {"type": "CONDITION", "metric": "paid", "operator": "GT", "value": 10}
  ]},
  "sort": [
    {"field": "attainment", "direction": "DESC"}
  ],
  "limit": 20
}
```

```json
[
  {"status": "PAID", "paid": 42, "paidAmount": 5400.0, "attainment": 0.9}
]
```

Having filters groups by their per-row metric results after aggregation, keeping only statuses with `attainment ≥ 0.8` and `paid > 10`; both `sort` and `limit` apply to the filtered rows, so non-qualifying statuses (for example `attainment = 0.5` or `paid ≤ 10`) do not consume `limit` slots. Null fails: a group whose `paidAmount` is `null` (no PAID record in the group) fails every comparison; switch to `isNull()` when those groups are the target. This bites hard here: both metrics keep only PAID records while the query groups by `state.status`, so every non-PAID group evaluates `paid = 0` and `attainment = null` and is dropped — only the `PAID` row can survive. Grouping by an independent dimension (product, customer) is the pattern to use when non-PAID groups should be measurable. Having may reference only declared metric aliases (group aliases and unknown names are rejected), never an `ANY` metric; referencing derived metrics has no declaration-order restriction. The HTTP guard counts filter and having nodes toward one shared `max-filter-nodes` budget and comparison values toward `max-filter-values`. Cost follows the required ordering: metric-value top-N inherently scans every bucket (having adds no extra scan), while group-alias sorting with a selective having stops early once `limit` survivors are collected and only degrades to a full scan when survivors are sparse. See [HAVING](./aggregation-query.md#having) for semantics and rules.

## Scenario 13: Dense Time-Series Fill

**Business question**

When charting the daily order-creation trend, days without orders must still hold a place on the chart — what are the metric values on gap days?

**Counting unit**

Root snapshot documents; each current order snapshot enters one day according to the business field `state.createdAt`, and `dense: true` fills the dates between the first and last actual bucket into a consecutive series.

**Kotlin DSL**

```kotlin
val query = aggregation {
    dateHistogram(
        "state.createdAt",
        AggregationDateUnit.DAY,
        "day",
        dense = true,
    )
    count("count")
    sum("state.totalAmount", "total")
    derived("aov") { ref("total") / ref("count") }
}
```

**HTTP JSON and result interpretation**

```json
{
  "groupBy": [
    {
      "type": "DATE_HISTOGRAM",
      "field": "state.createdAt",
      "alias": "day",
      "unit": "DAY",
      "timeZone": "UTC",
      "dense": true
    }
  ],
  "metrics": [
    {"type": "COUNT", "alias": "count"},
    {
      "type": "NUMERIC",
      "function": "SUM",
      "expression": {"type": "FIELD", "field": "state.totalAmount"},
      "alias": "total"
    },
    {
      "type": "DERIVED",
      "alias": "aov",
      "expression": {
        "type": "BINARY",
        "operator": "DIVIDE",
        "left": {"type": "METRIC_REF", "metric": "total"},
        "right": {"type": "METRIC_REF", "metric": "count"}
      }
    }
  ]
}
```

```json
[
  {"day": 1767225600000, "count": 1, "total": 10.0, "aov": 10.0},
  {"day": 1767312000000, "count": 2, "total": 40.0, "aov": 20.0},
  {"day": 1767398400000, "count": 1, "total": 30.0, "aov": 30.0},
  {"day": 1767484800000, "count": 0, "total": null, "aov": null},
  {"day": 1767571200000, "count": 0, "total": null, "aov": null},
  {"day": 1769904000000, "count": 1, "total": null, "aov": null},
  {"day": 1769990400000, "count": 1, "total": 50.0, "aov": 50.0}
]
```

`day` is the bucket-start epoch milliseconds aligned to `UTC`. The window runs from the first actual bucket `2026-01-01` to the last actual bucket `2026-02-02` and fills interior gaps only: the example elides the filled days `2026-01-06..2026-01-31`; the complete result has 33 rows, 28 of which are filled rows. Filled rows follow the empty semantics — `count` is `0` and `total` is `null`, and the derived `aov` evaluates over those values and is `null` as well. Note that `2026-02-01` is an actual bucket with `count = 1`; its `total` is `null` only because that day's snapshot contributed no valid amount, which differs from a filled row's `count = 0`. Filled rows participate in sorting, `having`, and `limit`: `sort { "day".desc() }` places filled rows in reverse grid order, `having { "count" gte 1.0 }` drops exactly the filled rows, and `limit(5)` counts filled rows toward its slots. `dense` requires `DATE_HISTOGRAM` to be the only group dimension; the MongoDB backend requires server 5.1+. See [Dense Date Histograms](./aggregation-query.md#dense) for the semantics.

## Scenario 14: Missing-Value Bucket

**Business question**

When counting order items by product name, how do items whose product name is missing or null keep a place in the result instead of disappearing?

**Counting unit**

Expanded order items; `productName` is a nullable single-valued string, and items where it is missing or null land in the sentinel-key `__missing__` bucket.

**Kotlin DSL**

```kotlin
val query = aggregation {
    expand("state.items")
    terms("productName", "name", missingKey = "__missing__")
    count("lineCount")
}
```

**HTTP JSON and result interpretation**

```json
{
  "elements": [
    {"path": "state.items"}
  ],
  "groupBy": [
    {"type": "TERMS", "field": "productName", "alias": "name", "missingKey": "__missing__"}
  ],
  "metrics": [
    {"type": "COUNT", "alias": "lineCount"}
  ]
}
```

```json
[
  {"name": "Alpha", "lineCount": 1},
  {"name": "Alpha 2026", "lineCount": 1},
  {"name": "__missing__", "lineCount": 4}
]
```

The `__missing__` bucket collects the 4 of the example's 6 order items that carry no product name. The sentinel sorts as a plain string lexicographically — `"Alpha" < "Alpha 2026" < "__missing__"` (`A` is `0x41` and `_` is `0x5F`) — identically on MongoDB and Elasticsearch; the sentinel has no fixed first or last position and lands wherever the lexicographic order places it. The sentinel shares the key space with real keys: if the data really contains a product name equal to the sentinel, both merge into one bucket. `missingKey` may be declared only on single-valued string fields — nullable strings are the canonical case; multi-valued, numeric, and boolean fields are rejected at construction or schema validation, and `HISTOGRAM`/`DATE_HISTOGRAM` offer no missing bucket. See [Missing-Value Buckets](./aggregation-query.md#missing-key) for the semantics.

## Backend Capabilities and Stability Boundaries

* The Snapshot Gateway appends `DELETION = ACTIVE` by default. Direct Backend callers supply deletion scope explicitly; the normalizer and compiler do not add defaults. The root filter first selects snapshots, and each Element filter then selects individual expanded elements.
* The runtime Query Model Schema and selected MongoDB or Elasticsearch mapping jointly prove whether logical fields support exact match, range, Element scope, TERMS, numeric, or temporal aggregation. A valid request DTO does not establish backend support.
* The HTTP Handler applies QueryRequestScope and independent `HttpQueryGuard` limits before invoking `SnapshotQueryGateway`. When expensive operators are disabled, HTTP rejects Elements, metric-alias sorting, and arithmetic expressions. In-process JVM calls do not automatically receive these HTTP-only limits.
* Masked fields remain valid for ordinary filters, full-text search, and sorting. A group, field metric, or arithmetic expression that references one is rejected by Gateway public validation; `COUNT` is unchanged. See [Field Masking](./masking.md) for the complete matrix.
* MongoDB and Elasticsearch share the public AST but do not promise identical physical pipelines, mappings, null handling, or bucket details. `ANY` in particular provides no stable value across executions or backends.
* A custom `SnapshotQueryBackend` must implement the aggregation contract. Working data-query routes or published OpenAPI alone do not prove that the Backend executes aggregation.