> ## Documentation Index
> Fetch the complete documentation index at: https://docs.bijection.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Joins and Aggregates
> Combine tables with equality joins and summarize them with grouped aggregates
A view can combine several tables. Joins attach matching rows from another
table, and grouping reduces many rows to one row per group, which you then join
back to the view's key table.
This view shows each order next to the name of its customer:
```ts bijection/schema.ts {15-19} theme={"theme":{"light":"github-light-default","dark":"github-dark-default"}}
import { defineSchema, defineTable, defineView, q } from "bijection/server";
import { v } from "bijection/values";
export default defineSchema({
customers: defineTable({ name: v.string() }),
orders: defineTable({
customer_id: v.id("customers"),
amount_cents: v.int64(),
}).index("by_customer", ["customer_id"]),
order_details: defineView({
key: { from: "orders", field: "_id" },
expression: q
.table("orders")
.as("order")
.leftJoin(q.table("customers").as("customer"), {
left: "order.customer_id",
right: "customer._id",
index: "by_id",
})
.select({
amount_cents: q.field("order.amount_cents"),
customer_name: q.field("customer.name"),
}),
}),
});
```
## Joins
`.leftJoin(right, options)` and `.innerJoin(right, options)` match each row of
the current relation with rows of `right` whose fields are equal:
| Option | Description |
| - | - |
| `left` | The field on the current relation to match, such as `"order.customer_id"`. |
| `right` | The field on `right` to match, including `right`'s alias, such as `"customer._id"`. |
| `index` | The index on `right`'s table used to find matches. Required when `right` is a table. |
| `cardinality` | `"one"` (the default) or `"many"`. How many right rows may match one left row. |
The two kinds differ only when there's no match:
* **`leftJoin`** keeps the left row. The right side's fields are optional in the
output type, so `customer_name` above is `string | undefined`.
* **`innerJoin`** drops the left row.
### The right side
The right side of a join is either:
1. **A table**, written `q.table(...)`, optionally with `.as()` and
`.filter()`. Name an index on that table with `index`. The index must begin
with the `right` field. To join on the right table's `_id`, use the built-in
`"by_id"` index.
2. **A grouped relation**, written `q.table(...).groupBy(...).aggregate(...)`,
optionally with `.as()`. Don't pass `index`. See [Grouping](#grouping).
A right side built with `.select()` or with another join isn't supported.
Give both sides an alias with `.as()`. A join combines the fields of both rows,
and Bijection refuses a join whose two sides have a field with the same name.
### Cardinality
A view has [one row per key](/views/defining-views#keys), so joins in a view's
main chain use `cardinality: "one"`: at most one right row may match each left
row. Bijection checks this as it reads. If a second row matches, the read fails
with a `ViewCardinalityViolation` error rather than returning two rows for one
key.
Make sure the data can't have two matches:
* Join on the right table's `_id`, as `order_details` does.
* Join a [link type](/views/links) on an endpoint whose maximum is `1`.
* Join a grouped relation on its grouping key, which has one row per group.
A `cardinality: "many"` join in the main chain would produce several rows for
one key, so Bijection refuses that view when you deploy.
## Grouping
`.groupBy(fields).aggregate({ ... })` returns one row per distinct combination of
the grouping fields, with one output field per aggregate:
```ts bijection/schema.ts theme={"theme":{"light":"github-light-default","dark":"github-dark-default"}}
const orderTotals = q
.table("orders")
.groupBy(["customer_id"])
.aggregate({
order_count: q.count(),
total_cents: q.sum("amount_cents"),
})
.as("totals");
```
The grouped row contains the grouping fields and the aggregates: here
`customer_id`, `order_count` and `total_cents`. A grouping field given as a
path, such as `"order.customer_id"`, is named after its last segment.
| Aggregate | Result | Result type |
| - | - | - |
| `q.count()` | The number of rows in the group. | `bigint` |
| `q.sum(field)` | The sum of a numeric field. | The field's type: `bigint` for `v.int64()`, `number` for `v.number()` |
`q.sum` requires the field to be declared as `v.int64()` or `v.number()`, and
every row in the group to have a finite value for it. Counts are exact integers,
and so are sums of `v.int64()` fields.
### Joining a grouped relation
A grouped relation doesn't have a key of its own, so it can't be a view by
itself. Join it to the view's key table on its complete grouping key, in the
order you grouped by:
```ts bijection/schema.ts {6-9} theme={"theme":{"light":"github-light-default","dark":"github-dark-default"}}
customer_summaries: defineView({
key: { from: "customers", field: "_id" },
expression: q
.table("customers")
.as("customer")
.leftJoin(orderTotals, {
left: "customer._id",
right: "totals.customer_id",
})
.select({
name: q.field("customer.name"),
order_count: q.field("totals.order_count"),
total_cents: q.field("totals.total_cents"),
}),
}),
```
The table you group needs an index that begins with the grouping fields. Here
`orders` has `by_customer` on `["customer_id"]`, which Bijection uses to read
one customer's orders without scanning the table.
A group with no rows has no grouped row. A customer without orders therefore
has no match in `orderTotals`, and with `leftJoin` its `order_count` is
`undefined`, not `0n`.
### Missing and null values
A missing field and a field set to `null` are different values for grouping and
joins. Rows where `region` is missing form one group, rows where `region` is
`null` form another, and each only matches rows with the same kind of value.
## Compound join keys
Compound join keys are in beta.
`left` and `right` can each be an array of one to three distinct fields, with
the same length on both sides. A row matches when every pair of fields is
equal:
```ts bijection/schema.ts {9-12} theme={"theme":{"light":"github-light-default","dark":"github-dark-default"}}
const salesTotals = q
.table("sales")
.groupBy(["region", "product_code"])
.aggregate({ units: q.sum("units") })
.as("sales");
q.table("sales_targets")
.as("target")
.leftJoin(salesTotals, {
left: ["target.region", "target.product_code"],
right: ["sales.region", "sales.product_code"],
})
.select({
target_units: q.field("target.units"),
sold_units: q.field("sales.units"),
});
```
The same rules apply as for a single field. An index on a right table must begin
with all the `right` fields in order. A grouped right side must be joined on its
complete grouping key, in the order of `groupBy`; a prefix of the key isn't
enough to guarantee one match. Here `sales` needs an index beginning with
`["region", "product_code"]`.
## Exact decimal and distribution aggregates
Exact decimal and distribution aggregates are in beta.
These aggregates add exact decimal arithmetic and distribution statistics to
`aggregate`:
| Aggregate | Result | Result type |
| - | - | - |
| `q.decimalSum(field, scale)` | The exact sum of decimal strings, as text with `scale` decimal places. | `string` |
| `q.countDistinct(field)` | The number of distinct values of `field`. | `bigint` |
| `q.percentile(field, basisPoints)` | The value at a percentile of a numeric field. | The field's type |
| `q.decimalPercentile(field, basisPoints, scale)` | The value at a percentile of a decimal string field, with `scale` decimal places. | `string` |
```ts bijection/schema.ts theme={"theme":{"light":"github-light-default","dark":"github-dark-default"}}
const invoiceStats = q
.table("invoices")
.groupBy(["customer_id"])
.aggregate({
total: q.decimalSum("amount", 2),
median_amount: q.decimalPercentile("amount", 5000, 2),
currency_count: q.countDistinct("currency"),
})
.as("stats");
```
* **Decimals.** `decimalSum` and `decimalPercentile` read a field declared as
`v.string()` holding decimal text such as `"1250.40"`. Values are never
converted to floating point. `scale` is an integer from 0 to 38. A value that
would need rounding to fit `scale`, a value that isn't a decimal, and a result
with more than 38 digits fail the read with an `InvalidDecimal` or
`DecimalOverflow` error.
* **Percentiles.** `basisPoints` is an integer from 0 to 10,000, where 5,000 is
the median. The result is the first observed value at which the cumulative
share of rows reaches the requested fraction, and 0 selects the minimum. It
never interpolates between values or approximates. `percentile` requires a
`v.int64()` or `v.number()` field.
* **Distinct counts.** A missing value and `null` count as two different values.
`countDistinct` and the percentiles keep the group's values while they're
computed, and those bytes count against the view's
[`max_bytes` limit](/views/defining-views#work-limits).