Aggregate / SQL API

Before you can chart data you usually have to shape it. aggregate() does group-by + measures with zero dependencies - the model-free default for "roll raw rows into a chart". For real in-browser SQL over big data, an opt-in DuckDB-Wasm engine is available. For the full story see the Insights guide.

Try it - flip the grouping and watch raw rows re-roll into a chart:

aggregate · rows → chart

12 raw rows

region	product	rev	tgt
North	Widget	42	38
North	Gadget	28	30
North	Bundle	35	30
South	Widget	19	22
South	Gadget	24	20
South	Bundle	14	16

…6 more

aggregate →

One call - aggregate(rows, { groupBy: "region", measures: { revenue: { col:"revenue", fn:"sum" }, target: { col:"target", fn:"sum" } } }) - rolls 12 rows into 0 bars (revenue vs target, sorted). Model-free and deterministic; opt into DuckDB-Wasm for real SQL over millions of rows.

Import

import { aggregate, createSqlEngine } from "@michi-vz/insights/sql";
// also re-exported from the package root: import { aggregate } from "@michi-vz/insights";

aggregate - pure group-by

const rows = [
  { region: "North", revenue: 42, target: 38 },
  { region: "North", revenue: 28, target: 30 },
  { region: "South", revenue: 19, target: 22 },
];

aggregate(rows, {
  groupBy: "region",
  measures: { revenue: { col: "revenue", fn: "sum" }, target: { col: "target", fn: "sum" } },
  orderBy: { key: "revenue", dir: "desc" },
});
// → [{ region: "North", revenue: 70, target: 68 }, { region: "South", revenue: 19, target: 22 }]

Field	Type	What it does
groupBy	string | string[]	Column(s) to group on. Omit for a single grand-total row.
measures	Record<string, { col: string; fn: MeasureFn }>	Output column -> source column + aggregation.
where	(row: Row) => boolean	Optional row filter applied before grouping.
orderBy	{ key: string; dir?: "asc" | "desc" }	Optional sort of the output rows.
limit	number	Optional cap on the number of output rows.

MeasureFn = "sum" \| "avg" \| "min" \| "max" \| "count". Row = Record<string, unknown>. Pure and deterministic; non-numeric measure values coerce to 0.

createSqlEngine - opt-in DuckDB-Wasm

For real SQL (joins, window functions) over CSV/Parquet/Arrow at scale, lazy-load a DuckDB-Wasm engine. It returns null when the optional @duckdb/duckdb-wasm dep isn't installed, so callers fall back to aggregate().

const engine = await createSqlEngine();
if (engine) {
  await engine.registerTable("sales", rows);
  const out = await engine.query("SELECT region, SUM(revenue) FROM sales GROUP BY region");
  await engine.close();
}

SqlEngine = { query(sql): Promise<Row[]>; registerTable(name, rows): Promise<void>; close(): Promise<void> }.

Insights guide