Database Queries#
The database Service accepts query objects. Collection and field names refer to the declared Data Collections. Examples below use a PurchaseOrder with id, timestamp, deleted, reference, total, status and a supplier reference to Supplier. Supplier has id, name and deleted. Acquire the declared database Service inside the function.
Query Properties#
| Property | Meaning |
|---|---|
from |
Collection name, optionally followed by fields in parentheses. |
where |
Conditions on the source records. |
groupBy |
Array of grouping field expressions. |
orderBy |
Object mapping field expressions to asc or desc, or a priority definition described below. |
distinct |
Return distinct combinations of the selected values. |
offset |
Non-negative number of result rows to skip. |
count |
Positive maximum number of result rows; omitted means 25. |
locking |
Read lock, for example { type: "forUpdate" }. |
set |
Fields and values for db.update, a bulk update. |
options |
Optional read tuning: { fetchSize: 100 } requests that fetch batch size. This does not change the result limit. |
Filtering precedes grouping; ordering and paging apply to the resulting rows. There is no supported having interface. To apply a condition to a grouped result, inspect the grouped rows in code; do not confuse filtering one page with filtering the entire grouped result.
Projections And References#
query = {
from: "PurchaseOrder (id, reference, supplier (id, name), total)",
where: { "supplier.id": input.supplierId, deleted: false },
orderBy: { timestamp: "desc", id: "asc" },
offset: input.offset,
count: input.pageSize
}
The projection selects loaded fields, not filter fields. A filter or ordering field need not appear in the projection. The example can order by timestamp without returning it. supplier (id, name) loads fields from the referenced Supplier; deeper references use nested parentheses. "supplier.name" in a condition follows that reference. It is not a field called supplierId, nor a second query written by the author.
An explicit projection is recommended. With no projection, db.query selects the Collection's non-reference fields; db.first defaults to id only. db.first returns at most one record regardless of the query's count, or null when none matches. Supply ordering if the choice among matching records matters. A bare reference such as supplier is not a loaded projection: enumerate supplier (id) or the required related fields.
Load all fields needed by the code. A projection does not fetch other fields on demand. Reading a reference does not automatically exclude a deleted target: add "supplier.deleted": false when the operation requires an active Supplier as well as an active order. References are single linked records; this syntax does not hydrate arrays of references or named inverse relationships.
Conditions#
| Form | Meaning |
|---|---|
{ status: "draft", deleted: false } |
Both equalities must match. |
{ total: { op: "gte", value: 100 } } |
Greater than or equal. Other comparisons: eq, neq, gt, lt, lte. |
{ notes: { op: "nil" } } |
Value is null. nnil means not null. Do not use scalar null as equality. |
{ status: { op: "in", value: ["draft", "approved"] } } |
Match a value in the array. nin excludes those values. Handle empty arrays in code before querying. |
{ total: { op: "btw", value: [100, 500] } } |
Inclusive bounds; nbtw excludes the interval. |
{ reference: { op: "like", value: "PO-%" } } |
Case-sensitive pattern; ilike is case-insensitive. % matches a sequence, _ one character. |
{ reference: { op: "ilike", value: "TMP-%", negate: true } } |
Negate the condition. |
{ tags: { op: "overlap", value: ["priority", "review"] } } |
A declared array field shares at least one value with the supplied array. |
{ tags: { op: "contains", value: ["priority", "review"] } } |
The array field contains all supplied values. |
{ tags: { op: "containedBy", value: ["priority", "review"] } } |
Every value in the array field is in the supplied array. |
Array conditions require a declared array field and values matching its element type. They do not mean that reference arrays are automatically loaded as linked records. Membership (in) compares a scalar field against candidate values; it is different from testing an array field with overlap or contains. Object equality uses { op: "eq", value: expectedObject }; a bare object is interpreted as a condition definition, not automatically as an object value.
Keep interval conditions inside an and group, as in the example below. For exclusive date/time bounds use separate gte and lt conditions instead of btw. Pattern wildcards are pattern syntax, not a literal substring-escaping facility. Do not invent an escape option.
Use and and or arrays for nested conditions or multiple comparisons on the same field:
filter = { and: [
{ deleted: false, "supplier.deleted": false },
{ timestamp: { op: "gte", value: start } },
{ timestamp: { op: "lt", value: until } },
{ or: [
{ status: "draft" },
{ total: { op: "gte", value: 1000 } }
] }
] }
Object-property paths use /, for example "details/country". Reference paths use ., for example "supplier.name". A condition on an object property can use { "details/country": "FR" }. Declared field types still apply. Names and expressions come from the design, not unchecked caller input.
Ordering And Paging#
Ordering keys are evaluated in their declared order. Add an id tie-breaker for stable record pages. Offset starts at zero. A cursor visits the returned page; it does not automatically fetch every matching row. To process all results, explicitly request successive bounded pages with stable ordering. Changes to the underlying records can move offset-based pages; paging does not itself provide a frozen snapshot.
Priority ordering places one value first, then uses the next ordering fields:
orderBy = {
status: { priority: "draft" },
timestamp: "desc",
id: "asc"
}
This places drafts before other statuses. It does not sort the other statuses alphabetically. priority is a string value, not a ranking list. When priority is present, direction does not reverse that priority. There is no documented null-placement option.
distinct: true compares the complete selected row. To list distinct statuses, select status alone, order by status, and page those rows. Selecting id as well makes different record identities distinct even when their statuses match.
Field Functions And Aggregates#
A field function uses field:function, for example total:sum. Use that exact expression in the projection, grouping or ordering as appropriate. These are database functions, not methods on a Record.
| Function | Meaning |
|---|---|
count |
Count non-null field values; id:count counts records with an id. |
ucount |
Count distinct non-null values. |
sum, avg |
Sum or average numeric values. |
usum, uavg |
Sum or average distinct numeric values. Equal amounts are counted only once. |
min, max |
Minimum or maximum numeric value. |
std_dev, std_dev_pop |
Sample or population standard deviation. |
var_samp, var_pop |
Sample or population variance. |
minute, hour, day, week, month, quarter, year |
Truncate a date/time to the start of that period. |
Date truncation and calendar snippets use the database's time context. For a caller-selected timezone, calculate datetime bounds with Dates and filter explicitly. They do not inherit an Input named timezone automatically.
Non-aggregate selected fields belong in groupBy. For example, group orders by supplier and status, then order by their total:
execute(input)
db = service "database_default"
cursor = db.query({
from: "PurchaseOrder (supplier (id, name), status, id:count, total:sum)",
where: { deleted: false, "supplier.deleted": false },
groupBy: ["supplier.id", "supplier.name", "status"],
orderBy: { "total:sum": "desc", "supplier.id": "asc", status: "asc" },
offset: input.offset,
count: input.pageSize
})
groups = []
for each row in cursor
data = row.data()
groups.add({
supplier: data.supplier,
status: data.status,
orderCount: data["id:count"],
total: data["total:sum"]
})
end
return { groups: groups }
end
This Process requires non-negative integer offset and positive bounded integer pageSize Inputs, the declared database Service and both Collections. Its groups Output is an array of objects containing supplier (id/name), status, orderCount (integer) and total (decimal). Function results keep their expression as the loaded key, such as "total:sum"; there is no AS alias syntax. Map them to business Output names in code. Counts can be zero; other aggregates can be null when no values contribute. Handle null according to the Output contract.
For daily totals use from: "PurchaseOrder (timestamp:day, id:count, total:sum)", groupBy: ["timestamp:day"] and orderBy: { "timestamp:day": "asc" }, with the appropriate active-record and time filters.
db.count({ from: "PurchaseOrder", where: filter }) counts matching source records, not grouped result rows. Do not use it as the total number of groups. distinct: true removes duplicate selected combinations; it is not a substitute for grouping or ucount. With paging, counts and sums calculated over returned rows describe only that page.
Named Condition Snippets#
A snippet is a named database condition. Use fn inside a field condition, not a new method and not a projection function:
query = {
from: "PurchaseOrder (id, reference, total)",
where: { deleted: false, timestamp: { fn: "LastNDays", value: 7 } },
orderBy: { timestamp: "desc", id: "asc" },
offset: 0,
count: 100
}
LastNDays selects complete calendar days preceding today; it excludes today. Today selects the current calendar day. negate: true negates a snippet condition too.
| Snippets | Meaning / value |
|---|---|
BeforeNow, AfterNow |
Strictly before/after the current database time; no value. |
ThisMinute, ThisHour, Today, ThisWeek, ThisMonth, ThisYear |
Current calendar period; no value. |
LastNMinutes, LastNHours, LastNDays, LastNWeeks, LastNMonths, LastNYears |
Previous N complete periods, excluding the current period; positive integer value. |
NamedHour |
Hour 0-23 of today. |
NamedDayOfWeek |
Day 1-7 of the current week, starting Monday. |
NamedDay |
Day of the current month; valid integer day for that month. |
NamedMonth |
Month 1-12 of the current year. |
NamedYear |
Calendar year as integer. |
StartOfDay |
At or after today's start; no upper bound. |
EndOfDay |
Before tomorrow's start; no lower bound. |
LastMonth |
Previous complete calendar month. |
WeekToDate, MonthToDate, QuarterToDate, YearToDate |
From period start through now. |
Snippet values are template substitutions, not ordinary parameter bindings. Only use validated integer values in these snippets, never caller-supplied text or expressions. For arbitrary dates, use ordinary comparison conditions with bound datetime values. Authors select the documented snippets; they do not supply database expressions as snippet values.
Reading And Updating#
Use the same filter for a record page and its matching-record count. For a single-record change, db.first loads the required fields, a base-row lock protects a concurrent decision, and field assignment or remove followed by save persists the change. Keep optional joined projections out of that locking read. Use db.update for one bulk change with from, where and a non-empty set. Updating "supplier.id" changes the order's reference, not the Supplier record. No query or save creates a separate transaction.
Bulk updates support local fields and local reference identities such as supplier.id; their conditions cannot traverse joined fields such as supplier.name. If a decision requires related data, query and validate that data first, then change the intended records. Do not use a projection, ordering or page count to imply a bounded bulk update: bound the actual filter. An absent filter would target every record; state the intended business filter explicitly.
Read lock types are forUpdate, forNoKeyUpdate, forShare and forKeyShare. An optional qualifier is noWait or skipLocked, for example locking: { type: "forUpdate", qualifier: "skipLocked" }. noWait fails instead of waiting for a conflicting lock; skipLocked omits locked rows, so an empty result need not mean no matching business record exists. Locks last for the execution transaction, not just the helper function.
Complete Reporting Examples#
The companion query-projections.ais returns a page and matching-record count using the same reference filter. query-summary.ais computes totals over all matching records, rather than adding only one page. query-daily-totals.ais groups a named calendar interval into daily totals. Their Input, Output and resource requirements are listed in the example index.