Group By fails with invalid field - How to handle encrypted fields?

Question

I want to find every record that occurs at least two times. The result should be something like this

MyLineItem__c	Type__c	ItemValueString__c	Count
Foo	Boo	Booze	2
Yal	Bal	Jazz	2
Xul	Xal	Xray	3

I tried this query

SELECT MyLineItem__c, Type__c, 
       ItemValueString__c, Count(Id)
FROM MyDetail__c 
WHERE 
     CreatedDate > 2024-01-20T00:00:00.000Z
GROUP BY
     MyLineItem__c, Type__c, ItemValueString__c

and got

INVALID_FIELD [object Object]: MyLineItem__c , Type__c, ItemValueString__c ^ ERROR at Row:1:Column:196 field 'ItemValueString__c' can not be grouped in a query call

The field ItemValueString__c is encrypted

Based on What types of fields are groupable in a SOQL GROUP BY clause? i assume that the encryption is the cause for the error message.

Questions

• How to handle encypted fields?
• What can be done to analyze these field?

Answer 1

You cannot use shield encrypted fields in WHERE, GROUP BY and ORDER BY clauses. This is covered in the documentation. You might consider adding a field to hold the hash of the data for the encrypted field and use that in your GROUP BY.

Since your ItemValueString__c field is a string, you can easily add a second field, like ItemValueHash__c, that is an integer. You would then include automation (through an apex trigger) that reacts when the ItemValueString__c is set or updated (so in before insert and before update):

for (MyDetail__c newRecord : Trigger.new) {
    MyDetail__c oldRecord = (Trigger.oldMap != null) ?
        Trigger.oldMap.get(newRecord.Id) :
        null;

    if (newRecord.ItemValueString__c != oldRecord?.ItemValueString__c) {
        newRecord.ItemValueHash__c = newRecord.ItemValueString__c.hashCode();
    }
}

Now you can GROUP BY ItemValueHash__c instead. Other aspects of the query may require update too (such as not including the ItemValueString__c in the results).