[Enh]: add aggregate_records DML tool to MCP server #3178
Description
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."
}
}
}
},
"required": ["entity", "function", "field"]
}Response Alias Convention
The aggregated value in the response is always aliased as {function}_{field}. For count with "*", the alias is count.
Examples
Q1: "How many products are there?"
{
"entity": "Product",
"function": "count",
"field": "*"
}SELECT COUNT(*) AS count
FROM Product;Example output:
| count |
|---|
| 77 |
Q2: "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 |
Q3: "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 |
Q4: "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 |
Q5: "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 |
Q6: "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 |
Q7: "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 |
Q8: "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 |
Q9: "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 |
Q10: "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 |
Metadata
Metadata
Assignees
Labels
No labels