Dataset Aggregate & Pivot

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.

{
  "type": "object",
  "properties": {},
  "additionalProperties": false
}

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.

{
  "type": "object",
  "properties": {
    "rows": {
      "type": "array",
      "description": "The records to aggregate, up to 500. Each row is a JSON object; keys may differ between rows.",
      "items": {
        "type": "object"
      }
    },
    "groupByFields": {
      "type": "array",
      "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.",
      "items": {
        "type": "string"
      }
    },
    "aggregations": {
      "type": "array",
      "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.",
      "items": {
        "type": "object",
        "properties": {
          "field": {
            "type": "string",
            "description": "The field to aggregate. Optional for count, which then counts rows."
          },
          "function": {
            "type": "string",
            "enum": [
              "count",
              "countDistinct",
              "sum",
              "avg",
              "min",
              "max",
              "median",
              "first",
              "last",
              "list",
              "listDistinct"
            ]
          },
          "alias": {
            "type": "string",
            "description": "Output column name."
          }
        },
        "required": [
          "function"
        ]
      }
    },
    "groupMatching": {
      "type": "string",
      "enum": [
        "normalized",
        "exact"
      ],
      "description": "normalized (default) ignores letter case and extra whitespace when grouping, so \"South\" and \"south \" are one group. exact requires identical values."
    },
    "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."
    },
    "dateBucketGranularity": {
      "type": "string",
      "enum": [
        "day",
        "week",
        "month",
        "quarter",
        "year"
      ],
      "description": "Bucket size for dateBucketField: day (2026-08-19), week (2026-W34), month (2026-08, default), quarter (2026-Q3) or year."
    },
    "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."
    },
    "pivotValueField": {
      "type": "string",
      "description": "The field whose values fill the pivot cells, for example \"amount\". Omitted fills each cell with a row count."
    },
    "pivotFunction": {
      "type": "string",
      "enum": [
        "count",
        "countDistinct",
        "sum",
        "avg",
        "min",
        "max",
        "median",
        "first",
        "last",
        "list",
        "listDistinct"
      ],
      "description": "How pivot cell values are combined. Defaults to sum when pivotValueField is set; ignored (row count) without one."
    },
    "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."
    },
    "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."
    },
    "sortDirection": {
      "type": "string",
      "enum": [
        "asc",
        "desc"
      ],
      "description": "asc (default) or desc. Use desc with topN for \"top N by\" questions."
    },
    "topN": {
      "type": "integer",
      "minimum": 1,
      "description": "After sorting, keep only the first N groups. The totals row still covers every input row."
    },
    "includeTotalsRow": {
      "type": "boolean",
      "description": "Appends a grand-total row labelled \"(total)\" and adds a _rowType column (\"group\" or \"total\")."
    }
  },
  "required": [
    "rows"
  ],
  "additionalProperties": false
}