Expressions

filter, derive, agg, groupBy().agg, sort keys and lazy queries all take expressions as strings:

orders.filter("region == 'EU' && price > 100");
orders.derive('total', 'price * qty * (1 - discount)');
orders.agg({ revenue: 'sum(price * qty)', big: 'count(qty >= 10)' });

Vectro parses these itself and compiles them into WebAssembly. There is no eval and no way to reach JavaScript from an expression, so formulas typed by users or written by an AI model are safe to run. Anything the language doesn’t have is an error, never a fallback.

Values

• Numbers: 3, 2.5, .5, 1e6, 2.5e-3, and the names Infinity and NaN.
• Columns by name: price, order_id.
• Params: names you pass in a second argument (see Params).
• Text in single or double quotes: 'EU', "O'Brien". There are no escapes, so a piece of text can’t hold both kinds of quote. Text is only for comparing with a text column and for the text functions below.

Operators

The same as in JavaScript, with JavaScript’s precedence (from loosest to tightest):

1. ? : (conditional)
2. ||
3. &&
4. == and !=
5. <, >, <=, >=
6. + and -
7. * and /
8. unary - and !

Brackets group as usual. Comparisons and logic give 1 or 0, so they can be added up: sum(price > 100) is the number of rows where the price is over 100, and derive('big', 'price > 100') makes a column of 1s and 0s.

There is no %, ** or ===. == compares numbers; text is compared through its column (see below).

import { Engine } from 'vectrojs';

const engine = new Engine();
const t = engine.table({
  price: [40, 120, 300, 75, 15],
  qty: [1, 2, 1, 4, 10],
  discount: [0, 0.1, 0.2, 0, 0.05],
});

console.log(t.agg({
  revenue: 'sum(price * qty * (1 - discount))',
  bigOrders: 'sum(price * qty > 200)',
  midRange: 'count(price >= 50 && price <= 150)',
}));
console.log(t.derive('band', 'price > 100 ? 3 : price > 50 ? 2 : 1').column('band'));

await engine.close();

Math functions

Function	Result
abs(x)	Absolute value
sqrt(x)	Square root
floor(x)	Round down
ceil(x)	Round up
min(a, b)	The smaller of two values
max(a, b)	The larger of two values

With one argument inside agg, min and max are aggregations (the smallest value of a column); with two they work row by row. There is no round; floor(x * 100 + 0.5) / 100 rounds to cents. SQL has more functions (ROUND, CASE, text functions…): see SQL.

Text

A text column stores codes (see Tables). Before a query runs, text in it is swapped for those codes, and the text functions are worked out once per distinct label rather than once per row. The query itself then compares only numbers.

Expression	True when
region == 'EU', region != 'EU'	The label is (or isn’t) exactly that text
in(region, 'EU', 'US', 'UK')	The label is one of these
startsWith(region, 'EU')	The label starts with the text
endsWith(region, '-north')	The label ends with the text
contains(region, 'west')	The label contains the text
like(sku, 'A-%-__')	The label matches an SQL pattern: % any run of characters, _ one character
ilike(sku, 'a-%')	The same, ignoring case

import { Engine } from 'vectrojs';

const engine = new Engine();
const t = engine.table({
  region: ['EU-West', 'EU-North', 'US', 'APAC', 'UK'],
  sales: [10, 20, 30, 40, 50],
});

console.log(t.filter("startsWith(region, 'EU')").sum('sales'));      // 30
console.log(t.filter("in(region, 'US', 'UK')").sum('sales'));         // 80
console.log(t.filter("ilike(region, 'eu-%')").count());                // 2
console.log(t.filter("region == 'Mars'").count());                     // 0: no such label

await engine.close();

The text must come straight after the column, in quotes. A label that isn’t in the column matches no rows rather than raising an error. < and > on text aren’t supported in expressions (SQL has them).

Missing values

A missing number is NaN. Comparisons with NaN are false, so a filter such as price > 10 leaves out rows with no price. Two functions deal with missing values directly:

• isMissing(x): 1 where x is missing.
• coalesce(x, y, …): the first value that isn’t missing, e.g. coalesce(discount, 0).

Aggregations skip missing values as SQL does. The details are on Missing values.

Dates and times

A date column holds milliseconds since 1970 (UTC), so dates are numbers inside an expression. Compare a date column with ISO text and the text is turned into milliseconds before the query runs:

t.filter("ts >= '2024-03-01'")
t.filter("ts >= '2024-03-01T12:00:00Z' && ts < '2024-03-02'")
t.filter("ts < date('2024-06-01')")       // date() works anywhere in an expression

Function	Result
year(ts), month(ts), day(ts)	Calendar year, month 1–12, day 1–31
hour(ts), minute(ts), second(ts)	0–23, 0–59, 0–59
weekday(ts)	1 (Monday) to 7 (Sunday)
dayStart(ts), hourStart(ts)	The start of the day or hour, as a date
weekStart(ts)	The Monday the week starts on, as a date
monthStart(ts), yearStart(ts)	The first of the month or year, as a date
date('2024-03-01')	That moment in milliseconds

These are turned into plain arithmetic inside the same compiled loop, not a call per row: sum(year(ts)) over 10M rows takes 26 ms, against 800 ms for a JavaScript loop with Date. A column derived with dayStart, weekStart, monthStart and the others is a date column, so rows come back with Date objects and it groups by day, week or month.

import { Engine } from 'vectrojs';

const engine = new Engine();
const t = engine.table({
  ts: ['2024-03-01T09:00:00Z', '2024-03-02T18:30:00Z', '2024-03-04T07:15:00Z', '2024-04-01T12:00:00Z'],
  amount: [100, 250, 75, 40],
}, { dates: ['ts'] });

console.log(t.filter("ts >= '2024-03-02' && ts < '2024-04-01'").sum('amount'));   // 325
console.log(t.filter('weekday(ts) >= 6').count());                               // weekend rows
console.log(t.derive('week', 'weekStart(ts)')
  .groupBy('week').agg({ total: 'sum(amount)' }).toRows());

await engine.close();

Params

Any name that isn’t a column is a param: pass its value in the second argument of filter, derive, agg, the shortcuts (sum, count…) or groupBy().agg.

import { Engine } from 'vectrojs';

const engine = new Engine();
const t = engine.table({
  ts: [new Date('2024-01-10'), new Date('2024-02-20'), new Date('2024-03-05')],
  price: [100, 40, 250],
});

const since = new Date('2024-02-01');
console.log(t.filter('ts >= since && price > minPrice', { since, minPrice: 50 }).toRows());
console.log(t.sum('price * rate', { rate: 0.2 }));

await engine.close();

A param is a number or a Date (taken as milliseconds). The expression text stays the same while the values change, so the compiled kernel is reused, and values never become part of the text, which keeps user input out of it. A param can’t share a name with a column.

Text can’t be a param, so text has to be written into the expression in quotes. When it comes from a user, check it against the labels you expect first: a value with a quote in it could otherwise change what the filter matches (it still can’t run code).

Filters chained one after another keep their params; giving the same name a different value further down the chain is an error.

Aggregations

Inside agg and groupBy().agg, each entry is one aggregation around an expression:

Aggregation	Result
sum(x)	Total of the values present
mean(x)	Average of the values present
min(x), max(x)	Smallest, largest value present (NaN if none)
count()	Rows
count(x)	Rows where x is present; for a condition, rows where it holds
countDistinct(x)	Distinct values, exact
approxDistinct(x)	Distinct values; on split tables and streams an estimate within about 1.6%

The aggregation has to be the outermost call: sum(price * qty) works, sum(max(price)) and sum(price) / count() don’t. Compute ratios from the results in JavaScript, or use SQL. See Filter, derive, aggregate and Distinct counts.

Errors

Mistakes are reported when the query is built, before anything runs, with the expression in the message:

vectro: unknown column "cost" in "cost > 1" (columns: region, price, qty)
vectro: unknown function round() with 1 argument(s) in "round(price)"
vectro: unexpected "%" in "qty % 2"
vectro: "EU" can only be compared with a text column, in "price == 'EU'"