Structured Query API: Multi-Metric JSON Endpoint

Overview

The structured query endpoint accepts a JSON request body describing the dimensions, metrics, filters, and comparisons you want, and returns a columnar result. Use it when you need richer query shapes than the GET query endpoint supports — for example, post-aggregation thresholds, negated filters, or period-over-period comparison in a single round trip.

POST https://api.scrunchai.com/v2/query/{brand_id}

The structured endpoint and the GET endpoint share the same dimensions, metrics, and brand scoping. They differ in expressivity and request shape: the structured endpoint takes a JSON body and adds having, negate, and comparison.

When to use

Use the structured endpoint when you need to:

Filter on metric thresholds after aggregation (for example, only weeks with at least 10 responses).
Exclude a set of values rather than include them (negate: true).
Pull a current-period result and a prior-period result in one request, aligned for charting.
Submit complex filter combinations that are awkward to encode in URL parameters.

For straightforward dimension-and-metric pulls, the GET query endpoint remains the simpler choice.

Authentication

Authenticate with a Bearer API key. The key must include the Query scope and have access to the target brand. See Provision an API Key.

curl -X POST "https://api.scrunchai.com/v2/query/$SCRUNCH_BRAND_ID" \
  -H "Authorization: Bearer $SCRUNCH_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d @query.json

Request body

Field	Type	Required	Description
`fields`	`string[]`	Yes	Dimensions and metrics to return. 1–32 entries. See Fields reference.
`start_date`	`string` (YYYY-MM-DD)	No	Inclusive start. Defaults to 30 days ago. Required when `comparison` is set.
`end_date`	`string` (YYYY-MM-DD)	No	Inclusive end. Defaults to today. Required when `comparison` is set.
`filters`	`DimensionFilter[]`	No	Pre-aggregation `WHERE` filters on dimensions. Up to 25 entries.
`having`	`HavingFilter[]`	No	Post-aggregation `HAVING` filters on metrics. Up to 10 entries.
`comparison`	`Comparison`	No	Period-over-period comparison configuration.
`include_totals`	`boolean`	No	Compute a grand total over the full result set and return it in `totals`. When the returned rows are a proper subset of the total (a top-N breakdown), the response also carries a derived `"other"` remainder. Default `false`. Rejected in combination with `comparison`. See Totals and remainder.
`include_row_count`	`boolean`	No	Populate `pagination.total_rows` with the number of grouped rows the query matched before `limit`. Off by default because it re-runs the aggregation and can cost roughly as much as the row query itself. Ask for it only to render a “showing N of M” caption. Paging does not need it — `pagination.has_more` is exact and free. Default `false`.
`limit`	`integer`	No	Row cap. Default `50000`, maximum `90000`.
`offset`	`integer`	No	Row offset for pagination. Default `0`.

DimensionFilter

{ "field": "ai_platform", "values": ["ChatGPT"], "negate": false }

Field	Type	Description
`field`	string	A filterable dimension. `prompt` is not filterable.
`values`	array	One or more values to match. Up to 1000 entries. Values are coerced to the dimension’s type — booleans accept `true`/`false`, `1`/`0`, `"true"`/`"false"`, `"yes"`/`"no"`; integer dimensions accept numeric strings. Mismatched types return HTTP `422`. Pass the reserved token `"__none__"` on `tag` or `prompt_topic` to match prompts that carry no tag or no topic. See Filter for prompts with no tag or topic.
`negate`	boolean	When `true`, excludes rows that match. With `match: "all"`, this excludes prompts carrying every specified value; it does not exclude prompts carrying only some of them. Default `false`.
`match`	enum	How multiple `values` combine. `"any"` (default) matches prompts carrying at least one value (OR). `"all"` matches only prompts carrying every value (AND). Only supported on the array-valued prompt dimensions `tag` and `prompt_topic`; sending `"all"` on any other field returns HTTP `422`.

Match modes

By default, multi-value filters combine with OR — a filter on tag with ["A", "B"] returns prompts tagged A or B. This widens the result set as you add values. Set match: "all" on tag or prompt_topic to combine with AND instead — the filter then returns only prompts tagged A and B, narrowing the result set. Use this when a prompt can carry multiple tags or topics and you want to isolate the intersection (for example, product-level reporting that requires two tags at once). match only applies to tag and prompt_topic, which map each prompt to a set of values. Scalar dimensions like ai_platform or country reject match: "all" with HTTP 422 — a row has one platform, so AND across values would match nothing. Omit match (or send "any") on scalar dimensions.

Filtering by citation segment

citation_segment_id is filter-only: pass segment ids in values to scope citation metrics to specific segments. It cannot appear in fields — break results down by segment name with the citation_segment dimension instead. Segment ids are brand-scoped; an id that does not belong to the brand returns HTTP 400. Each segment filters by its configured match mode. Prefix segments (the default) match their entries and any subpath. Exact segments match only the listed pages, ignoring letter case, www., ports, query strings, and trailing slashes. Managed Agent Pages segments resolve to the pages their AXP site is serving right now, so the filter tracks deployments automatically. Pass multiple ids to combine sites — for example, a www site and a docs subdomain that the domain filter cannot separate. A managed segment whose site currently serves no pages matches zero rows.

{
  "fields": ["date_week", "citation_count", "citation_unique_responses"],
  "filters": [
    { "field": "citation_segment_id", "values": [123, 456] }
  ]
}

HavingFilter

{ "metric": "responses", "operator": "gte", "value": 10 }

Field	Type	Description
`metric`	string	A metric in `fields`. Referencing a metric not in `fields` is an error.
`operator`	enum	One of `gt`, `gte`, `lt`, `lte`, `eq`, `neq`.
`value`	number	The threshold. `inf` and `nan` are rejected.

Comparison

{ "mode": "prior_period" }

Field	Type	Description
`mode`	enum	`prior_period` shifts back by the window length. `prior_year` shifts back by one calendar year (Feb 29 falls back to Feb 28 in non-leap years).

When comparison is set, start_date and end_date are required and the response includes a synthetic period dimension with values current and prior. Each period is capped at limit / 2 rows so the merged result stays within limit. Prior-period dates are aligned to current-period bucket labels so the two series overlay on a chart.

Response

The response is columnar.

{
  "columns": [
    { "name": "date_week", "kind": "dimension", "dtype": "date", "rollup": null },
    { "name": "brand_presence_percentage", "kind": "metric", "dtype": "float", "rollup": "ratio" }
  ],
  "rows": [
    ["2026-W14", 0.421],
    ["2026-W15", 0.478]
  ],
  "pagination": { "limit": 50000, "offset": 0, "has_more": false, "total_rows": null },
  "totals": null,
  "compare_status": null
}

Field	Type	Description
`columns`	array	Column metadata in the same order as `rows`. `kind` is `dimension` or `metric`. `dtype` is one of `string`, `int`, `float`, `bool`, `date`.
`columns[].rollup`	`"count"` \| `"ratio"` \| `"average"` \| `"none"` \| `null`	On metric columns, how per-row values collapse into one window-level number. `null` on dimensions. See Rolling up metrics client-side.
`rows`	array	Each row has one cell per column.
`pagination.limit`	integer	The row cap clients should use for end-of-page detection. Echoes `limit` for non-comparison queries and for successful comparison queries (each period is capped at `limit / 2` rows, and the merged result never exceeds `limit`). When `compare_status` is `"current_only"`, returns `limit / 2` — the cap that was actually applied to the surviving current series.
`pagination.offset`	integer	Echoes the requested offset.
`pagination.has_more`	boolean	`true` when the query matched more rows than `limit` returned. Determined exactly (the server asks ClickHouse for `limit + 1` rows and drops the extra one), so `len(rows) == limit` is not a reliable substitute. Always present.
`pagination.total_rows`	integer \| null	Total grouped rows matched, ignoring `limit` and `offset`. Populated when the request set `include_row_count: true`; `null` otherwise.
`totals`	object \| null	The window’s grand total. Populated when the request set `include_totals: true`; `null` otherwise. See Totals and remainder.
`compare_status`	`"current_only"` \| `null`	Set to `"current_only"` when `comparison` was requested but the prior-period query failed; the response then contains only the current series. `null` otherwise.

Date dimensions are formatted as labels: date → YYYY-MM-DD, date_week → ISO YYYY-Www (uses ISO week-numbering year, so dates near the year boundary group with their ISO week — for example, 2024-12-30 is 2025-W01), date_month → YYYY-MM, date_quarter → YYYY-Q#, date_year → YYYY.

Totals and remainder

Set include_totals: true to receive a totals object alongside rows. The server computes the grand total in SQL over the full result set, so it does not drift when limit truncates the rows or when a top-N request excludes long-tail slices.

"totals": {
  "values": [null, 0.312],
  "remainder": [null, 0.284]
}

Field	Type	Description
`values`	array	One cell per column, positionally aligned with `columns`. Dimension positions are `null`; metric positions carry the window-level aggregate.
`remainder`	array \| null	The `"other"` bucket — the total minus what the returned rows account for, derived from the parts. `null` when the returned rows overlap and no honest leftover exists (for example, tag-sliced rows that share responses). Populated only alongside a limited breakdown result.

Use totals.values to render an accurate percentage-of-total on a page that shows only a top-N slice, and totals.remainder to display a real “Other” slice in a donut or pie without querying the tail separately. include_totals is rejected in combination with comparison: the response interleaves two periods, so a single grand total describes neither. Request the two periods separately.

Rolling up metrics client-side

Every metric column carries a rollup mode that tells you how to collapse its per-row values into one window-level number — the arithmetic the server would perform if you re-queried without the axis you are flattening. Use it when you fold a time-bucketed or breakdown-sliced response down to a single value in the client.

Mode	Meaning
`count`	Additive. Sum the values across rows.
`ratio`	A poolable rate. Recover the window value as `sum(numerator) / sum(denominator)` — request `metric_shape: "envelope"` to get the numerator and denominator per row.
`average`	A poolable average. Recover the window value as `sum(value * denominator) / sum(denominator)` — request `metric_shape: "envelope"` for the denominators.
`none`	Not poolable. No arithmetic over the returned rows recovers the window value. Re-query without the collapsing dimension to get the true total.

Averaging or summing without following the declared rollup gives measurably wrong window-level numbers on rate and average metrics — an unweighted mean of per-day rates weights a 1-response day the same as a 4,000-response day. When you only need the window aggregate and do not care about the per-row values, set include_totals: true instead and read totals.values.

Examples

Multi-metric weekly trend with a platform filter

curl -X POST "https://api.scrunchai.com/v2/query/$SCRUNCH_BRAND_ID" \
  -H "Authorization: Bearer $SCRUNCH_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "start_date": "2026-04-01",
    "end_date":   "2026-04-30",
    "fields": ["date_week", "brand_presence_percentage", "brand_unique_prompts"],
    "filters": [
      { "field": "ai_platform", "values": ["ChatGPT"] }
    ]
  }'

Threshold the result with HAVING

Return only weeks that received at least 10 responses.

curl -X POST "https://api.scrunchai.com/v2/query/$SCRUNCH_BRAND_ID" \
  -H "Authorization: Bearer $SCRUNCH_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "start_date": "2026-04-01",
    "end_date":   "2026-04-30",
    "fields": ["date_week", "responses", "brand_presence_percentage"],
    "having": [
      { "metric": "responses", "operator": "gte", "value": 10 }
    ]
  }'

Period-over-period comparison

curl -X POST "https://api.scrunchai.com/v2/query/$SCRUNCH_BRAND_ID" \
  -H "Authorization: Bearer $SCRUNCH_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "start_date": "2026-04-01",
    "end_date":   "2026-04-30",
    "fields": ["date_week", "brand_presence_percentage"],
    "comparison": { "mode": "prior_period" }
  }'

The response prepends a period column. Prior dates are mapped to current-period labels so the series can be plotted on a single x-axis.

Exclude specific prompts

{
  "fields": ["prompt_topic", "responses"],
  "filters": [
    { "field": "prompt_topic", "values": ["Pricing", "Support"], "negate": true }
  ]
}

Match all tags (AND) instead of any (OR)

Return only prompts carrying both tags. Without match: "all", this filter returns prompts carrying either tag.

{
  "fields": ["brand_presence_percentage"],
  "filters": [
    { "field": "tag", "values": ["Enterprise", "Security"], "match": "all" }
  ]
}

Filter for prompts with no tag or topic

Pass the reserved token "__none__" in values on a tag or prompt_topic filter to match prompts that carry no tag or no topic. Use it to isolate the untagged tail of your prompt set — for example, to audit coverage gaps or to compute presence on the unclassified slice. Only tag and prompt_topic accept "__none__". Sending it on any other dimension returns HTTP 422. Return only untagged prompts:

{
  "fields": ["brand_presence_percentage"],
  "filters": [
    { "field": "tag", "values": ["__none__"] }
  ]
}

Combine "__none__" with real values in the same filter — the filter matches either the untagged prompts or the named tags:

{
  "fields": ["tag", "brand_presence_percentage"],
  "filters": [
    { "field": "tag", "values": ["__none__", "Core"] }
  ]
}

Set negate: true to invert the whole filter — for example, prompts that carry at least one tag:

{
  "fields": ["brand_presence_percentage"],
  "filters": [
    { "field": "tag", "values": ["__none__"], "negate": true }
  ]
}

"__none__" cannot combine with match: "all": the request returns HTTP 422. A prompt with no tag has an empty tag set, so requiring it to contain “every” value including "__none__" is contradictory. Keep match as the default "any" (OR) when the filter uses "__none__". A tag literally named __none__ becomes unreachable by equality — the token always resolves to the absence match. Rename such a tag if you need to filter for it directly.

Top slices with a grand total and “other” remainder

Request the top platforms for a metric alongside the full-window total. totals.values gives the accurate window-level number, and totals.remainder accounts for everything past the returned rows.

curl -X POST "https://api.scrunchai.com/v2/query/$SCRUNCH_BRAND_ID" \
  -H "Authorization: Bearer $SCRUNCH_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "start_date": "2026-04-01",
    "end_date":   "2026-04-30",
    "fields": ["ai_platform", "brand_presence_percentage"],
    "limit": 5,
    "include_totals": true
  }'

Limits

Limit	Value
Fields per request	32
Filters per request	25
HAVING clauses per request	10
Values per filter	1000
Rows per response (`limit`)	90000
Default rows per response	50000
Server-side execution time	30 seconds

Requests that exceed these limits are rejected with HTTP 422.

Errors

Status	Cause
`401`	Missing or invalid API key.
`403`	API key lacks the `query` scope or access to the brand.
`400`	The field combination cannot be built into a single query — for example, citation metrics combined with raw-path-only fields, or a share-of-voice metric with an unsupported breakdown. Also returned for an unknown `citation_segment_id` filter value.
`404`	Brand not found.
`422`	Request body fails validation (for example, a non-filterable dimension in `filters`, a HAVING metric not in `fields`, `start_date` after `end_date`, a value that cannot be coerced to the dimension’s type, a HAVING `value` of `inf` or `nan`, `match: "all"` on a dimension other than `tag` or `prompt_topic`, `"__none__"` on a dimension other than `tag` or `prompt_topic`, `"__none__"` combined with `match: "all"`, `include_totals: true` combined with `comparison`, or a request that exceeds the size limits).
`500`	Server error. When triggered by the prior-period query in a comparison request, the response succeeds with `compare_status: "current_only"` instead.

Query API overview

Field reference shared with the GET endpoint.

Query API quickstart

First request walkthrough.

Query API

Responses API

Agent Traffic API

AI Referrals API

Site Audit API

Signals API

Sitemap API

Collection API

AXP Render API

Optimize & Deploy API

Webhooks

Configuration API

Structured Query API: Multi-Metric JSON Endpoint

Overview

When to use

Authentication

Request body

DimensionFilter

Match modes

Filtering by citation segment

HavingFilter

Comparison

Response

Totals and remainder

Rolling up metrics client-side

Examples

Multi-metric weekly trend with a platform filter

Threshold the result with HAVING

Period-over-period comparison

Exclude specific prompts

Match all tags (AND) instead of any (OR)

Filter for prompts with no tag or topic

Top slices with a grand total and “other” remainder

Limits

Errors

Query API overview

Query API quickstart

​Overview

​When to use

​Authentication

​Request body

​DimensionFilter

​Match modes

​Filtering by citation segment

​HavingFilter

​Comparison

​Response

​Totals and remainder

​Rolling up metrics client-side

​Examples

​Multi-metric weekly trend with a platform filter

​Threshold the result with HAVING

​Period-over-period comparison

​Exclude specific prompts

​Match all tags (AND) instead of any (OR)

​Filter for prompts with no tag or topic

​Top slices with a grand total and “other” remainder

​Limits

​Errors

​Related

Query API overview

Query API quickstart

Overview

When to use

Authentication

Request body

DimensionFilter

Match modes

Filtering by citation segment

HavingFilter

Comparison

Response

Totals and remainder

Rolling up metrics client-side

Examples

Multi-metric weekly trend with a platform filter

Threshold the result with HAVING

Period-over-period comparison

Exclude specific prompts

Match all tags (AND) instead of any (OR)

Filter for prompts with no tag or topic

Top slices with a grand total and “other” remainder

Limits

Errors

Related