GROUP BY and pivot tables for JSON rows: 11 functions, date buckets, top N, totals, messy numbers.
Maintain this server? Claim it
Call this server's tools from your browser. Nothing to install.
Requests go through mcptry because browsers can't call most MCP servers directly. Header values are sent to the server for that request only and are never stored or logged.
Tool list from our last check. Connect live to refresh it.
SQL GROUP BY and a spreadsheet pivot table for a list of JSON rows, in one call. Returns one output row per group with the aggregated columns, plus a summary: groups found, groups dropped by topN, values skipped because they were blank or not numeric (never guessed), and warnings such as a misspelled field name. Use it to turn scraped or API records into totals: orders and revenue per region, average price per brand, listings per city per month, top 10 products by revenue. Messy data is expected: "South" and "south " group together, and "$1,234.50" sums as 1234.5. Leave groupByFields empty to summarise all rows into one row. At most 500 rows per call.
The records to aggregate, up to 500. Each row is a JSON object; keys may differ between rows.
After sorting, keep only the first N groups. The totals row still covers every input row.
An output column to sort by: a group field, an aggregation alias such as "total_amount", or a pivot column. Omitted sorts by the group fields.
Turns this field's distinct values into columns, pivot-table style: group by "region" and pivot on "product" for one row per region with a column per product. Must not also be a group-by field. At most 50 distinct values.
What to compute per group, for example [{"function":"count","alias":"orders"}, {"field":"amount","function":"sum","alias":"total_amount"}]. Every function except count needs a field. alias is the output column name (defaults to function_field, or "count"). Omitted gives a plain row count per group. Up to 20.
One output row per distinct combination of these field values, like SQL GROUP BY, for example ["region"] or ["city", "category"]. Dot paths like "address.city" work. Empty or omitted aggregates every row into a single row.
normalized (default) ignores letter case and extra whitespace when grouping, so "South" and "south " are one group. exact requires identical values.
How pivot cell values are combined. Defaults to sum when pivotValueField is set; ignored (row count) without one.
asc (default) or desc. Use desc with topN for "top N by" questions.
On by default: "$1,234.50", "49 USD", "12%" and "(300)" count as numbers for sum, avg, min, max and median. Set false to accept only real numbers and plain numeric strings.
A date or timestamp field to group by time period, for example "orderedAt". Adds a group column named like "orderedAt_month". Unreadable dates land in an "(invalid date)" group.
The field whose values fill the pivot cells, for example "amount". Omitted fills each cell with a row count.
Appends a grand-total row labelled "(total)" and adds a _rowType column ("group" or "total").
Bucket size for dateBucketField: day (2026-08-19), week (2026-W34), month (2026-08, default), quarter (2026-Q3) or year.
This tool isn't marked read-only, so it may change something on the server or in an account you connected.
SQL GROUP BY and a spreadsheet pivot table for a list of JSON rows, in one call. Returns one output row per group with the aggregated columns, plus a summary: groups found, groups dropped by topN, values skipped because they were blank or not numeric (never guessed), and warnings such as a misspelled field name. Use it to turn scraped or API records into totals: orders and revenue per region, average price per brand, listings per city per month, top 10 products by revenue. Messy data is expected: "South" and "south " group together, and "$1,234.50" sums as 1234.5. Leave groupByFields empty to summarise all rows into one row. At most 500 rows per call.
| Argument | Type | Description |
|---|---|---|
| rows* | array | The records to aggregate, up to 500. Each row is a JSON object; keys may differ between rows. |
| topN | integer | After sorting, keep only the first N groups. The totals row still covers every input row. |
| sortBy | string | An output column to sort by: a group field, an aggregation alias such as "total_amount", or a pivot column. Omitted sorts by the group fields. |
| pivotField | string | Turns this field's distinct values into columns, pivot-table style: group by "region" and pivot on "product" for one row per region with a column per product. Must not also be a group-by field. At most 50 distinct values. |
| aggregations | array | What to compute per group, for example [{"function":"count","alias":"orders"}, {"field":"amount","function":"sum","alias":"total_amount"}]. Every function except count needs a field. alias is the output column name (defaults to function_field, or "count"). Omitted gives a plain row count per group. Up to 20. |
| groupByFields | array | One output row per distinct combination of these field values, like SQL GROUP BY, for example ["region"] or ["city", "category"]. Dot paths like "address.city" work. Empty or omitted aggregates every row into a single row. |
| groupMatching | string | normalized (default) ignores letter case and extra whitespace when grouping, so "South" and "south " are one group. exact requires identical values. |
| pivotFunction | string | How pivot cell values are combined. Defaults to sum when pivotValueField is set; ignored (row count) without one. |
| sortDirection | string | asc (default) or desc. Use desc with topN for "top N by" questions. |
| lenientNumbers | boolean | On by default: "$1,234.50", "49 USD", "12%" and "(300)" count as numbers for sum, avg, min, max and median. Set false to accept only real numbers and plain numeric strings. |
| dateBucketField | string | A date or timestamp field to group by time period, for example "orderedAt". Adds a group column named like "orderedAt_month". Unreadable dates land in an "(invalid date)" group. |
| pivotValueField | string | The field whose values fill the pivot cells, for example "amount". Omitted fills each cell with a row count. |
| includeTotalsRow | boolean | Appends a grand-total row labelled "(total)" and adds a _rowType column ("group" or "total"). |
| dateBucketGranularity | string | Bucket size for dateBucketField: day (2026-08-19), week (2026-W34), month (2026-08, default), quarter (2026-Q3) or year. |
{
"type": "object",
"required": [
"rows"
],
"properties": {
"rows": {
"type": "array",
"items": {
"type": "object"
},
"description": "The records to aggregate, up to 500. Each row is a JSON object; keys may differ between rows."
},
"topN": {
"type": "integer",
"minimum": 1,
"description": "After sorting, keep only the first N groups. The totals row still covers every input row."
},
"sortBy": {
"type": "string",
"description": "An output column to sort by: a group field, an aggregation alias such as \"total_amount\", or a pivot column. Omitted sorts by the group fields."
},
"pivotField": {
"type": "string",
"description": "Turns this field's distinct values into columns, pivot-table style: group by \"region\" and pivot on \"product\" for one row per region with a column per product. Must not also be a group-by field. At most 50 distinct values."
},
"aggregations": {
"type": "array",
"items": {
"type": "object",
"required": [
"function"
],
"properties": {
"alias": {
"type": "string",
"description": "Output column name."
},
"field": {
"type": "string",
"description": "The field to aggregate. Optional for count, which then counts rows."
},
"function": {
"enum": [
"count",
"countDistinct",
"sum",
"avg",
"min",
"max",
"median",
"first",
"last",
"list",
"listDistinct"
],
"type": "string"
}
}
},
"description": "What to compute per group, for example [{\"function\":\"count\",\"alias\":\"orders\"}, {\"field\":\"amount\",\"function\":\"sum\",\"alias\":\"total_amount\"}]. Every function except count needs a field. alias is the output column name (defaults to function_field, or \"count\"). Omitted gives a plain row count per group. Up to 20."
},
"groupByFields": {
"type": "array",
"items": {
"type": "string"
},
"description": "One output row per distinct combination of these field values, like SQL GROUP BY, for example [\"region\"] or [\"city\", \"category\"]. Dot paths like \"address.city\" work. Empty or omitted aggregates every row into a single row."
},
"groupMatching": {
"enum": [
"normalized",
"exact"
],
"type": "string",
"description": "normalized (default) ignores letter case and extra whitespace when grouping, so \"South\" and \"south \" are one group. exact requires identical values."
},
"pivotFunction": {
"enum": [
"count",
"countDistinct",
"sum",
"avg",
"min",
"max",
"median",
"first",
"last",
"list",
"listDistinct"
],
"type": "string",
"description": "How pivot cell values are combined. Defaults to sum when pivotValueField is set; ignored (row count) without one."
},
"sortDirection": {
"enum": [
"asc",
"desc"
],
"type": "string",
"description": "asc (default) or desc. Use desc with topN for \"top N by\" questions."
},
"lenientNumbers": {
"type": "boolean",
"description": "On by default: \"$1,234.50\", \"49 USD\", \"12%\" and \"(300)\" count as numbers for sum, avg, min, max and median. Set false to accept only real numbers and plain numeric strings."
},
"dateBucketField": {
"type": "string",
"description": "A date or timestamp field to group by time period, for example \"orderedAt\". Adds a group column named like \"orderedAt_month\". Unreadable dates land in an \"(invalid date)\" group."
},
"pivotValueField": {
"type": "string",
"description": "The field whose values fill the pivot cells, for example \"amount\". Omitted fills each cell with a row count."
},
"includeTotalsRow": {
"type": "boolean",
"description": "Appends a grand-total row labelled \"(total)\" and adds a _rowType column (\"group\" or \"total\")."
},
"dateBucketGranularity": {
"enum": [
"day",
"week",
"month",
"quarter",
"year"
],
"type": "string",
"description": "Bucket size for dateBucketField: day (2026-08-19), week (2026-W34), month (2026-08, default), quarter (2026-Q3) or year."
}
},
"additionalProperties": false
}Returns the 11 aggregation functions and what each one does, the date bucket formats, the labels used for blank, invalid-date and total rows, and the limits per call (rows, pivot columns, pivot cells, aggregations). Call this first if you are unsure what is available. Free, processes no data.
No arguments.
{
"type": "object",
"properties": {},
"additionalProperties": false
}