[Enh]: add `aggregate_records` DML tool to MCP server

[JerryNixon]

What?

Allow models to answer: "How many products are there?" and "What is our most expensive product?"

Why?

These are among the most common information discovery questions, a primary model use case.

How?

Introduce a new tool: aggregate_records that reuses native GraphQL aggregation capabilities in DAB.

Schema

{
  "type": "object",
  "properties": {
    "entity": {
      "type": "string",
      "description": "Entity name with READ permission.",
      "required": true
    },
    "function": {
      "type": "string",
      "enum": ["count", "avg", "sum", "min", "max"],
      "description": "Aggregation function to apply.",
      "required": true
    },
    "field": {
      "type": "string",
      "description": "Field to aggregate. Use '*' for count.",
      "required": true
    },
    "distinct": {
      "type": "boolean",
      "description": "Apply DISTINCT before aggregating.",
      "default": false
    },
    "filter": {
      "type": "string",
      "description": "OData filter applied before aggregating (WHERE). Example: 'unitPrice lt 10'",
      "default": ""
    },
    "groupby": {
      "type": "array",
      "items": { "type": "string" },
      "description": "Fields to group by, e.g., ['category', 'region']. Grouped field values are included in the response.",
      "default": []
    },
    "orderby": {
      "type": "string",
      "enum": ["asc", "desc"],
      "description": "Sort aggregated results by the computed value. Only applies with groupby.",
      "default": "desc"
    },
    "having": {
      "type": "object",
      "description": "Filter applied after aggregating on the result (HAVING). Operators are AND-ed together.",
      "properties": {
        "eq":  { "type": "number", "description": "Aggregated value equals." },
        "neq": { "type": "number", "description": "Aggregated value not equals." },
        "gt":  { "type": "number", "description": "Aggregated value greater than." },
        "gte": { "type": "number", "description": "Aggregated value greater than or equal." },
        "lt":  { "type": "number", "description": "Aggregated value less than." },
        "lte": { "type": "number", "description": "Aggregated value less than or equal." },
        "in":  {
          "type": "array",
          "items": { "type": "number" },
          "description": "Aggregated value is in the given list."
        }
      }
    },
    "first": {
      "type": "integer",
      "description": "Maximum number of results to return. Used for pagination. Only applies with groupby.",
      "minimum": 1,
      "default": null
    },
    "after": {
      "type": "string",
      "description": "Cursor for pagination. Returns results after this cursor. Only applies with groupby and first.",
      "default": null
    }
  },
  "required": ["entity", "function", "field"]
}

• first: Only applies when groupby is used (since non-grouped aggregations return a single value)
• after: Requires both groupby and first to be set
• Cursor format: The cursor value should be an opaque string returned by DAB in the response (typically base64-encoded)

Response Alias Convention

The aggregated value in the response is always aliased as {function}_{field}. For count with "*", the alias is count.

Examples

Example 1: "How many products are there?"

{
  "entity": "Product",
  "function": "count",
  "field": "*"
}

SELECT COUNT(*) AS count
FROM Product;

Example output:

count
77

---

Example 2: "What is the average price of products under $10?"

{
  "entity": "Product",
  "function": "avg",
  "field": "unitPrice",
  "filter": "unitPrice lt 10"
}

SELECT AVG(unitPrice) AS avg_unitPrice
FROM Product
WHERE unitPrice < 10;

Example output:

avg_unitPrice
6.74

---

Example 3: "Which categories have more than 20 products?"

{
  "entity": "Product",
  "function": "count",
  "field": "*",
  "groupby": ["categoryName"],
  "having": {
    "gt": 20
  }
}

SELECT categoryName, COUNT(*) AS count
FROM Product
GROUP BY categoryName
HAVING COUNT(*) > 20;

Example output:

categoryName	count
Beverages	24
Condiments	22

---

Example 4: "For discontinued products, which categories have a total revenue between $500 and $10,000?"

{
  "entity": "Product",
  "function": "sum",
  "field": "unitPrice",
  "filter": "discontinued eq true",
  "groupby": ["categoryName"],
  "having": {
    "gte": 500,
    "lte": 10000
  }
}

SELECT categoryName, SUM(unitPrice) AS sum_unitPrice
FROM Product
WHERE discontinued = 1
GROUP BY categoryName
HAVING SUM(unitPrice) >= 500
   AND SUM(unitPrice) <= 10000;

Example output:

categoryName	sum_unitPrice
Seafood	1834.50
Produce	742.00

---

Example 5: "How many distinct suppliers do we have?"

{
  "entity": "Product",
  "function": "count",
  "field": "supplierId",
  "distinct": true
}

SELECT COUNT(DISTINCT supplierId) AS count_supplierId
FROM Product;

Example output:

count_supplierId
29

---

Example 6: "Which categories have exactly 5 or 10 products?"

{
  "entity": "Product",
  "function": "count",
  "field": "*",
  "groupby": ["categoryName"],
  "having": {
    "in": [5, 10]
  }
}

SELECT categoryName, COUNT(*) AS count
FROM Product
GROUP BY categoryName
HAVING COUNT(*) IN (5, 10);

Example output:

categoryName	count
Grains	5
Produce	5

---

Example 7: "What is the average distinct unit price per category, for categories averaging over $25?"

{
  "entity": "Product",
  "function": "avg",
  "field": "unitPrice",
  "distinct": true,
  "groupby": ["categoryName"],
  "having": {
    "gt": 25
  }
}

SELECT categoryName, AVG(DISTINCT unitPrice) AS avg_unitPrice
FROM Product
GROUP BY categoryName
HAVING AVG(DISTINCT unitPrice) > 25;

Example output:

categoryName	avg_unitPrice
Meat/Poultry	54.01
Beverages	32.50

---

Example 8: "Which categories have the most products?"

{
  "entity": "Product",
  "function": "count",
  "field": "*",
  "groupby": ["categoryName"],
  "orderby": "desc"
}

SELECT categoryName, COUNT(*) AS count
FROM Product
GROUP BY categoryName
ORDER BY COUNT(*) DESC;

Example output:

categoryName	count
Confections	13
Beverages	12
Condiments	12
Seafood	12

---

Example 9: "What are the cheapest categories by average price?"

{
  "entity": "Product",
  "function": "avg",
  "field": "unitPrice",
  "groupby": ["categoryName"],
  "orderby": "asc"
}

SELECT categoryName, AVG(unitPrice) AS avg_unitPrice
FROM Product
GROUP BY categoryName
ORDER BY AVG(unitPrice) ASC;

Example output:

categoryName	avg_unitPrice
Grains/Cereals	20.25
Condiments	23.06
Produce	32.37

---

Example 10: "For categories with over $500 revenue from discontinued products, which has the highest total?"

{
  "entity": "Product",
  "function": "sum",
  "field": "unitPrice",
  "filter": "discontinued eq true",
  "groupby": ["categoryName"],
  "having": {
    "gt": 500
  },
  "orderby": "desc"
}

SELECT categoryName, SUM(unitPrice) AS sum_unitPrice
FROM Product
WHERE discontinued = 1
GROUP BY categoryName
HAVING SUM(unitPrice) > 500
ORDER BY SUM(unitPrice) DESC;

Example output:

categoryName	sum_unitPrice
Seafood	1834.50
Meat/Poultry	1062.50
Produce	742.00

Example 11: "Show me the first 5 categories by product count"

{
  "entity": "Product",
  "function": "count",
  "field": "*",
  "groupby": ["categoryName"],
  "orderby": "desc",
  "first": 5
}

SELECT categoryName, COUNT(*) AS count
FROM Product
GROUP BY categoryName
ORDER BY COUNT(*) DESC
LIMIT 5;

Example output:

categoryName	count
Confections	13
Beverages	12
Condiments	12
Seafood	12
Dairy	10

Response includes cursor:

{
  "items": [
    { "categoryName": "Confections", "count": 13 },
    { "categoryName": "Beverages", "count": 12 },
    { "categoryName": "Condiments", "count": 12 },
    { "categoryName": "Seafood", "count": 12 },
    { "categoryName": "Dairy", "count": 10 }
  ],
  "endCursor": "eyJjYXRlZ29yeU5hbWUiOiJEYWlyeSJ9",
  "hasNextPage": true
}

Example 12: "Show me the next 5 categories"

{
  "entity": "Product",
  "function": "count",
  "field": "*",
  "groupby": ["categoryName"],
  "orderby": "desc",
  "first": 5,
  "after": "eyJjYXRlZ29yeU5hbWUiOiJEYWlyeSJ9"
}

SELECT categoryName, COUNT(*) AS count
FROM Product
GROUP BY categoryName
ORDER BY COUNT(*) DESC
LIMIT 5 OFFSET 5;

Example output:

categoryName	count
Grains/Cereals	7
Meat/Poultry	6
Produce	5

Response includes cursor:

{
  "items": [
    { "categoryName": "Grains/Cereals", "count": 7 },
    { "categoryName": "Meat/Poultry", "count": 6 },
    { "categoryName": "Produce", "count": 5 }
  ],
  "endCursor": "eyJjYXRlZ29yeU5hbWUiOiJQcm9kdWNlIn0=",
  "hasNextPage": false
}

Example 13: "Show me the top 3 most expensive categories by average price"

{
  "entity": "Product",
  "function": "avg",
  "field": "unitPrice",
  "groupby": ["categoryName"],
  "orderby": "desc",
  "first": 3
}

SELECT categoryName, AVG(unitPrice) AS avg_unitPrice
FROM Product
GROUP BY categoryName
ORDER BY AVG(unitPrice) DESC
LIMIT 3;

Example output:

categoryName	avg_unitPrice
Meat/Poultry	54.01
Seafood	37.08
Beverages	37.98