Write SOSL Queries

Learning Objectives

After completing this unit, you’ll be able to:

• Understand how SOSL differs from other full-text search alternatives.
• Identify the basic SOSL syntax.
• Describe the differences between SOSL and SOQL.
• Build a SOSL query that searches across multiple sObjects.

What Is SOSL?

In the last unit you were introduced to SOQL, and you learned how you can use it to query data in sObjects and their related tables. To perform text-based queries across multiple sObjects, you can use SOSL (Salesforce Object Search Language), Salesforce’s option for full-text searching.

As a .NET developer, you might be familiar with the full-text searching capabilities available with Microsoft SQL Server (MS FTS). You might also be familiar with a popular search engine library called Lucene.Net, which was ported from the original Java version to C#.

The good and bad news here is that SOSL is different from those full-text search alternatives. The biggest differences have to do with what is required to set up and maintain the indexes. Salesforce uses the open-source search and indexing engine Lucene to power SOSL, but it’s been set up so that you don’t have to worry about installing and configuring anything. You’re also not responsible for maintaining indexes. In fact, for the most part, you don’t have much control over how search indexes are used, which is good news because it means that writing SOSL queries is easy.

However, as a .NET developer you love to tweak and fine-tune your code. So hearing out that you have little control over the configuration and indexes might make you feel a tad uncomfortable. We want to reassure you that even though you have less control, you can still do things that improve query performance, for both SOSL and SOQL queries. Shortly, we’ll be covering these methods quite extensively, but for now, let’s go over how SOSL syntax differs.

Building a SOSL Query

If you’re familiar with MS FTS, you know that you need to write SELECT queries that use a CONTAINS or FREETEXT statement. The powerful CONTAINS search, which has many variations, allows you to search for exact or fuzzy matches, as well as search for words that are close to another word.

SOSL uses a simpler search syntax that doesn’t use the SOQL SELECT keyword. Instead, it uses the FIND keyword. A basic query looks like the following:

FIND {"grand*"} IN ALL FIELDS RETURNING Account(Name), Contact(LastName, FirstName, Email)

Breaking this down, we see that it has three parts.

FIND Clause with Search Term

The FIND clause is required. It’s what makes the SOSL search unique. FIND is followed by whatever search term you’re looking for, whether it’s a single word or phrase. In this case, it’s the word “grand.” You can also include wildcard characters, which our example does, such as these two:

* matches zero or more characters at the middle or end of the search term
? matches only one character at the middle or end of the search term

We talk more about wildcards in the next unit. But in the meantime, be aware that just because you can use a wildcard in both the middle and end of the search term, doesn’t mean you should. In fact, as a best practice, it’s not a good idea, because it can seriously impact query performance.

IN Clause

Use the IN clause to specify a search group. It tells Salesforce which fields to search. But we have a really big thing that you need to be aware of here, so bear with us a bit.

In the example above, we specified ALL FIELDS. One might take this literally and think that all fields will be searched, but depending on which object you specify in the RETURNING clause, only certain text-based fields are included. Say what?

Like we said, bear with us here. You see, if the object you’re returning is an article, document, feed comment, feed item, file, product, or solution, ALL fields are searched because these are the types of objects where you would want to search all fields.

But if you’re searching through most standard or custom objects, which include all sorts of crazy non-text based fields, it makes sense to only search through the name, email, phone, and sidebar fields. By the way, this is the default behavior.

So let’s say that instead of specifying ALL FIELDS, you want to search only name fields. Then you would use NAME FIELDS. To search only phone fields, use PHONE FIELDS. See how this works?

RETURNING Clause

You use the RETURNING clause to specify which data to return and which objects to search. In the example above, we returned the Name field from Account and the Lastname, FirstName and Email fields from Contact. The search looked through all searchable fields on both objects, but the returned results only include those fields listed in parenthesis.

If you specify an object name without a field name in parenthesis, the search return only the ID for that object if a match is found.

The SOSL syntax has other optional clauses that you might be interested in, Check out the official docs to learn more about them.

Ok, so now you’re wondering how does SOSL compare to other full-text search engines when it comes to fuzzy matches. Other than the LIKE keyword that you can use in a SOQL query and the wildcards that you can use in the SOSL query, Salesforce provides only a synonym search to look for nicknames.

SOQL or SOSL?

Which one do you use? I’m sure you won’t be surprised to hear this: It depends on what you’re trying to accomplish.

If you need data from a single object and you specifically know the criteria for that object, you’ll most likely want to use SOQL. In fact, most of your queries will probably be in SOQL.

SOSL is most useful when you don’t know the exact fields and objects that your data resides in, and you need to search across multiple objects. This is especially true when those objects aren’t related because SOQL works only with related objects.

Execute a SOSL Search

So now that you know why and how to use a SOSL search, let’s go ahead and try building one.

You can execute a SOSL search in more than one way, but right now we’re going to focus on the easiest one, which is using Query Editor in Developer Console. You will probably remember this from the last unit on SOQL.

Prerequisites

Before running the SOSL search, we need to add data to the development org. First, we’ll create a sample document that we can upload to our development org.

1. In Notepad or any text-based editor, create a file and enter the following text:

   First quarter figures were better than expected for new employee Joseph Smith.
   

2. Save the file to your local machine with the name TestDocument.txt.
3. In your Developer Edtion org, on the Files tab, click Upload Files.
4. Browse to the location where you saved the TestDocument.txt file and click Open.
5. Click Done.

We also need to add data to some of our standard objects.

1. From the Setup menu, select Developer Console to open Developer Console.
2. In the Developer Console, select Debug > Open Execute Anonymous Window.
3. Delete the existing code, and insert the following snippet:

   // Add Account and related Contact
   Account acct = new Account(
       Name='Test Account',
       Phone='(225)555-8989',
       NumberOfEmployees=10,
       BillingCity='Baton Rouge');
   insert acct;
   // Get the Id of the Inserted Account
   ID acctID = acct.ID;
   // Add a contact to the Account.
   Contact con = new Contact(
       FirstName='Joseph',
       LastName='Smith',
       Phone='(225)555-8787',
       Email='jsmith@testaccount.com',
       AccountId=acctID);
   insert con;
   

4. Click Execute.

Use Developer Console

Now that we have sample data to search for, we can use the Query Editor to execute a new SOSL search.

1. In Developer Console, click the Query Editor tab in the bottom pane.
2. Delete the existing code, and insert the following snippet:

   FIND {joey} IN ALL FIELDS RETURNING Account(Name), Contact(LastName, FirstName), ContentVersion(Title)
   

First thing you notice here is that the single quotes that surrounded the search term in our earlier example have been replaced with curly brackets. We must use these instead of quotes when the SOSL search is done in the Query Editor. If the search was done in Apex code, you would use single quotes. Also notice that the search term is the word “joey,” which is a known nickname for Joseph (the actual name we’re searching for is Joseph).

1. Click Execute. The search results show only one tab. The Contact that you inserted is listed under the Contact tab because it was able to find a match based on the nickname feature made available in Winter ’16. But the TestDocument you uploaded isn’t listed because the nickname search applies only to Account, Contact, Lead, and User objects. Let’s revise our test, and get back all the results.
2. Replace the existing code in Query Editor with the following:

   FIND {jos*} IN ALL FIELDS RETURNING Account(Name), Contact(LastName, FirstName), ContentVersion(Title)
   

3. Click Execute.
4. Your search results now display two tabs. The Document tab displays the name of the document you uploaded. Notice that we used a wildcard search to get back all the results we wanted.

Tell Me More

It’s possible for a SOSL search to NOT find all the matching results because of limits. Remember, Salesforce is a multi-tenanted environment. If every customer was allowed to max out the resources, the whole system would crash in no time. Searches that exceed 20,000 characters can tax the system. Therefore, searches are limited in SOSL just like they are in SOQL. Next, we’re going to learn how to optimize your search queries.