# Document pipelines
Source: https://sovaria.in/docs/document-pipelines

Filter, group, aggregate, sort and reshape documents in one request — like Firestore pipelines — run inside your project's database as the caller.

A pipeline is an ordered list of **stages** over a collection (or a [collection group](https://sovaria.in/docs/document-queries#collection-groups)). Each stage takes the previous stage's output: filter with `where`, group and total with `aggregate`, then sort, page and pick fields. The whole pipeline compiles to one SQL statement and runs in your project's Postgres as the caller, so collection rules and row policies apply to every document it reads.

| Stage | Example | What it does |
|---|---|---|
| `where` | `["status", "==", "paid"]` | Filters, with the same operators and `or` / `and` groups as [queries](https://sovaria.in/docs/document-queries#filters). After an `aggregate` it filters the groups by their aliases. |
| `aggregate` | `{"accumulators": [{"op": "sum", "field": "amount", "alias": "total"}], "groups": ["country"]}` | One row per group (or one row for everything). Ops: `count`, `count_distinct`, `sum`, `avg`, `min`, `max`. |
| `distinct` | `["country", "city"]` | One row per distinct combination. |
| `sort` | `[["total", "desc"], "name"]` | Orders the rows; carried through later stages. |
| `offset` / `limit` | `20` / `10` | Skip / keep rows (in the current sort order). |
| `select` | `["name", {"field": "address.city", "as": "city"}]` | Keeps only these fields. A nested field is named by its last segment unless you give `as`. |
| `remove_fields` | `["secret", "meta.raw"]` | Drops fields. |
| `sample` | `50` | That many rows at random. |

HTTP:
```bash
curl -s -X POST $URL/api/v1/data/doc/_pipeline/orders -H "Authorization: Bearer $TOKEN" -d '{
  "stages": [
    {"where": [["status", "==", "paid"]]},
    {"aggregate": {"accumulators": [{"op": "sum", "field": "amount", "alias": "total"},
                                    {"op": "count", "alias": "orders"}], "groups": ["country"]}},
    {"where": [["total", ">", 1000]]},
    {"sort": [["total", "desc"]]},
    {"limit": 5}
  ]}'
# → {"results": [{"data": {"country": "in", "total": 18250, "orders": 97}}, …], "count": 5, "documents_read": 4120}
```

JavaScript:
```js
const top = await sov.api.data.docPipeline({ collection: 'orders', stages: [
  { where: [['status', '==', 'paid']] },
  { aggregate: { accumulators: [{ op: 'sum', field: 'amount', alias: 'total' }], groups: ['country'] } },
  { sort: [['total', 'desc']] }, { limit: 5 },
] })
```

Python:
```python
top = await sov.api.data.doc_pipeline(collection="orders", stages=[
    {"where": [["status", "==", "paid"]]},
    {"aggregate": {"accumulators": [{"op": "sum", "field": "amount", "alias": "total"}], "groups": ["country"]}},
    {"sort": [["total", "desc"]]}, {"limit": 5},
])
```

CLI:
```bash
sov collections pipeline orders -f top-countries.json
sov collections pipeline comments --group --stages '[{"aggregate": {"accumulators": [{"op":"count","alias":"n"}], "groups": ["author"]}}]'
```

## Performance and limits

- Leading `where` stages become the query's filter on the documents table, so your [document indexes](https://sovaria.in/docs/document-queries#indexes) apply. Put the most selective filter first.
- A `sort` followed by `limit` keeps Postgres' top-N sort: only the rows it returns are fully sorted.
- A pipeline that doesn't start (after its filters) with a `limit` reads at most your plan's *documents read per pipeline*. If it would read more it is **refused**, never answered from part of the data — add a `where` or a `limit`.
- sum, avg, min and max use numbers (min/max fall back to strings when a group has no numbers); other values are skipped, like Firestore.

|  | Free | Pro | Ultra | Team | Enterprise |
|---|---|---|---|---|---|
| Document pipeline queries (where, aggregate, sort, limit, select) | ✓ | ✓ | ✓ | ✓ | ✓ |
| Stages per document pipeline | 5 | 10 | 20 | 30 | 50 |
| Documents one pipeline may read | 10,000 | 100,000 | 500,000 | 1,000,000 | 5,000,000 |
| Documents one collection scan may return | 1,000 | 5,000 | 10,000 | 25,000 | 50,000 |