Finding the columns and values from AggregateResult (or QueryResult/sObject) in Salesforce.com APEX

I have been doing some quite intensive Saleforce.com (Visualforce.com) custom site development recently. This has involved full front to back-end development on multiple levels along with integration into various jQuery and charting frameworks.

One issue I ran into I could find no immediate recommended solutions in the various forums. This was having the ability to iterate through a result set, determine the column names and values.

While retrieving the data was relatively simple through SOQL, after employing the various workarounds for the lack of ability to do simple joins. Once I had my query I needed to consume it dynamically from multiple sources (different AggregateResult sets).

In order to make the application robust the consumption of the AggregateResult set needed to be dynamic, and be able to adapt to varying data that might be supplied to it i.e. for the chart output we would have some standard fields, but there would be additional fields the number of which would vary depending on the result set.

In most development languages there is some object model to verify that a column exists and if it does get your value out of it. AggregateResult is the same if you are using C#, the result set is XML so you can confirm structure, but within APEX there is currently no ability to do that.

At first I though I could use “<my agg res>.getSObjectType().getDescribe().fields.getMap()”, but I soon found that only returned one field, an internal ID field, unrelated to my data.

After searching for a quick solution, nothing obvious could be found, so had to figures something else out.

I did try to serialize the result set but that failed as well. Apparently sObjects cannot be serialized (within APEX), which I found odd.

While trying to debug and figure out a solution, I stumbled onto the fact that the AggregateResult object would convert to a string… that led me to the solution.

The string (at least for now) follows a predictable format:

string somethingToWorkWith = string.valueOf(myAggregateResult);

Will give me a string which has the format:

AggregateResult:{MyColumn1=Value1, MyColumn2=Value2}

This allows me to pull out all of my data, with their types intact. Below are a couple of functions, one will get me all my columns in a set, the other will get me all my columns and values in a key/value map. Note that in order to preserve the original type (class) of data the later function’s value is a generic object, and it is pulled from the original AggregateResult, rather than from the converted string array.

/**
* Returns set of columns in the supplied AggregateResult.
**/
public static Set<string> AggResColumns(AggregateResult a) {
    if (a == null) return new Set<string>();
        set<string> myColumns = new set<string>();
        string ar = string.valueOf(a).removeStart(‘AggregateResult:’).removeStart(‘{‘).removeEnd(‘}’);
        string[] ars = ar.split(‘, ‘, 0);
        for (string pair :ars) {
            if (pair != null && pair != && pair.indexOf(‘=’) > 0) {
            string[] keyAndValue = pair.split(‘=’, -2); //We are using -2 deliberately.
           if (keyAndValue.size() > 0 && keyAndValue[0] != null && keyAndValue[0] != ) {
                myColumns.add(keyAndValue[0]);
            }
        }
    }

    return myColumns;
}

/**
* Returns map of columns and values in the supplied AggregateResult.
**/
public static map<string, object> AggResColumnsAndValues(AggregateResult a) {
map<string, object> myValues = new map<string, object>();
string ar = string.valueOf(a).removeStart(‘AggregateResult:’).removeStart(‘{‘).removeEnd(‘}’);
   
string[] ars = ar.split(‘, ‘, 0);
   
for (string pair :ars) {
       
if (pair != null && pair != && pair.indexOf(‘=’) > 0) {
           
string[] keyAndValue = pair.split(‘=’, -2); //We are using -2 deliberately.
           
if (keyAndValue.size() > 0 && keyAndValue[0] != null && keyAndValue[0] != ) {
               
myValues.put(keyAndValue[0], a.get(keyAndValue[0]));
           
}
        }
    }

    return myValues;
}

3 thoughts on “Finding the columns and values from AggregateResult (or QueryResult/sObject) in Salesforce.com APEX

  1. Actually, thanks to the JSON.deserializeUntyped, you don’t have to do any of that.

    Basically, the JSON.deserializeUntyped returns a Map that you can easily find the fields that you need. For example:

    
    AggregateResult resultOfInterest = [SELECT COUNT_DISTINCT(ParentId) FROM Account];
    Map aggregateResultMap = (Map)JSON.deserializeUntyped(JSON.serialize(resultOfInterest ));
    aggregateResultMap.remove('attributes');
    

    This gives you a key value map where the key is the field name and the value (as an Object which you can cast). So from there, you can simply call aggregateResultMap.keySet() to produce a unique set of the fields.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s