Tried to do some due diligence before asking this one. I have read the SOSL documentation, and have done some cursory searching here on SFSE.
Scenario
I have an integration with an external system, which sends JSON payloads to Salesforce via an Apex REST webservice. We log the payload to a custom object, and store the payload verbatim in a Longtext(32000)
field, in case we need to go back and investigate issues that pop up with the integration.
One of the integration events that we're handling needs to check to see if a given id serviceId
from our external system has been included in a previous event payload of type "serviceBillingCompleted".
To accomplish this, since longtext fields can't be filtered in SOQL queries, I'm relying on the following SOSL query
List<List<SObject>> soslResult = [
FIND :payload.serviceId
RETURNING
Log__c (id WHERE Class_Name__c = 'AtlasServiceOrderEvent.ServiceBillingCompleted')];
The Problem
Given a Log__c
record that contains {"services":[{"extension":"8","phone":"6","price":"8","serviceId":"HG03524","lineItemId":"514913","productName":"VIPreferred","productId":"100000000000002076"}]}
, and where Class_Name__c
== AtlasServiceOrderEvent.ServiceBillingCompleted, my SOSL query returns no results when I use "HG03524" as the search query.
What I've Tried and Figured Out So Far
- Adding wildcards before, after, and both before and after the search query does not cause the target record to be returned
- Adding a double-backslash between the letters and the numbers, e.g. "HG\\03524" works, and my target record is returned
- Separating the letters from the digits in the search query, e.g. "HG 03524" also works
- The leading zero in my serviceIds don't appear to be the issue, going from letters to digits seems to be the important distinction
- I think I've narrowed down the issue to being with quotes (single or double) being separated by something other than whitespace. If I modify my logged payload to be
{:[{"extension":"8","phone":"6","price":"8","serviceId":"HG03524", "lineItemId":"514913","productName":"VIPreferred","productId":"100000000000002076"}]}
(simply removing"s"
near the beginning and adding a space between"HG03524",
and"lineItemId"
), the SOSL query returns the target record
How To Reproduce
- Find an
Account
(or any SObject, really) with a text field - Put
{:[{"extension":"8","phone":"6","price":"8","serviceId":"HG03524", "lineItemId":"514913","productName":"VIPreferred","productId":"100000000000002076"}]}
into a text field - Open the dev console, and run the following SOSL query:
FIND {HG03524} RETURNING Account(Id)
- Observe that you get a result
- Edit the text field on your target Account to be
{"s":[{"extension":"8","phone":"6","price":"8","serviceId":"HG03524","lineItemId":"514913","productName":"VIPreferred","productId":"100000000000002076"}]}
(the difference is the extra"s"
near the beginning and the lack of whitespace between"serviceId":"HG03524",
and"lineItemId"
- Go back to the dev console, and clear the old SOSL result
- Wait for Salesforce to rebuild its search indexes (couple of seconds to a minute, maybe a little more)
- Execute the SOSL query again
- Observe that you get 0 results
The question
Can anyone tell me what it is about the string {:[{"extension":"8","phone":"6","price":"8","serviceId":"HG03524","lineItemId":"514913","productName":"VIPreferred","productId":"100000000000002076"}]}
that causes this odd SOSL behavior?
Does it have something to do with Solr or Lucene (which I believe Salesforce uses for SOSL)?
Why do the various workarounds I've found so far work?
+edit 1:
Still working through Salesforce support. Updated the reproduction to one that I've explicitly tested does not work with wildcards (the first reproduction I gave actually did work with wildcards included).
To be clear:
{"s":[{"extension":"8","phone":"6","price":"8","serviceId":"HG03524","lineItemId":"514913","productName":"VIPreferred","productId":"100000000000002076"}]}
- does not work with searching for "
HG03524
", "*HG03524
", "HG03524*
", or "*HG03524*
" - does work with searching for "
HG 03524
" and "HG\\03524
"
- does not work with searching for "
{:[{"extension":"8","phone":"6","price":"8","serviceId":"HG03524","lineItemId":"514913","productName":"VIPreferred","productId":"100000000000002076"}]}
- does not work with searching for "
HG03524
" or "*HG03524
" - does work with searching for "
HG03524*
", "*HG03524*
", "HG 03524
", and "HG\\03524
"
- does not work with searching for "
{:[{"extension":"8","phone":"6","price":"8","serviceId":"HG03524", "lineItemId":"514913","productName":"VIPreferred","productId":"100000000000002076"}]}
- works with searching for any of the example strings involving "HG03524"