salesforce_aggregate_query

Instructions

Implementation Reference

• src/tools/aggregateQuery.ts:179-282 (handler)

  Executes the aggregate SOQL query: validates inputs (GROUP BY fields, WHERE/HAVING/ORDER BY), constructs the full SOQL with GROUP BY and aggregates, queries Salesforce, formats grouped results, handles common errors with helpful messages.

  export async function handleAggregateQuery(conn: any, args: AggregateQueryArgs) {
    const { objectName, selectFields, groupByFields, whereClause, havingClause, orderBy, limit } = args;
  
    try {
      // Validate GROUP BY contains all non-aggregate fields
      const groupByValidation = validateGroupByFields(selectFields, groupByFields);
      if (!groupByValidation.isValid) {
        return {
          content: [{
            type: "text",
            text: `Error: The following non-aggregate fields must be included in GROUP BY clause: ${groupByValidation.missingFields!.join(', ')}\n\n` +
                  `All fields in SELECT that are not aggregate functions (COUNT, SUM, AVG, etc.) must be included in GROUP BY.`
          }],
          isError: true,
        };
      }
  
      // Validate WHERE clause doesn't contain aggregates
      const whereValidation = validateWhereClause(whereClause);
      if (!whereValidation.isValid) {
        return {
          content: [{
            type: "text",
            text: whereValidation.error!
          }],
          isError: true,
        };
      }
  
      // Validate ORDER BY fields
      const orderByValidation = validateOrderBy(orderBy, groupByFields, selectFields);
      if (!orderByValidation.isValid) {
        return {
          content: [{
            type: "text",
            text: orderByValidation.error!
          }],
          isError: true,
        };
      }
  
      // Construct SOQL query
      let soql = `SELECT ${selectFields.join(', ')} FROM ${objectName}`;
      if (whereClause) soql += ` WHERE ${whereClause}`;
      soql += ` GROUP BY ${groupByFields.join(', ')}`;
      if (havingClause) soql += ` HAVING ${havingClause}`;
      if (orderBy) soql += ` ORDER BY ${orderBy}`;
      if (limit) soql += ` LIMIT ${limit}`;
  
      const result = await conn.query(soql);
      
      // Format the output
      const formattedRecords = result.records.map((record: any, index: number) => {
        const recordStr = selectFields.map(field => {
          const baseField = extractBaseField(field);
          const fieldParts = field.trim().split(/\s+/);
          const displayName = fieldParts.length > 1 ? fieldParts[fieldParts.length - 1] : baseField;
          
          // Handle nested fields in results
          if (baseField.includes('.')) {
            const parts = baseField.split('.');
            let value = record;
            for (const part of parts) {
              value = value?.[part];
            }
            return `    ${displayName}: ${value !== null && value !== undefined ? value : 'null'}`;
          }
          
          const value = record[baseField] || record[displayName];
          return `    ${displayName}: ${value !== null && value !== undefined ? value : 'null'}`;
        }).join('\n');
        return `Group ${index + 1}:\n${recordStr}`;
      }).join('\n\n');
  
      return {
        content: [{
          type: "text",
          text: `Aggregate query returned ${result.records.length} grouped results:\n\n${formattedRecords}`
        }],
        isError: false,
      };
    } catch (error) {
      const errorMessage = error instanceof Error ? error.message : String(error);
      
      // Provide more helpful error messages for common issues
      let enhancedError = errorMessage;
      if (errorMessage.includes('MALFORMED_QUERY')) {
        if (errorMessage.includes('GROUP BY')) {
          enhancedError = `Query error: ${errorMessage}\n\nCommon issues:\n` +
            `1. Ensure all non-aggregate fields in SELECT are in GROUP BY\n` +
            `2. Check that date functions match exactly between SELECT and GROUP BY\n` +
            `3. Verify field names and relationships are correct`;
        }
      }
  
      return {
        content: [{
          type: "text",
          text: `Error executing aggregate query: ${enhancedError}`
        }],
        isError: true,
      };
    }
  } 

• src/tools/aggregateQuery.ts:3-88 (schema)

  Defines the tool specification including name, detailed description with examples, and input schema for parameters like objectName, selectFields (with aggregates), groupByFields, whereClause, havingClause, etc.

  export const AGGREGATE_QUERY: Tool = {
    name: "salesforce_aggregate_query",
    description: `Execute SOQL queries with GROUP BY, aggregate functions, and statistical analysis. Use this tool for queries that summarize and group data rather than returning individual records.
  
  NOTE: For regular queries without GROUP BY or aggregates, use salesforce_query_records instead.
  
  This tool handles:
  1. GROUP BY queries (single/multiple fields, related objects, date functions)
  2. Aggregate functions: COUNT(), COUNT_DISTINCT(), SUM(), AVG(), MIN(), MAX()
  3. HAVING clauses for filtering grouped results
  4. Date/time grouping: CALENDAR_YEAR(), CALENDAR_MONTH(), CALENDAR_QUARTER(), FISCAL_YEAR(), FISCAL_QUARTER()
  
  Examples:
  1. Count opportunities by stage:
     - objectName: "Opportunity"
     - selectFields: ["StageName", "COUNT(Id) OpportunityCount"]
     - groupByFields: ["StageName"]
  
  2. Analyze cases by priority and status:
     - objectName: "Case"
     - selectFields: ["Priority", "Status", "COUNT(Id) CaseCount", "AVG(Days_Open__c) AvgDaysOpen"]
     - groupByFields: ["Priority", "Status"]
  
  3. Count contacts by account industry:
     - objectName: "Contact"
     - selectFields: ["Account.Industry", "COUNT(Id) ContactCount"]
     - groupByFields: ["Account.Industry"]
  
  4. Quarterly opportunity analysis:
     - objectName: "Opportunity"
     - selectFields: ["CALENDAR_YEAR(CloseDate) Year", "CALENDAR_QUARTER(CloseDate) Quarter", "SUM(Amount) Revenue"]
     - groupByFields: ["CALENDAR_YEAR(CloseDate)", "CALENDAR_QUARTER(CloseDate)"]
  
  5. Find accounts with more than 10 opportunities:
     - objectName: "Opportunity"
     - selectFields: ["Account.Name", "COUNT(Id) OpportunityCount"]
     - groupByFields: ["Account.Name"]
     - havingClause: "COUNT(Id) > 10"
  
  Important Rules:
  - All non-aggregate fields in selectFields MUST be included in groupByFields
  - Use whereClause to filter rows BEFORE grouping
  - Use havingClause to filter AFTER grouping (for aggregate conditions)
  - ORDER BY can only use fields from groupByFields or aggregate functions
  - OFFSET is not supported with GROUP BY in Salesforce`,
    inputSchema: {
      type: "object",
      properties: {
        objectName: {
          type: "string",
          description: "API name of the object to query"
        },
        selectFields: {
          type: "array",
          items: { type: "string" },
          description: "Fields to select - mix of group fields and aggregates. Format: 'FieldName' or 'COUNT(Id) AliasName'"
        },
        groupByFields: {
          type: "array",
          items: { type: "string" },
          description: "Fields to group by - must include all non-aggregate fields from selectFields"
        },
        whereClause: {
          type: "string",
          description: "WHERE clause to filter rows BEFORE grouping (cannot contain aggregate functions)",
          optional: true
        },
        havingClause: {
          type: "string",
          description: "HAVING clause to filter results AFTER grouping (use for aggregate conditions)",
          optional: true
        },
        orderBy: {
          type: "string",
          description: "ORDER BY clause - can only use grouped fields or aggregate functions",
          optional: true
        },
        limit: {
          type: "number",
          description: "Maximum number of grouped results to return",
          optional: true
        }
      },
      required: ["objectName", "selectFields", "groupByFields"]
    }
  };

• src/index.ts:45-63 (registration)

  Registers the tool in the MCP server's listTools response by including AGGREGATE_QUERY in the tools array.

  server.setRequestHandler(ListToolsRequestSchema, async () => ({
    tools: [
      SEARCH_OBJECTS, 
      DESCRIBE_OBJECT, 
      QUERY_RECORDS, 
      AGGREGATE_QUERY,
      DML_RECORDS,
      MANAGE_OBJECT,
      MANAGE_FIELD,
      MANAGE_FIELD_PERMISSIONS,
      SEARCH_ALL,
      READ_APEX,
      WRITE_APEX,
      READ_APEX_TRIGGER,
      WRITE_APEX_TRIGGER,
      EXECUTE_ANONYMOUS,
      MANAGE_DEBUG_LOGS
    ],
  }));

• src/index.ts:101-117 (registration)

  Dispatches calls to 'salesforce_aggregate_query' by validating arguments, typing them as AggregateQueryArgs, and invoking the handleAggregateQuery function.

  case "salesforce_aggregate_query": {
    const aggregateArgs = args as Record<string, unknown>;
    if (!aggregateArgs.objectName || !Array.isArray(aggregateArgs.selectFields) || !Array.isArray(aggregateArgs.groupByFields)) {
      throw new Error('objectName, selectFields array, and groupByFields array are required for aggregate query');
    }
    // Type check and conversion
    const validatedArgs: AggregateQueryArgs = {
      objectName: aggregateArgs.objectName as string,
      selectFields: aggregateArgs.selectFields as string[],
      groupByFields: aggregateArgs.groupByFields as string[],
      whereClause: aggregateArgs.whereClause as string | undefined,
      havingClause: aggregateArgs.havingClause as string | undefined,
      orderBy: aggregateArgs.orderBy as string | undefined,
      limit: aggregateArgs.limit as number | undefined
    };
    return await handleAggregateQuery(conn, validatedArgs);
  }

• src/tools/aggregateQuery.ts:125-135 (helper)

  Helper function to validate that all non-aggregate selectFields are present in groupByFields, ensuring SOQL compliance.

  function validateGroupByFields(selectFields: string[], groupByFields: string[]): { isValid: boolean; missingFields?: string[] } {
    const nonAggregateFields = extractNonAggregateFields(selectFields);
    const groupBySet = new Set(groupByFields.map(f => f.trim()));
    
    const missingFields = nonAggregateFields.filter(field => !groupBySet.has(field));
    
    return {
      isValid: missingFields.length === 0,
      missingFields
    };
  }