Document pipelines

A pipeline is an ordered list of stages over a collection (or a collection group). 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.

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}

#Performance and limits

• Leading where stages become the query's filter on the documents table, so your document 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.