How to write SOSL IN query

Question

I've following SOQL query -

SELECT Id, Name, Email, Account.Id, Account.Name FROM Contact WHERE Email in :emails

Here emails is a comma separated list of email ids.

Can I write the same query in SOSL? I'll be running the query using rest API. The reason I want to do it using SOSL is -

a) I want to get around the limit of 4000 characters in SOQL where clause

b) I also want to search Lead object, not just Contact. This one is low priority though.

Answer 1

Yes, you can write the query in SOSL. There's a great bit of info here: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_sosl.htm

Please be aware that SOSL isn't the most precise way of finding records, so you may be better off even splitting your query into two SOQL queries and combining the result objects somehow.

I'm a little nervous that you might have a SOQL query of longer than 4000 characters. Do you really need every part of it? All the fields and all the WHERE clause parts? If so, you may wish to consider breaking your query into multiple queries and combining them.

Answer 2

When making a query via the REST API, you don't have the luxury of binding a collection with : to help avoid character limits on the WHERE clause, or the entire query.

Instead, your client code, wherever it lives and whatever language it's written in, must take responsibility for tracking the length of the generated query, which will include the literal values of whatever strings you are filtering against in your IN clause.

I've written code that does this for Ids in Python, which I previously discussed on SFSE here. In pseudo-Python, the algorithm looks like this:

query = 'SELECT Id, Name, Email, Account.Id, Account.Name FROM Contact {}'
where_clause = 'WHERE Email in ({})'
where_clause_length = len(where_clause) - 2 # 2 characters are the merge field {}
email_addresses = [] # put your email addresses in a collection.

# Iterate and make queries until we run out of email addresses
# n.b. there's surely a more elegant way to do this, but it works.
while len(email_addresses) > 0:
    # Start building a query.
    this_batch = '\'' + email_addresses.pop() + '\''

    # The maximum length of the WHERE clause is 4,000 characters
    # Iterate while (a) we have more emails and (b) we have space in this query.
    while len(email_addresses) > 0:
        # Make sure we have space for the next item.
        # We should escape or clean single quotes in the email address, which would add a bit of complexity.
        if where_clause_length + len(this_batch) + len(email_addresses[-1:]) + 3 < 4000:
            query += ',\'' + email_addresses.pop() + '\''
        else:
            break

    # Now run this query and iterate again.
    results = sf.query_all(query.format(where_clause.format(this_batch)))

    for res in results.get('records'):
        self.add_result(res)
        

SOSL

Whether to use SOSL is going to depend on your exact requirements. While SOSL can search multiple objects in a single operation, the semantics of a SOSL search are different than SOQL and subject to different limits. If your goal is to identify every single record containing these emails, SOSL is not a fit for you:

The search engine looks for matches to the search term across a maximum of 2,000 records (this limit starts with API version 28.0)

SOSL applies different limits for a given object or situation. If the search is for a single object, the full record limit is applied. If the search is global across multiple objects, each object has individual limits that total 2,000 records.

Instead, you'd just run SOQL queries across more than one object and aggregate your results in your client code.

Answer 3

You may want to consider assigning this task to an apex batch. Today 4000 may be enough, but not tomorrow. Check out this trailhead for more information.