salesforce_aggregate_query

Instructions

Implementation Reference

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

  Main handler function that validates input parameters, constructs SOQL aggregate query with GROUP BY, HAVING, etc., executes via Salesforce connection, formats grouped results, and handles errors.

  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)

  Tool definition including name, description, and inputSchema specifying parameters for aggregate queries (objectName, selectFields, groupByFields, whereClause, havingClause, orderBy, limit).

  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 AGGREGATE_QUERY in the MCP server's listTools response, making the tool discoverable.

  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)

  Switch case in CallToolRequestSchema handler that validates arguments and dispatches to handleAggregateQuery for execution.

  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 included 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
    };
  }