Getting AggregateResult variables from Execute Anonymous Window

Question

I am trying to execute a block of code using the Execute Anonymous Window that is meant to delete some duplicated data, but I am getting this weird error about a variable not existing even though my debugging says otherwise.

Here is what I have:

List<AggregateResult> ars = [SELECT YTPQuiz__c quiz FROM YTPQuestion__c GROUP BY YTPQuiz__c HAVING Count(Id) > 1];
System.debug(ars);
for (AggregateResult ar : ars) {
    System.debug(ar.quiz); 
}

In the for-loop you will see I simply print the quiz variable I assigned during the SOQL statement, but I get an error saying that this variable does not exist.

If I replace this debug line with System.debug(ar) the log print out a bunch of object like:

DEBUG|AggregateResult:{quiz=a1XY0000000012}
DEBUG|AggregateResult:{quiz=a1XY0000000013}
DEBUG|AggregateResult:{quiz=a1XY0000000014}
DEBUG|AggregateResult:{quiz=a1XY0000000015}

Yet, I can't access the quiz variable.

If I don't alias YTPQuiz__c as quiz then it will print out

DEBUG|AggregateResult:{YTPQuiz__c=a1XY0000000012}
DEBUG|AggregateResult:{YTPQuiz__c=a1XY0000000013}
DEBUG|AggregateResult:{YTPQuiz__c=a1XY0000000014}
DEBUG|AggregateResult:{YTPQuiz__c=a1XY0000000015}

Yet, I get the error that ar.YTPQuiz__c variable doesn't exist. I even tried ar.expr0 and it said that variable doesn't exist.

I've done this exact same method of accessing AggregateResults in APEX classes and it works perfectly fine, but not this Anonymous Execution Window... What could possibly be wrong?

Answer 1

You will need to utilize ar.get('alias-name') here based on the Working with SOQL Aggregate Functions documentation.

So in your case it will be:

ar.get('quiz');

If you didn't have any aliasing, then you would have to use expri depending on the appearance of the field in the query.

The key is not forgetting to use single quotes around your variable since it's actually a String: ar.get('quiz'), ar.get('expr0'), ar.get('YTPQuiz__c'). Forgetting them will result in a 'variable does not exist' error.

Answer 2

If you alias the field, you then call .get('alias'). In your case, you would do something like:

for (AggregateResult aggregate : [
    SELECT YTPQuiz__c quiz FROM YTPQuestion__c
    GROUP BY YTPQuiz__c HAVING Count(Id) > 1
]){
    Id quizId = (Id)aggregate.get('quiz');
}