Bulk match contacts based on email

Question

I've a list of email ids and I want to match it against contacts in Salesforce. I'm planning to do use SOQL like -

Select Id, Name from Contact where email IN ('[email protected], [email protected]')

I will be executing tis SOQL using REST API. What's the limit of emails that I can send in IN clause. My list is going to be pretty big, around 5000 emails.

I understand that based on limit, I can split the list and make multiple queries for each batch. I'm concerned about hitting REST Api limits this way. Is there a better way to do this, like creating a custom API or thing like that?

Answer 1

You can write a custom endpoint if you wanted to; Apex can bypass the normal character limits for a SOQL query. Here's the Apex for that:

@RestResource(urlMapping='/getContacts')
global class GetContacts {
  @HttpPost global static Contact[] getContactsByEmail(String[] emails) {
    return [SELECT Name, Email FROM Contact WHERE Email = :emails];
  }
}

To call this, you'd use a call like this:

POST /services/apexrest/getContacts HTTP/1.1
Host: something.my.salesforce.com
Content-Type: text/json
Authorization: Bearer <session-id>
Content-Length: <calculated-size>

{ "emails": [ "[email protected]", "[email protected]", ... ] }

You can theoretically retrieve up to 50,000 contacts this way. Of course, if this is just a one-shot, you might not want to waste your time with this, but if this is a regular thing, it might help you.

Answer 2

The limit for total character length is 20,000 characters, and the limit for WHERE clause lengih is 4,000. See also:

Salesforce Developer Limits and Allocations Quick Reference

Feature
SOQL statements

Limit
Maximum length of SOQL statements

Limit Description
By default, 20,000 characters.

Note

Long, complex SOQL statements, such as statements that contain many formula fields, can sometimes result in a QUERY_TOO_COMPLICATED error. The error occurs because the statement is expanded internally when processed by Salesforce, even though the original SOQL statement is under the 20,000 character limit. To avoid this error, reduce the complexity of your SOQL statement.

Page layouts in Lightning with more than 250 fields can also cause a QUERY_TOO_COMPLICATED error. Lightning uses auto-generated SOQL to retrieve fields for a record page layout, so the error can occur even if there isn’t any customer-written SOQL.

The character limit can also be reached by including too many currency fields. Currency fields require SOQL to use a format method, roughly doubling the field API name length for each currency field.

---

Feature
SOQL WHERE clause

Limit
Maximum length of SOQL WHERE clause

Limit Description
4,000 characters.