Query

Description

Run an aggregation query over records of a given record type. Use this endpoint to compute counts, sums, averages, and other aggregations — optionally grouped by one or more fields — without retrieving individual records.

The endpoint is gated behind the record-query-mode feature flag. Contact your account team if you don't have access.

Request

HTTP Method: POST

URL: https://<tenant-domain>/api/v1/records/query

Query Parameter	Description
test_mode	Optional Boolean true or false. When true, the query runs against test records only. Defaults to false (live records). Only effective for tenants with the change control feature.

The request body is a JSON object with the following fields:

Body Field	Required	Description
record_type_id	Yes	The numeric ID of the record type to query. The calling token must have read access to this record type.
aggregations	Yes	An array of aggregation functions to compute (see below).
filters	No	An array of filter conditions to narrow records before aggregation (see below). All filters are ANDed together.
group_by	No	An array of field references to group results by (see below). At most two entries. When omitted, you get a single row with the overall aggregation.
time_range	No	A time range to restrict which records are included (see below).
parent_ids	No	An array of parent record IDs. Restricts the query to first-degree children of those records.
order_by	No	Sort the output rows (see below).
limit	No	Cap on the number of rows returned, between 1 and 1000.

Field identification

Every field reference in aggregations, filters, and group_by accepts one of:

Key	Type	Description
field_id	Integer	The numeric ID of the record field. Stable across renames — recommended for automation.
field_name	String	The exact, case-sensitive name of the field within the record type. More readable, but breaks if the field is renamed.

Specify at most one of field_id or field_name per reference. COUNT aggregations may omit both (they count all rows).

Tip: Use field_id for stable automation that won't break if fields are renamed. Use field_name for ad-hoc queries and MCP integrations where readability matters.

To find field IDs and names, use the List record types API or navigate to any record page and select Manage record type from the dropdown menu.

Aggregations

Each entry in aggregations has:

Key	Required	Description
fn	Yes	The aggregation function: COUNT, SUM, AVG, MIN, MAX, or MEDIAN.
field_id or field_name	Depends	Required for all functions except COUNT. Identifies the field to aggregate.
alias	No	A name for this value in the response. Auto-generated if omitted (e.g., count, sum_<field_id>).

At most five aggregations per query.

Filters

Each entry in filters has:

Key	Required	Description
field_id or field_name	Yes	The field to filter on.
operator	Yes	EQUAL, IS_ANY_OF, IS_TRUE, or IS_FALSE.
value	Depends	The value to compare against. For IS_ANY_OF, provide an array. For IS_TRUE/IS_FALSE, omit.

At most 10 filters per query. IS_ANY_OF value arrays are capped at 10 entries.

Note: Query mode accepts a narrower set of operators than the list endpoint. Operators like GREATER_THAN, CONTAINS, IS_EMPTY, NOT_EQUAL, and IS_NONE_OF are not supported.

Group-by

Each entry in group_by has:

Key	Required	Description
field_id or field_name	Yes	The field to group by.
date_trunc	No	For timestamp fields only: hour, day, week, month, or year. Rolls values into calendar buckets.

At most two group-by entries per query.

Time range

The time_range object restricts which records are included based on when they were created. Use either a rolling preset or a custom range:

Key	Description
rolling_date_range	One of: TODAY, YESTERDAY, LAST_7_DAYS, LAST_31_DAYS, LAST_365_DAYS, ALL_TIME.
range_start	ISO 8601 timestamp for the start of a custom range (inclusive).
range_end	ISO 8601 timestamp for the end of a custom range (inclusive).

Use either rolling_date_range or a custom range (range_start and/or range_end), not both.

Order-by

The order_by object controls how result rows are sorted:

Key	Required	Description
column	Yes	The name of a column to sort by — either a group-by field's name or an aggregation alias.
direction	No	ASC or DESC. Defaults to ASC.

Example request

curl --proto '=https' --tlsv1.2 \
  -X POST \
  "https://<tenant-domain>/api/v1/records/query" \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer <<CREDENTIAL.tines_api_key>>' \
  -d '{
    "record_type_id": 123,
    "aggregations": [
      { "fn": "COUNT", "alias": "total" },
      { "fn": "AVG", "field_id": 8, "alias": "avg_score" }
    ],
    "filters": [
      { "field_id": 4, "operator": "EQUAL", "value": "blue" }
    ],
    "group_by": [
      { "field_id": 1, "date_trunc": "day" },
      { "field_name": "Color" }
    ],
    "time_range": { "rolling_date_range": "LAST_31_DAYS" },
    "order_by": { "column": "total", "direction": "DESC" },
    "limit": 50
  }'

Example request using field_name

curl --proto '=https' --tlsv1.2 \
  -X POST \
  "https://<tenant-domain>/api/v1/records/query" \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer <<CREDENTIAL.tines_api_key>>' \
  -d '{
    "record_type_id": 123,
    "aggregations": [
      { "fn": "COUNT", "alias": "total" },
      { "fn": "SUM", "field_name": "Amount", "alias": "total_amount" }
    ],
    "filters": [
      { "field_name": "Status", "operator": "EQUAL", "value": "open" }
    ],
    "group_by": [
      { "field_name": "Category" }
    ]
  }'

Rate limit

This endpoint has its own rate limit, separate from the general records rate limit. Please be aware that both request count and request duration are throttled.

Response

A successful request returns a JSON object containing the aggregation results.

Field description

Field	Description
rows	Array of result rows, each a flat object (see below).
meta	Object containing metadata about the query result.

Each object in rows is a flat map of column names to values:

• Group-by columns are keyed by the field's name (e.g., "Color", "Timestamp"). Timestamp values bucketed with date_trunc are ISO 8601 strings.
• Aggregation columns are keyed by the alias you specified (or auto-generated). COUNT values are integers. Numeric aggregates (SUM, AVG, MIN, MAX, MEDIAN) are returned as strings to preserve precision.

The meta object contains:

Field	Description
row_count	The number of rows returned.
truncated	true when no limit was specified and the result hit the 1000-row system cap. Always false when limit is set.
columns.group_by	Array of column names in rows that come from the group_by fields.
columns.aggregations	Array of column names in rows that come from aggregations.

Sample response — grouped query

{
  "rows": [
    {
      "Timestamp": "2026-05-01T00:00:00+00:00",
      "Color": "red",
      "total": 12,
      "avg_score": "70.0"
    },
    {
      "Timestamp": "2026-05-01T00:00:00+00:00",
      "Color": "blue",
      "total": 5,
      "avg_score": "64.0"
    }
  ],
  "meta": {
    "row_count": 2,
    "truncated": false,
    "columns": {
      "group_by": ["Timestamp", "Color"],
      "aggregations": ["total", "avg_score"]
    }
  }
}

Sample response — single-number query (no group_by)

{
  "rows": [
    {
      "total": 173,
      "avg_score": "62.4"
    }
  ],
  "meta": {
    "row_count": 1,
    "truncated": false,
    "columns": {
      "group_by": [],
      "aggregations": ["total", "avg_score"]
    }
  }
}

Errors

Errors are returned as a JSON object with an error field containing type and message, along with an appropriate HTTP status code:

Status	Meaning
400	Invalid query — malformed body, unknown field, disallowed operator, or validation failure.
404	Record type not found, or the calling token does not have access to the specified record type.
504	Query timeout — the query exceeded the 30-second time limit. Narrow your filters or reduce the time range.

Example error response

{
  "error": {
    "type": "bad_request",
    "message": "record_type_id is required and must be an integer"
  }
}

Limits

Limit	Value
Statement timeout	30 seconds
Maximum rows returned	1000 (indicated by meta.truncated when exceeded)
Maximum aggregations	5 per query
Maximum group-by fields	2 per query
Maximum filters	10 per query
Maximum IS_ANY_OF values	10 entries per filter
Supported filter operators	EQUAL, IS_ANY_OF, IS_TRUE, IS_FALSE
Rate limit	400 requests per minute (per IP and token)

No caching: every query runs fresh against the database. If you're calling this endpoint in a loop, add a delay between requests to stay within the 400 requests per minute rate limit.

Tips

• Use order_by + limit together to get "top N" results (e.g., top five categories by count).
• When meta.truncated is true, narrow your query with filters or a tighter time range to see all results.
• The MEDIAN function computes the 50th percentile, which is useful for skewed data where AVG can be misleading.
• field_name is resolved by exact, case-sensitive match within the record type. If you rename a field, queries using field_name will break — use field_id for rename-proof automation.