Queries

Queries join multiple collections through lookup references, creating read-only datasets with selected properties from each.

URL Structure

Queries share the same namespace as collections:

/api/{query-name}

For external clients, replace /api with https://<region>.restapi.com/<api-name>. See Virtual Paths.

Fetching a Single Item

To fetch a single item from a query, append the ID to the URL:

/api/{query-name}/{id}

The ID refers to the primary (root) collection's item ID. This returns the query result for that specific item with all joined data included.

Creating Queries

Use the Query Builder in the Developer Portal:

1. Navigate to your API's schema
2. Create a new query
3. Select collections to join
4. Choose properties to include
5. Test with the Query Explorer

Property Aliasing

Properties must have unique aliases within the query. This is necessary because multiple collections may have properties with the same name.

customers.name → customerName
orders.name → orderName

Simple Query Example

Join orders with customer information:

Query: orders-with-customers

orders
  └── customer (lookup) → customers
        └── name, state

Selected properties:

• orders.orderDate → orderDate
• orders.total → total
• customers.name → customerName
• customers.state → customerState

Result:

{
  "data": [
    {
      "orderDate": "2024-01-15",
      "total": 299.99,
      "customerName": "Acme Corp",
      "customerState": "California"
    }
  ]
}

Property Behaviour

Each property in a query can have a behaviour that determines how it's processed:

Behaviour	Description
None	Include the property value as-is (default)
SUM	Total of numeric values
AVG	Average of numeric values
MIN	Minimum value
MAX	Maximum value
COUNT	Number of items
FILTER	Use for filtering only, not included in results

None (Default)

Properties with no behaviour are included directly in results. When joining collections, this creates a flat result set.

Aggregation Functions

Use SUM, AVG, MIN, MAX, or COUNT to aggregate values. When using aggregation, non-aggregated properties become grouping columns.

Filter Only

The FILTER behaviour lets you use a property in the query's filter without including it in the results. Useful for filtering on fields you don't need to display.

Aggregation Example

Query: Customer Order Totals

customers
  └── orders (via customer lookup)
        └── orderLines (via order lookup)
              └── lineTotal

Property configuration:

• customers.name → behaviour: None (grouping column)
• orderLines.lineTotal → behaviour: SUM

Result:

{
  "data": [
    { "customerName": "Acme Corp", "totalOrders": 15420.5 },
    { "customerName": "Tech Inc", "totalOrders": 8750.0 }
  ]
}

Sorting Query Results

Use the sortBy parameter:

?sortBy=totalOrders-    # Descending
?sortBy=customerName    # Ascending

Filtering Queries

Apply filters to query results:

?filter=totalOrders gt 10000

Filter Operators

Operator	Description	Example
eq	Equal to	status eq "active"
ne	Not equal to	status ne "deleted"
gt	Greater than	total gt 100
lt	Less than	quantity lt 10
ge	Greater than or equal	rating ge 4
le	Less than or equal	priority le 3
con	Contains	customerName con "Corp"
sw	Starts with	orderName sw "ORD-"
ew	Ends with	email ew "@company.com"

Case-insensitive: Append ~ to operators (e.g., eq~, con~)

Dynamic Expressions

Use exp() for dynamic values. The expression must be wrapped in single or double quotes.

tip

When using + in URLs, encode it as %2B (e.g., exp('now() %2B 7D')).

?filter=orderDate gt exp('now() - 30D')     # Orders from last 30 days
?filter=dueDate lt exp('now()')             # Overdue orders
?filter=total gt exp('100 + 50')            # Total > 150

Date/time tokens:

Token	Description	Example
now()	Current UTC date-time	now()
D	Days	now() + 7D
H	Hours	now() + 24H
M	Minutes	now() + 30M
S	Seconds	now() + 60S

Tokens are case-insensitive. All date/time values are handled in UTC.

Current user: userId() resolves to the id of the authenticated caller. As a request parameter it is a convenience; as the query's built-in filter it is what scopes a query per user, since _CREATOR does not (Access and Security).

Math functions: Abs, Ceiling, Floor, Round, Max, Min, Pow, Sqrt

Access and Security

A query is read-only — only GET is accepted — and is authorized through its own access rules like any other entity. Security policies apply to a query's rows as they do everywhere else.

_CREATOR does not scope a query

Unlike a collection or a view, a query is not owner-filtered. Naming _CREATOR in a query's access rule still lets the caller through the gate, but it adds no narrowing of its own — so a query whose GET rule names only _CREATOR serves every row it would otherwise return to any authenticated caller. Only the query's built-in filter and the security policies on the collections it joins narrow it.

When a query has to be per-user, put that in the configuration, not in the request: give the query a built-in filter on userId(), or a security policy on the collections it joins. A filter passed as a request parameter is chosen by the caller and can simply be left out, so it is never an access control.

Query vs View

Feature	Query	View
Joins collections	Yes	No
Aggregation	Yes	No
Write operations	No (read-only)	Configurable
Filter built-in	Optional	Optional
Owner filtering (_CREATOR)	No	Yes
Performance	Computed on request	Computed on request

Best Practices

• Alias clearly — Use descriptive names for aliased properties
• Limit joins — Complex queries with many joins may be slower
• Add filters — Reduce result size with built-in filters
• Use pagination — For large datasets, use pageNo and pageSize; for exporting a whole collection, prefer keyset pagination with lastKey