SOSL query having issues with quotes (in the record(s) that should be returned, not the query string)

Question

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

1. Find an Account (or any SObject, really) with a text field
2. Put {:[{"extension":"8","phone":"6","price":"8","serviceId":"HG03524", "lineItemId":"514913","productName":"VIPreferred","productId":"100000000000002076"}]} into a text field
3. Open the dev console, and run the following SOSL query: FIND {HG03524} RETURNING Account(Id)
4. Observe that you get a result
5. 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"
6. Go back to the dev console, and clear the old SOSL result
7. Wait for Salesforce to rebuild its search indexes (couple of seconds to a minute, maybe a little more)
8. Execute the SOSL query again
9. 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"
• {:[{"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"
• {:[{"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"

Answer 1

Response from support

Well, it took the better part of a month and repeating myself to tier-2 support about half a dozen times, but I got a response.

This functionality is (WAD) working as designed.

A disappointing result, to be sure, but I was pointed to a help article that provides some more insight.

https://help.salesforce.com/articleView?id=000321225&type=1&mode=1

Information from the help articles

Reproduced:

Customer may find the global search not returning all the results when they are searching text from the Long Text Area fields. The reason for this is that:

To efficiently store and find information, the search engine breaks up record content into smaller pieces called tokens, and stores them in the search index. Please review How Does Search Break Up Information?, which lists examples of tokens created and indexed from record information.

Some content, such as JSON formatted strings, may have long unbroken text containing words, special characters, and punctuation. There is a limit on how many tokens can be created from these types of strings. This can result in searches on words found in these strings not returning expected results.

For this type of content try adding white space before and after, or in place of, punctuation/special characters.

The other linked article, "How Does Search Break Up Information?" tells us this:

Here are some examples of the tokens created and indexed from record information. You could find the record with a search using any token listed.

Alphanumeric terms are split at letter-number boundaries. Terms with non-alphanumeric characters (such as punctuation marks) are split at the non-alphanumeric characters into alphabetic, numeric, and alphanumeric tokens.

Summary

This is a problem with searching for tokens inside of JSON stored in longtextarea fields.

The tool(s) Salesforce uses to tokenize strings for text search has limits with the long, often unbroken string of characters in JSON.

At some point, or in some situations, the tokens generated from splitting from punctuation (commas, colons, quotes, brackets, braces, etc...) appears to only be alphabetic and numeric (leaving out alphanumeric).

Adding spaces between commas in JSON strings appears to make things work as expected (and keeps the JSON valid). If you're writing JSON to a longtext field, the following code should ensure that you won't run into issues when searching for a string within the JSON

String myJson = '{"s":[{"extension":"8","phone":"6","price":"8","serviceId":"HG03524", "lineItemId":"514913","productName":"VIPreferred","productId":"100000000000002076"}]}';
// splits and recombines the JSON string so that each comma has a space afterwords
myJson = String.join(myJson.split(','), ', ');

myObj.myField__c = myJson;

Using myJson = myJson.replace(',', ', '); would also work, but is a bit harder to digest in my opinion.

As for why searching for "HG\03524" worked, it appears that Salesforce is also tokenizing the search terms. "HG 03524", "HG\03524", "HG@03524", "HG[03524", and a slew of other search strings also work here.

If all of these get tokenized to "HG" and "03524", and the JSON string is tokenized at alphanumeric boundaries (also yielding "HG" and "03524"), then it makes sense why results are found. Perhaps the tokenization for search terms is limited to prevent searches like "HG03524" from turning up records that only contain "HG".