Joins and 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:

bijection/schema.ts

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:

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.
A grouped relation, written q.table(...).groupBy(...).aggregate(...), optionally with .as(). Don’t pass index. See 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, 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 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:

bijection/schema.ts

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:

bijection/schema.ts

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:

bijection/schema.ts

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`

bijection/schema.ts

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.

Get Started

Platform

Data and Operations

Guides

Client Libraries

Tools

API Reference

Joins and Aggregates

Joins

The right side

Cardinality

Grouping

Joining a grouped relation

Missing and null values

Compound join keys

Exact decimal and distribution aggregates

​Joins

​The right side

​Cardinality

​Grouping

​Joining a grouped relation

​Missing and null values

​Compound join keys

​Exact decimal and distribution aggregates

Joins

The right side

Cardinality

Grouping

Joining a grouped relation

Missing and null values

Compound join keys

Exact decimal and distribution aggregates