Query (lazy)

A Query comes from table.lazy(), dataset.lazy() (and every query method of a Dataset) and sql(). Each step returns a new Query; nothing runs until collect(), toRows(), agg() or count() (or their …Async twins). The class isn’t exported: you get one from those calls.

Before running, the planner rewrites the chain: derived columns are inlined where they are used and built only when needed as columns (then only for matching rows); filters move below derives, selects and sorts, below a group-by when they only use its keys, and into each side of a join; joins and sorts copy only the columns used later; sort followed by limit becomes top-k; params become constants. explain() prints the result. See Lazy queries and explain().

import { Engine } from 'vectrojs';             // or 'vectrojs/browser', 'vectrojs/portable'
const q = engine.table(columns).lazy();        // a Query

Steps

Each returns a new Query.

q.filter(expr, params?)

Keeps the rows where expr holds. params values are bound into the expression as constants (a Date as milliseconds, a string as text).

q.derive(name, expr, params?)

Adds a column. It is inlined into the filters, aggregations and sort keys that use it, and built as a column only if a later step needs it as one (an output column, a group key, a join key).

q.select(...names)

Keeps these columns, in this order. Names may also be passed as one array.

q.sort(by)

Sorts by a column, an expression, or an array of them; a leading - sorts large to small. Same rules as table.sort.

q.limit(n, offset?)

Keeps n rows after skipping offset (default 0). After a sort it becomes a top-k; over a dataset, reading stops once it has its rows.

q.top(k, by)

sort(by).limit(k).

q.join(other, options)

Joins with other: a Table, a Dataset or another Query (its filters are pushed into it). Options as table.join: on, how ('inner' or 'left'), suffix. other is the side that is hashed. Over a dataset, it is hashed once for all chunks; when both sides are datasets and the other side has more than 16M matching rows, both are split to disk by key range and joined range by range.

q.groupBy(keys)

Returns an object with agg(spec, params?), which returns a Query of one row per group (keys first, sorted by key). Aggregations as in table.agg. Unlike table.groupBy, there is no aggAsync here: run the query with collectAsync() or toRowsAsync().

Running

q.collect()

Runs the query and returns a new Table. Dispose it when you’re done. Also collectAsync.

q.toRows(limit?)

Runs the query and returns plain objects, at most limit. Over a dataset, a finite limit becomes part of the plan, so only those rows are read. Also toRowsAsync.

q.agg(spec)

Runs the query and computes totals over its rows: { name: value }, aggregations as in table.agg. Also aggAsync.

q.count()

The number of rows the query gives. Takes no expression; use filter(...).count(). Also countAsync.

q.explain()

The optimized plan as text, one step per line, innermost first. It doesn’t run the query.

import { Engine } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({
  customer: ['ann', 'bob', 'ann', 'cy', 'bob', 'ann'],
  region: ['EU', 'US', 'EU', 'APAC', 'US', 'EU'],
  price: [120, 80, 45, 300, 99, 15],
  qty: [2, 1, 4, 1, 3, 10],
});

const q = orders.lazy()
  .derive('revenue', 'price * qty')
  .filter("region != 'APAC' && revenue > 100")
  .groupBy('customer').agg({ spend: 'sum(revenue)', orders: 'count()' })
  .sort('-spend')
  .limit(2);

console.log(q.explain());
console.log(q.toRows());
console.log(await orders.lazy().filter('qty > 1').countAsync());
await engine.close();

import { Engine } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({ cust: [1, 2, 1, 3, 2], price: [120, 80, 45, 300, 99] });
const customers = engine.table({ cust: [1, 2, 3], tier: ['gold', 'basic', 'gold'] });

// the filter on tier moves into the right side of the join
const q = orders.lazy()
  .join(customers, { on: 'cust' })
  .filter("tier == 'gold'")
  .select('cust', 'price');

console.log(q.explain());
console.log(q.agg({ total: 'sum(price)', n: 'count()' }));
const t = await q.top(2, '-price').collectAsync();
console.log(t.toRows());
t.dispose();
await engine.close();

Steps used by SQL

q.textmap(name, spec), q.window(spec) and the statics Query.union(queries) and Query.unmatched(state, names) are how sql() builds text functions, window functions, set operations and outer joins into one plan. Their arguments are internal and may change; write those queries in SQL.