To Find a Phrase within a View

We want to find all full-time employees. We know that "Full-Time" or "full time"—or something like that—is somewhere in our document structure.

An Optic query like this one finds a case-, punctuation-, and whitespace-insensitive phrase that could be anywhere in any document in the view’s context—not just in the view’s column data:

op.fromView('Employee', 'Profile')
  .where(cts.wordQuery('full time'))
  .offsetLimit(0, 100)
  .result();

We used this query to retrieve a row sequence with all our employee-view columns from any employee-collection document containing some form of the phrase "full time" anywhere within it, limited to 100 results:

• The Data Accessor Function fromView() pulls data indexed for the view Profile associated with the schema Employee into a row sequence of this view’s columns.

• The Operator Function where() restricts the rows returned to only those that satisfy the given Boolean expression.

• The CTS (Core Text Search) Function cts.wordQuery() finds the word or phrase provided in the first parameter:

  • With the phrase containing no uppercase letters and with no other parameters, the search defaults to finding variations with any case letters.

  • With the phrase containing no punctuation between the words and with no other parameters, the search defaults to also finding variations with punctuation between words.

  • With the phrase containing a single space between the words and with no other parameters, the search defaults to also finding variations with more than one space between the words.

  • Other cts.wordQuery() parameters provide other possibilities.

• The Operator Function offsetLimit() restricts results returned. The first parameter specifies the number of results to skip; the second, the number of results to return. So, (0, 100) returns the first 100 results.

• The Executor Function result() executes the query and returns the results as a row sequence.

Here is row 1 of the 100-row x 23-column result:

{
  "Employee.Profile.GUID": "095d4e63-4a1f-4fc1-b694-b681e2aa3ee0", 
  "Employee.Profile.HiredDate": "2019-06-12", 
  "Employee.Profile.Gender": "male", 
  "Employee.Profile.Title": "Mr.", 
  "Employee.Profile.GivenName": "Elmer", 
  "Employee.Profile.MiddleInitial": "H", 
  "Employee.Profile.Surname": "Morlan", 
  "Employee.Profile.StreetAddress": "3084 Bolman Court", 
  "Employee.Profile.City": "Springfield", 
  "Employee.Profile.State": "IL", 
  "Employee.Profile.ZipCode": "62701", 
  "Employee.Profile.Country": "US", 
  "Employee.Profile.EmailAddress": "ElmerHMorlan@rhyta.com", 
  "Employee.Profile.TelephoneNumber": "217-301-0206", 
  "Employee.Profile.TelephoneCountryCode": "1", 
  "Employee.Profile.Birthday": "7/31/37", 
  "Employee.Profile.NationalID": "333-82-1925", 
  "Employee.Profile.Point": "39.747955,-89.709328", 
  "Employee.Profile.BaseSalary": 47744, 
  "Employee.Profile.Bonus": 4774, 
  "Employee.Profile.Department": "Engineering", 
  "Employee.Profile.Status": "Active - Regular Exempt (Full-time)",        // Found!
  "Employee.Profile.ManagerGUID": "3ad0ffbc-3ade-4897-902b-718417a721f5"
}

• This query returned the first 100 results as we specified in offsetLimit().

• This query also could have found “Full-Time”, "Full Time", "FULL-TIME", "Full - time", and other variations.

• This query would NOT have found "fulltime" (or other case variations) because "fulltime" is indexed as one word while "full" and "time", separated by space and/or punctuation, are each indexed as separate words.

• Only one result will be returned per document no matter how many times the phrase occurs within a particular document.