Queries

A query is the read mirror of an action. Where an action is a typed write into a source dataset, a query is a typed, read-only read over one dataset, declared in the manifest and called on demand. You don't write SQL: a query is a declarative spec (a dataset, optional filters, a projection, an order) that the compiler translates to a parameterized SELECT. You declare a query; an agent runs it over MCP with oi.app().runQuery.

Why declarative

A query is a spec, not raw SQL in a JSON string. The compiler reads the spec and emits the SQL. That buys five things:

• No casting footgun. The translator emits type-aware casts itself. You never write a TRY_CAST or an ILIKE; you name a field and an op and it compiles to the right comparison.
• Fields are validated. Every field you reference is checked against the dataset's live columns at build time, like an island binding. A field that doesn't exist is a named error, not an empty result.
• Injection-safe. Every param and every literal value is bound, never interpolated. Only verified column identifiers are quoted into the SQL.
• Agent-authorable. The spec is plain JSON, so an agent writes a query through the normal oi.app().patchManifest → applyEdit loop, the same loop it uses for islands (see Authoring queries).
• No joins by design. A query reads one dataset. When a read needs joins or other heavy shaping, that lives in a sql transform the query's dataset points at.

What a query reads

One dataset: a raw source dataset or a sql transform, named by dataset. Because a transform is itself a dataset, a query over a transform reads already-shaped, already-joined columns. Think of a query as a saved, named, typed version of the ad-hoc oi.app().runSql read, scoped to a single dataset.

A query is the dual of a transform, not a replacement for one. A transform is a file-backed view bound to islands: it renders, takes no parameters, and shapes data once. A query renders nothing, takes parameters, and returns rows when called. A query often selects from a transform: let the transform do the joins and aggregation, and let the query parameterize the read.

Declaring one

A query names its dataset and, optionally, the params it accepts and how to where / select / orderBy / limit the rows:

"queries": {
  "get_daily_macros": {
    "description": "Macros + goals for one day; omit date for the latest.",
    "dataset": "macros_daily",
    "params": { "date": { "type": "date", "required": false } },
    "where": [{ "field": "date", "op": "eq", "param": "date" }],
    "orderBy": [{ "field": "date", "dir": "desc" }],
    "limit": 1
  }
}

That reads one row from macros_daily. With a date, it's that day; with no date, the filter drops and the orderBy + limit return the latest row (see Filters).

Parameters

params maps each parameter name to its declaration. A where clause references a param by name in its param field; the value is bound when the query runs.

Filters

where is a list of filters, ANDed together. Each names a field, an op, and exactly one of param (bind a declared param) or value (a literal):

A filter bound to an optional param the caller omits is dropped; the query runs as if the filter weren't there. That's the latest-row pattern above: omit date and the day filter disappears, leaving order by date desc limit 1.

Projection

select is a list of columns to return; omit it for all of them. An entry is either a column name or { field, fn?, as? }, where fn is sum, avg, count, min, or max. Aggregates pair with groupBy, a list of columns to group by:

"queries": {
  "spend_by_category": {
    "dataset": "transactions",
    "select": [{ "field": "category" }, { "field": "amount", "fn": "sum", "as": "total" }],
    "groupBy": ["category"],
    "orderBy": [{ "field": "total", "dir": "desc" }],
    "limit": 10
  }
}

orderBy is a list of { field, dir? } sort keys (dir is asc or desc, default asc), and limit caps the rows.

Search

search turns a query into a relevance-ranked full-text search over text columns. It compiles to DuckDB's full-text index and ranks rows by BM25, so a multi-word term matches rows that share any token, best match first. That's the difference from a contains filter: contains is a whole-phrase, case-insensitive substring match and returns rows unranked; search tokenizes both the columns and the term and orders by likeliness. Searching greek yogurt returns Greek Yogurt above Olympus Yogurt — which only shares the token yogurt — and excludes Banana.

"queries": {
  "search_ingredients": {
    "description": "Find ingredients by name or brand, best match first.",
    "dataset": "ingredients",
    "params": { "q": { "type": "string" } },
    "search": { "fields": ["name", "brand"], "param": "q", "scoreField": "score" },
    "limit": 20
  }
}

A search clause names:

search composes with the rest of the spec. A where clause further filters the matched rows; an omitted orderBy defaults to relevance (highest BM25 first), and an explicit orderBy overrides that order; limit caps the rows as always.

search reads a source dataset, not a sql transform. (A full-text index can't track changes to the files a transform reads, so it would silently go stale.) Because the index needs a real table, declaring search materializes the dataset into an indexed table at build time — the manifest gates that, and it's invisible to the query.

Running a query

Queries belong to the agent edit loop, not to a CLI command. An agent:

1. Calls oi.app().listQueries() to get each declared query: its name, description, its params as a JSON Schema, and the result columns. That's its grounding for what to pass and what comes back.
2. Calls oi.app().runQuery(name, params?, { limit? }), which validates the params and runs the compiled SELECT. On success it returns { ok: true, rowCount, columns, rows }. A bad param rejects the whole call with { ok: false, errors } (all-or-nothing); an unknown name or a query error returns { ok: false, error }. limit is 1–500 and the result is row-capped regardless.

Authoring queries

Because a query is declarative JSON, an agent writes one end-to-end through the same oi.app().patchManifest → applyEdit loop it uses for islands and actions. No new tool, no file write. It reads the data, drafts the queries block, stages the change, reviews the diff, and applies it. The MCP write path only ever writes manifest.json, so a JSON spec is what lets an agent create a read tool, not just run one.

patchManifest / replaceManifest and validate enforce the same checks: the dataset must exist, and every field in where, select, groupBy, and orderBy must be a real column on it. A field that doesn't exist is a named error, caught before the manifest lands: the same fail-loud binding check an island gets.

Where to go next

• SQL Transforms: the file-backed views a query selects from.
• Actions: the typed write a query mirrors.
• MCP Server: oi.app().listQueries() and runQuery inside the agent loop.