Selecting rows

Intent

A JSONPath selects the record set, and filtering, ordering, and ranking are separate structured steps on top. Four keys do the work, and the order they run in decides what every aggregate in the document means.

Design decisions

data is a path, never an expression. A = in data is a literal character. The one selection mechanism is JSONPath, so the shape of the query stays analysable and the engine can bound it.

where sees one row, so it can’t rank. It’s a per-row boolean and nothing more. Ranking is take, which is the only key that can see the set.

A filter selector reaches where where can’t. A selector sits at any segment of the path, including segments above the row set. That’s how a report selects rows by a property of their container.

A filter selector and where don’t mean the same thing by the same words. RFC 9535 reads a bare relative query as an existence test, while where is an ordinary boolean and tests truthiness.

take runs after the sort, and a path slice runs before it. A slice is selection, so it picks rows by their position in the input. A negative step can reverse that order, but never ranks it. That’s why a slice isn’t a top-N.

Rows take drops leave the aggregates too. That’s the same removal where performs, and it’s what separates take from visible, which is presentation and keeps its rows in the totals.

A group ranks its instances apart from their rows. A group’s sort and take work inside each instance. instances orders and cuts the instances themselves, so “the five largest regions” is one declaration rather than a pre-sorted query.

An instance instances.take drops still counts in the totals around it. It never renders, but the group aggregates around it and the report aggregates include its rows, as they include a row a group’s take drops. A top five of regions therefore reads against the grand total of every region.

The query carries a traversal budget by default. Render input may be untrusted and unbounded descent would exhaust the call stack, so maxDepth defaults to 500 rather than to nothing.

API walkthrough

Choosing the record set

data is an RFC 9535 JSONPath selecting the row array from your input.

{ "data": "$.orders[*]" }

A path may carry a filter selector, which is JSONPath’s own expression dialect rather than quario’s.

{ "data": "$.regions[[email protected] == true].orders[*]" }

That takes the orders of active regions only. No where expresses it, because @ binds to the order and an order carries no reference to its region.

Write the comparison out. [[email protected]] keeps a region whose active is false, because the member exists, where where: "[email protected]" would drop it.

Selectors may use match(value, pattern) for a full match and search(value, pattern) for a substring match. Patterns follow bounded RFC 9485 I-Regexp semantics, not JavaScript RegExp, so \d, lookarounds, and backreferences fall outside it. The compiler checks a pattern written as a literal. A pattern arriving from data produces no match at render rather than throwing.

Dropping rows

where is a boolean evaluated per row with @ bound to that row. The report drops rows returning falsy. It’s report-level only.

{ "where": "[email protected] != 'void'" }

A report aggregate read inside where is null, because where runs before the aggregates exist. There is no earlier moment that could supply one.

Ordering

sort takes keys applied left to right. The first key is the primary key. dir defaults to "asc", and the sort is stable, so rows whose keys compare equal keep the order they arrived in.

{ "sort": [{ "by": "[email protected]" }, { "by": "[email protected]", "dir": "desc" }] }

Keys compare with JavaScript ordering, so a mixed-type or nullish key orders by that ordering. Keep a key one type where the order matters.

A sort on the report orders the whole set. A sort on a group orders the rows within each instance.

A report aggregate read in a report sort key is null, for the same reason it’s null in where. A group’s sort key is the one exception. The engine computes a group instance’s aggregates over its partition before it orders the rows, so =region.subtotal - @.amount works. Declaring take on that group withdraws the exception, since ranking has to happen first.

Ranking

take keeps the first N rows after that node’s sort. It’s a literal integer of one or more.

{ "sort": [{ "by": "[email protected]", "dir": "desc" }], "take": 5 }

Sort descending and take N for a top-N, ascending for a bottom-N. Without a sort it keeps the first N as they arrived, and a count larger than the set keeps every row.

A take on the report ranks the whole set. A take on a group ranks within each instance.

To show five rows and still total over all of them, use visible with a count runner instead. Suppressed rows stay in the aggregates.

Ranking the instances

instances sits on a group beside its sort and take, and holds an optional sort and take of its own.

{
  "name": "region",
  "by": "[email protected]",
  "aggregates": { "subtotal": "sum:[email protected]" },
  "instances": { "sort": [{ "by": "=region.subtotal", "dir": "desc" }], "take": 5 }
}

An instances.sort key reads what a group band reads. That’s the group’s own handle, the handles of the groups around it, and $. @ is unbound, so @.amount throws there. run is unbound too. Without instances.sort, instances appear in the order their first row arrived, and keys that compare equal keep that order. instances.take keeps the first N in the resulting order. A hollow instance still uses one of the N places, because the cut happens before anything renders.

The order of operations

Select with data, drop with where, apply the report sort, apply the report take, then partition into groups, each group applying its own sort then take. Each group then orders and cuts its instances by instances. An instances.sort key therefore sees the aggregates of the rows each instance keeps.

Aggregates and running totals cover the rows that remain, in the resulting render order, so a running total follows what the reader sees.

Bounding the traversal

Budgets are instance options, never schema fields.

import { quario } from 'quario'

const q = quario({ query: { maxDepth: 64, maxNodes: 10_000, maxResults: 1_000 } })

An explicit maxDepth replaces the default of 500, and Infinity disables a budget. Exceeding one throws a located RangeError carrying code, limit and actual.