How do I change my SOQL below so that Shield Platform Encryption is supported on SSN and the query supports large amounts of data

Question

Our company has several packages and we've recently been asked to do some analysis on how to make them compatible with Salesforce Shield field encryption.

In the Shield encryption considerations doc, it states that encrypted fields can't be used in where clauses for SOQL and SOSL.

One of our packages uses a Contact field for Social Security Number that uniquely identifies a Contact within an org. How do you filter a SOQL/SOQL query when Social Security Number is encrypted? Especially if there are more than 50,000 Contact records in an org?

List<Contact> contacts = [select Id, Name from Contact where SocialSecurityNumber__c in :ssns];

Answer 1

If you have the ability to add a custom field where the last four of the SSN is masked I think there's a decent solution for you. If you can do that, you could query on the masked last four digits, then use a concatenation of the Contact Name and of the Last Four digits of the SSN to determine the correct account that you're looking for. That's the best scenario that comes to mind off the top of my head.