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.
`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`.
`negate`	boolean	When `true`, excludes rows that match. Default `false`.

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" },
    { "name": "brand_presence_percentage", "kind": "metric", "dtype": "float" }
  ],
  "rows": [
    ["2026-W14", 0.421],
    ["2026-W15", 0.478]
  ],
  "pagination": { "limit": 50000, "offset": 0 },
  "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`.
`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.
`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.

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 }
  ]
}

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`, 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.

​Overview

​When to use

​Authentication

​Request body

​DimensionFilter

​HavingFilter

​Comparison

​Response

​Examples

​Multi-metric weekly trend with a platform filter

​Threshold the result with HAVING

​Period-over-period comparison

​Exclude specific prompts

​Limits

​Errors

​Related