Everything You Wanted to Know About Kona’s Query Language

Notes

  • To force a Boolean expression to resolve first, encase it in parentheses. Parentheses can be nested.
  • To search for an exact term (e.g. phrase), surround it with double quotes (“”). To search for a double quote inside a quoted term use a backslash (\) in front of it (\”).
  • Double quotes must be standard ASCII double quotes (“), not the curly version (“”) often created in word processors.
  • Operator words (AND, OR, NOT) are case-sensitive and must be all caps.
  • Dashes are recognized as the NOT operator except when they’re in a quoted phrase, or in the phrase portion of a fielded search.
  • Query operators are binary, not n-ary, so A & B & C yields AND(AND(A,B),C), not AND(A,B,C)
  • Searching for punctuation is not supported except for specific terms like C++ and C# or the punctuation is inside a quoted phrase.
  • Search terms are case-insensitive.

Examples

Search Term Explanation
a AND b Results containing both a and b.
a b Same as previous example.
a & b Same as previous example.
a “AND” b a AND “AND” AND b. Results containing all of a, “and”, and b.
a OR b Results contain either a or b (and can have both).
a | b Same as previous example.
a b OR c d (a AND b) OR (c AND d). Results that have both a and b, or both c and d.
a b OR a c (a AND b) OR (a AND c). Results that have a, and also b or c.
a (b OR c) a AND (b OR c). Same as previous example but more efficient.
(a OR b) (c OR d) (a OR b) AND (c OR d). Results that contain one of a or b, and one of c or d.
a NOT b a AND NOT(b). Results contains a but not b.
a –b Same as previous example.
a b OR c -d (a AND b) OR (c AND NOT(d)). Results that have both a and b, or c but not d.
“nip & tuck” Results that contain the phrase “nip tuck” (the & is ignored because it’s punctuation). Note that “nip and tuck” is not found because “and” is a word, unless ‘&’ and ‘and’ are listed in your thesaurus as synonyms.

Wildcards

?    Single character. Can appear anywhere in a word and more than once in a word.

*    1 or more characters. Must only appear once per phrase and as the last character.

Examples

A*    All terms starting with “A”.

AB?    All 3 letter terms that start with AB.

Proximity Search

a NEAR[5] b    Results containing both a and b within 5 terms of each other.

a ONEAR[5] b    Results containing both a and b where b is within 5 terms after a (results with b before a are not included).

Proximity search only accepts terms and phrases, not expressions, so the following is illegal:

a NEAR[5] (b OR c)

Because (b OR c) is an expression. But this expanded version of the query will work:

(a NEAR[5] b) OR (a NEAR[5] c)

A “term” is any contiguous collection of alphanumeric characters, generally, a word. Non-alphanumeric characters act as delimiters for defining the separation of these terms and all of them are converted to space characters with some exceptions: periods not followed by a space (e.g. “mywebsite.com”, “12.99”) and underscores (e.g. “rob_roy”).

Field Level Search

You can search on a specific field to find a term or resolve an inequality. A fielded search can be part of a longer query term. The syntax of a fielded search is as follows:

kona_field_name<operator>search_term

Operator Explanation
: = Search term must exist in the field.
< Value in field is less than search_term (used with numbers).
<= Value in field is less than or equal to search_term (used with numbers).
> Value in field is greater than search_term (used with numbers).
>= Value in field is greater than or equal to search_term (used with numbers).
<> != Value in field is not equal to search_term.

kona_field_name is defined as follows:

CONCATENATE(LOWERCASE(Salesforce API name),  _, Kona data type)

SFDC Data Type Kona Data Type Kona Virtual Field Multi-Data Type
Auto Number double doublemultipicklist
Checkbox boolean booleanmultipikclist
Currency currency currencymultipicklist
Date date datemultipicklist
Date/Time datetime datetimemultipicklist
Email email emailmultipicklist
Geolocation double, double
(latitude, longitude)
doublemultipicklist,
doublemultipicklist
Number double doublemultipicklist
Percent double doublemultipicklist
Phone phone phonemultipicklist
Picklist picklist multipicklist
Picklist (Multi-Select) multipicklist multipicklist
Text string multipicklist
Text Area textarea multipicklist
Text Area (Long) textarea multipicklist
Text Area (Rich) textarea multipicklist
Text (Encrypted) textarea multipicklist
Time datetime datetimemultipicklist
URL url urlmultipicklist
Reference reference referencemultipicklist
Additional Primitive Types
Blob string multipicklist
Boolean boolean booleanmultipikclist
Decimal double doublemultipicklist
ID reference referencemultipicklist
Integer integer integermultipicklist
Long double doublemultipicklist
String textarea multipicklist

Examples

SF Field Name SF Data Type Kona Field Name
Name Text name_string
CellPhone__c Phone cellphone__c_phone
CloseRate Percent closerate_double
State Picklist state_picklist
Description Text Area (Rich) description_textarea

The Kona field name will search ANY field of the same name, regardless of object type. To filter your results to one or more object types, use the table field, as in:

table:Contact    Only searches Contact records.

Salesforce Constants

Kona supports some of the popular Salesforce constants:

TODAY

YESTERDAY

TOMORROW

THIS_WEEK

LAST_WEEK

NEXT_WEEK

THIS_MONTH

LAST_MONTH

NEXT_MONTH

THIS_YEAR

LAST_YEAR

NEXT_YEAR

Examples

SFDC API Field SFDC Data Type Field Query
First_Name Text(255) first_name_string:andrew

Search for “andrew” in all fields called First_Name regardless of object.

First_Name Text(255) table:Contact first_name_string:Andrew

Search for “andrew” in all the First_Name field for the Contact object.

Salary__c Currency(7,2) salary__c_currency>100000

Search for all salaries over $100,000.

DoNotCall Checkbox table:Lead donotcall_boolean:false

Only return leads where DoNotCall is not checked.

DeferredReason__c Text Area(500) deferredreason__c_textarea:*

Return records that have something defined for deferred reason.

CreatedDate Date createddate_date:YESTERDAY

Return all records that were created yesterday.

Notes

  • Do NOT put spaces before or after the operators. The logic will be incorrect.
  • For the table field use the name of the table as it is used in Salesforce.
  • Multiselect Picklists are indexed as separate values in a multi-valued field (sort of like an array). As with any field with text, you can search on one or more of the values.