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
)
)
