SEARCH_PREVIEW (SNOWFLAKE.CORTEX)¶

Syntax¶

SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
    '<service_name>',
    '<query_parameters_object>'
)

Arguments¶

service_name

Name of your Cortex Search service. Use the fully qualified name if the service is in a schema different from the current session.

query_parameters_object

A STRING that contains a JSON object that specifies the query parameters for invoking the service.

Key	Type	Description	Default
query	String	Your search query, to search over the text column in the service.	This is required.
columns	Array	A comma-separated list of columns to return for each relevant result in the response. These columns must be included in the source query for the service.	Search column that was specified when the service was created.
filter	Object	A filter object for filtering results based on data in the ATTRIBUTES columns. For detailed syntax, see Filter syntax.	Empty object
limit	Integer	Maximum number of results to return in the response.	10

Filter syntax¶

Cortex Search supports filtering on the ATTRIBUTES columns specified in the CREATE CORTEX SEARCH SERVICE command.

Cortex Search supports five matching operators:

• TEXT or NUMERIC equality: @eq

• ARRAY contains: @contains

• NUMERIC or DATE/TIMESTAMP greater than or equal to: @gte

• NUMERIC or DATE/TIMESTAMP less than or equal to: @lte

• primary key equality: @primarykey

These matching operators can be composed with various logical operators:

• @and

• @or

• @not

The following usage notes apply:

• Matching against NaN (‘not a number’) values in the source query are handled as described in Special values.

• Fixed-point numeric values with more than 19 digits (not including leading zeroes) do not work with @eq, @gte, or @lte and will not be returned by these operators (although they could still be returned by the overall query with the use of @not).

• TIMESTAMP and DATE filters accept values of the form: YYYY-MM-DD and, for timezone aware dates: YYYY-MM-DD+HH:MM. If the timezone offset is not specified, the date is interpreted in UTC.

• @primarykey is only supported for services configured with a primary key. The value of the filter must be a JSON object mapping every primary key column to its corresponding value (or NULL).

These operators can be combined into a single filter object.

Example¶

• Filtering on rows where string-like column string_col is equal to value value.

  { "@eq": { "string_col": "value" } }
  

  Copy

• Filtering to a row with the specified primary key,

  { "@primarykey": { "region": "us-west-1", "agent_id": "abc123" } }
  

  Copy

• Filtering on rows where ARRAY column array_col contains value value.

  { "@contains": { "array_col": "arr_value" } }
  

  Copy

• Filtering on rows where NUMERIC column numeric_col is between 10.5 and 12.5 (inclusive):

  { "@and": [
    { "@gte": { "numeric_col": 10.5 } },
    { "@lte": { "numeric_col": 12.5 } }
  ]}
  

  Copy

• Filtering on rows where TIMESTAMP column timestamp_col is between 2024-11-19 and 2024-12-19 (inclusive).

  { "@and": [
    { "@gte": { "timestamp_col": "2024-11-19" } },
    { "@lte": { "timestamp_col": "2024-12-19" } }
  ]}
  

  Copy

• Composing filters with logical operators:

  // Rows where the "array_col" column contains "arr_value" and the "string_col" column equals "value":
  {
      "@and": [
        { "@contains": { "array_col": "arr_value" } },
        { "@eq": { "string_col": "value" } }
      ]
  }
  
  // Rows where the "string_col" column does not equal "value"
  {
    "@not": { "@eq": { "string_col": "value" } }
  }
  
  // Rows where the "array_col" column contains at least one of "val1", "val2", or "val3"
  {
    "@or": [
        { "@contains": { "array_col": "val1" } },
        { "@contains": { "array_col": "val1" } },
        { "@contains": { "array_col": "val1" } }
    ]
  }
  

  Copy

Returns¶

Returns an OBJECT that contains the result of your query from your Cortex Search service and a unique request ID. See example output in Examples.

Usage notes¶

• This function incurs more latency than using the Python API. It is designed for testing/validation purposes only. You should not use this function to serve search queries in an end-user application that requires low latency.

• This function only operates on string literal queries. It does not accept a batch of text data.

Examples¶

This example queries a service named sample_service with a test query. The example returns five results (at most) and includes the data from the col1 and col2 columns.

SELECT
  SNOWFLAKE.CORTEX.SEARCH_PREVIEW (
      'mydb.mysch.sample_service',
      '{
          "query": "test query",
          "columns": ["col1", "col2"],
          "limit": 3
      }'
  );

{
  "results":[
      {"col1":"text", "col2":"text"},
      {"col1":"text", "col2":"text"},
      {"col1":"text", "col2":"text"}
  ],
  "request_id":"a27d1d85-e02c-4730-b320-74bf94f72d0d"
}