Help with hybrid search function

[the_peterlandis]

I need help writing a filter.  I want to pre-filter a vector index before performing a hybrid search and create this as a function.  Below is a simple example of searching for products for a given customer.  A prefilter is key as this provides authorizations for searching a vector index before applying the top k which reduces the vector space searching as a prefilter before the search.  I am not seeing any filter capability like how you would call the API.
Example of the search API with prefilter

results = index.similarity_search(
   query_text=question,
   query_type = "HYBRID",
   columns=["content", "product", "product_description", "product_id", "purchase_date"],
   filters="{"customer_emai":customer_email},
   num_results=5
)

Below is the SQL Function I need help on

%sql

CREATE OR REPLACE FUNCTION get_customer_products(

search_query string comment 'Search query',

customer_email string comment 'Customer email',

)

RETURNS TABLE (

product STRING,
product_description STRING,

product_id int,

purchase_date_timestamp timestamp

)

COMMENT 'Retrieve the customer products'

LANGUAGE SQL

RETURN (

SELECT

content,

product_id,

purchase_date_timestamp

from

vector_search(

index=> 'gold.my_store.products',

query => search_query,

query_type => 'HYBRID',

filter => '{"customer_email": "' || customer_email || '"}',

num_results => 5

)

)

[the_peterlandis]

Based on this documentation, it says, it indicate sql function VECTOR_SEARCH cannot apply pre filter which prefilter is a fundamental capability for vector search.  Just very surprised this is not supported.

[KaushalVachhani · Accepted solution]

@the_peterlandis, Yes, currently vector_search SQL function doesn't provide pre filter support. However, if you must implement the UC function for this, you can do it something like below using Python code with filters.

%sql CREATE OR REPLACE FUNCTION kaushal.kaushal.vector_similarity_search( query_text STRING, filter_id INT, num_results INT ) RETURNS STRING LANGUAGE PYTHON COMMENT "Vector similarity search using authenticated client" ENVIRONMENT ( dependencies = '["databricks-vectorsearch", "databricks-sdk"]', environment_version = 'None' ) AS $$ import json import os # Get credentials from Databricks secrets # You'll need to set these up first def get_secret(scope, key): from databricks.sdk import WorkspaceClient w = WorkspaceClient() return w.secrets.get_secret(scope=scope, key=key).value # Alternative: if dbutils is available in UDF context # token = dbutils.secrets.get(scope="your-scope", key="databricks-token") # host = dbutils.secrets.get(scope="your-scope", key="databricks-host") # Set environment variables for authentication os.environ['DATABRICKS_HOST'] = get_secret("your-scope", "databricks-host") os.environ['DATABRICKS_TOKEN'] = get_secret("your-scope", "databricks-token") from databricks.vector_search.client import VectorSearchClient # Initialize client - should now pick up environment variables client = VectorSearchClient() index = client.get_index( endpoint_name="vector-search-demo-endpoint-kaushal", index_name="kaushal.kaushal.my_text_data_index" ) results = index.similarity_search( query_text=query_text, columns=["id", "content"], filters={"id": [filter_id]}, num_results=num_results ) return json.dumps(results.get('result', {}).get('data_array', [])) $$;

Then run your UC function with SQL, and you should get the expected results.