Task 04: Implement a filtered Semantic Search with SQL

Introduction

Adatum Corporation wants to enhance their search capabilities by combining traditional SQL queries with vector-based search functionalities. The hybrid approach will allow Adatum to provide more accurate and relevant search results.

Description

In this task, you’ll implement a filtered semantic search using SQL. Specifically, you’ll use vector embeddings to find the most relevant products based on a textual description and apply filters for availability and sale price. Then, you’ll perform a hybrid search in a SQL database, leveraging both traditional and vector-based search techniques to deliver more precise and meaningful search results.

Success criteria

You successfully utilized vector embeddings to identify the most relevant products based on textual descriptions.
You applied filters for availability and sale price to refine the search results.
You combined traditional SQL queries with vector-based search capabilities to enhance the search results.

Key tasks

01: Use a SQL Query for a hybrid search

Expand this section to view the solution

The following SQL script demonstrates how to perform a hybrid search in a SQL database. It uses vector embeddings to find the most relevant products based on a textual description and combines with Available=True and Sale price of \<=50.

Enter the following in a new query:

/*-- Declare the search text*/
declare @search_text nvarchar(max) = 'help me plan a high school graduation party'
    
/*-- Declare a variable to hold the search vector*/
declare @search_vector vector(1536)
    
/*-- Generate the search vector using the 'create_embeddings' stored procedure*/
exec dbo.create_embeddings @search_text, @search_vector output;
SELECT TOP(10)
id, product_name, description, sale_price,
    
/*-- Calculate the cosine distance between the search vector and product description vectors*/
vector_distance('cosine', @search_vector, product_description_vector) AS distance
FROM [dbo].[walmart_product_details]
WHERE sale_price <= 50 -- Filter by sale price
AND available = 'TRUE' -- Filter by availability
ORDER BY distance; -- Order by the closest distance

Congratulations! You’ve successfully completed this task.