Query datasets
const options = {method: 'POST'};
fetch('https://api.dcycle.io/v1/datasets/{key}/query', options)
.then(res => res.json())
.then(res => console.log(res))
.catch(err => console.error(err));import requests
url = "https://api.dcycle.io/v1/datasets/{key}/query"
response = requests.post(url)
print(response.text)curl --request POST \
--url https://api.dcycle.io/v1/datasets/{key}/queryQuery datasets
Aggregate dataset records, evaluate formulas and recalculate subtotals.
POST
/
v1
/
datasets
/
{key}
/
query
Query datasets
const options = {method: 'POST'};
fetch('https://api.dcycle.io/v1/datasets/{key}/query', options)
.then(res => res.json())
.then(res => console.log(res))
.catch(err => console.error(err));import requests
url = "https://api.dcycle.io/v1/datasets/{key}/query"
response = requests.post(url)
print(response.text)curl --request POST \
--url https://api.dcycle.io/v1/datasets/{key}/queryQueries use the authenticated organization’s accepted family scope. Use a catalog dataset key or
The numerator and denominator must refer to the same valid observations. If
Each subtotal is recalculated from the source observations with the query’s filters, organization scope and dimension row restrictions. It is not a sum of the displayed leaf cells. The leaf result and subtotals run in one SQL statement and share a database snapshot and statement timeout.
elastic:<dataset UUID> for a custom dataset. The dataset schema describes available dimensions, metrics and row-level formula operands.
Formula evaluation
A formula withmode: "row" evaluates each record before applying its agg (sum by default; avg for a mean). A formula with mode: "aggregate" evaluates the grouped metric values. The distinction matters: SUM(value * weight) / SUM(weight) is a weighted mean; the mean of group means is generally a different result. Division by zero returns null.
This example assumes numeric fields value and weight, and dimension region in the dataset schema:
{
"dimensions": ["region"],
"metrics": [],
"formula_metrics": [
{
"key": "weighted_sum",
"label": "Weighted sum",
"mode": "row",
"agg": "sum",
"expr": {"op": "*", "args": [{"field": "value"}, {"field": "weight"}]}
},
{
"key": "weights",
"label": "Weights",
"mode": "row",
"agg": "sum",
"expr": {"field": "weight"}
},
{
"key": "weighted_mean",
"label": "Weighted mean",
"mode": "aggregate",
"expr": {"op": "/", "args": [{"field": "weighted_sum"}, {"field": "weights"}]}
}
],
"subtotal_dimensions": [[]],
"limit": 100
}
value can be missing, exclude that record’s weight as well, using an appropriate row formula or input filtering. avg ignores null observations; null values are not automatically zero.
Recalculated subtotals
subtotal_dimensions is optional and defaults to []. Each entry is a distinct subset of the query’s selected dimensions. An empty subset requests the grand total. For dimensions: ["region", "product"], [[], ["region"]] requests both a grand total and regional totals. Requests support at most 16 subsets; raw sample queries reject this option.
The response includes subtotals, alongside the existing columns, rows and truncated fields:
{
"subtotals": [
{
"dimensions": [],
"rows": [{"weighted_sum": "1400", "weights": "100", "weighted_mean": "14"}],
"truncated": false
}
]
}
limit applies independently to each grouping, with its own truncated flag. A complete grand total remains valid when the leaf table is truncated. A truncated subtotal grouping can omit groups; clients must not treat an absent group as zero. Request only the groupings needed by the visualization, as each adds aggregation work.
Temporal and display semantics
For datasets with prorated intervals, a subtotal that removes the period dimension retains the selected time buckets. Amounts are allocated to the selected overlap; a mean counts each source observation once. Aggregate formulas are evaluated after period aggregation. For external intensity metrics, collapsing an existing period dimension retains its grain and period filters. Monthly and annual denominator records are not added together. Missing selected-period coverage produces a null denominator for division. Existing unsupported combinations of external intensity or conditional attributes with prorated period queries remain unsupported. Clients should use returned subtotals for means, ratios and chart totals. Comparison windows must remain separate, since windows may overlap. Percentage shares require a complete additive partition; summing means or calculating shares from a truncated sample can produce misleading percentages. Subtotals do not perform unit conversion: incompatible units still require a separating dimension or a separate conversion policy.Was this page helpful?