Salesforce Docs Portal

Hybrid Search Query Expressions

Enhance your hybrid search queries with pre-filtering fields and join expressions to access fields.

Filter Expressions in Hybrid Search

Expressions Types Examples Notes
Expressions with NOT, IN, AND, OR operators 
strField1 != 'v1' and
strField2 = 'v2' and
(strField3 = 'v3' or
strField4 not in ('a', 'b', 'c') or
strField5 in ('a', 'b', 'c')
) and
strField6='v6'
You can combine individual filters using the logical and and or operators.
Simple pre-filter expression in a SQL expression 
select * from 
        hybrid_search(
            TABLE('some_VDMO'),
            'some_search_text',
            'RecordId__c=''1c1c533c-1bc0-40cb-8bd8-f4b6e6e101b7''',
            10)
        )
 When the pre-filter condition is part of the SQL expression the quotes in the filter expression have to be escaped by doubling them.
Complex pre-filter expression in a SQL expression 
select * from
        hybrid_search(
            TABLE('some_VDMO'),
            'some_search_text',
            'strField1 != ''v1'' and strField2 = ''v2'' and 
            (strField3 = ''v3'' or
            strField4 not in (''a'', ''b'', ''c'') or
            strField5 in (''a'', ''b'', ''c'')) and
            strField6=''v6''', 10)
 When the pre-filter condition is part of the SQL expression the quotes in the filter expression have to be escaped by doubling them.
Pre-filtering on a field that is of Number data type 
select * from 
        hybrid_search(
            TABLE('some_VDMO'),
            'some_search_text',
            'NumField=2',
            10)
select * from 
        hybrid_search(
            TABLE('some_VDMO'),
            'some_search_text',
            'NumField>2',
            10)
 —
Pre-filtering on a field that is of Date/DateTime data type with the standard (Z) offset 
select * from
        hybrid_search(
            TABLE('some_VDMO'),
            'some_search_text',
            'DateTimeTypeWithTimezone=2024-05-08T15:28:07.000Z',
            10)
select * from
        hybrid_search(
            TABLE('some_VDMO'),
            'some_search_text',
            'DateTimeTypeWithTimezone>=2024-05-08T15:28:07.000Z',
            10)
 The date value doesn’t have to be surrounded by two single quotes (as vector search does).
Pre-filtering on a field that is of Date/DateTime data type with a custom offset 
select * from (
        hybrid_search(
            TABLE('some_VDMO'),
            'some_search_text',
            'DateTimeTypeWithTimezone=2024-05-08T15:28:07.000-08:30',
            10)
 The date value doesn’t have to be surrounded by two single quotes (as vector search does).
Pre-filtering on a field with Date only 
select * from 
        hybrid_search(
            TABLE('some_VDMO'),
            'some_search_text',
            'DateType=2024-05-08',
            10)
select * from 
        hybrid_search(
            TABLE('some_VDMO'),
            'some_search_text',
            'DateType<2024-05-08',
            10)
 The date value doesn’t have to be surrounded by two single quotes (as vector search does).
Pre-filtering on a field which is of string data type with the LIKE operator. 
select * from
hybrid_search(
TABLE('some_VDMO'),
'some_search_text',
'StrField LIKE ''prefix%''',
10)

The LIKE operator must be used with a value with a single trailing wildcard character (%). The following are examples of invalid LIKE filters.

'StrField LIKE ''%prefix%'''
'StrField LIKE ''%prefix'''
'StrField LIKE ''prefix%suffix'''

The LIKE operator doesn't support the following characters, : ^ { } [ ] ( ). For example, the expression 

Field__c LIKE 'https:%'

, is invalid because it contains a colon (:).

Case Sensitivity on Pre-filtering Fields

Names of pre-filtering fields are case insensitive. For example, if the name of a field is My_Column_c , a filter expression with my_column_c correctly references that field. However, case matters for values used within filters. If the field has a value SomeValue,  the filter expression My_Column_c='somevalue' or My_Column_c='SOMEVALUE' results in a no-match—only the expression My_Column_c='SomeValue' matches that record.

Default Pre-filtering Fields

The field DataSource__c from the chunk data model object (CDMO) is a default pre-filtering field. It is always available in pre-filtering queries.

Pre-filtering Values for Objects in 1:N Relationships

When creating a search index, you can index only one pre-filtering value.

Consider this example. You create a search index in which the indexed chunk data model object (CDMO) is in a 1:N relationship with related records from which a pre-filtering value is extracted. In the relationship, the CDMO {"chunkId":"1"} matches both {"filterRecordId"="123": "filterValue": "tag1"} and {"filterRecordId"="123": "filterValue": "tag2”}.

Data 360 indexes only one of these filtered values for retrieval, and there’s no guarantee which one is indexed. For example, Data 360 may index{"chunkId":"1", "filterValue":"tag1"} or {"chunkId":"1", "filterValue":"tag2"} but not {"chunkId":"1", "filterValue":["tag1", "tag2”]}.

Join Expressions in Hybrid Search

Include joins to access fields from both the Chunk DMO and Search Index DMO associated with the index.

SELECT
  src.ArticleNumber__c,
  hsearch.hybrid_score__c
FROM
  hybrid_search(	
    TABLE(Knowledge_index__dlm),
    'DataWedge Decode Volume',
    '',
    10
  ) hsearch
  join Knowledge_chunk__dlm chnk 
on hsearch.SourceRecordId__c = chnk.RecordId__c
  join Knowledge_kav_Home__dlm src 
on chnk.SourceRecordId__c = src.Id__c