3_tabular_semantic_search_text_to_sql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Text-to-SQL solution on top of Amazon products\n",
    "\n",
    "In this notebook we will explore what it takes to implement a text-to-SQL solution on top of our Amazon dataset using LlamaIndex."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[32m2025-01-09 17:39:10.764\u001b[0m | \u001b[1mINFO    \u001b[0m | \u001b[36msuperlinked_app.config\u001b[0m:\u001b[36m<module>\u001b[0m:\u001b[36m9\u001b[0m - \u001b[1mLoading '.env' file from: /Users/pauliusztin/Documents/01_projects/hands-on-retrieval/.env\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "import openai\n",
    "import pandas as pd\n",
    "\n",
    "from superlinked_app.config import settings\n",
    "\n",
    "settings.validate_processed_dataset_exists()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "openai.api_key = settings.OPENAI_API_KEY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Database Schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import (\n",
    "    Column,\n",
    "    Float,\n",
    "    Integer,\n",
    "    MetaData,\n",
    "    String,\n",
    "    Table,\n",
    "    create_engine,\n",
    "    insert,\n",
    "    select,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(\"sqlite:///:memory:\")\n",
    "metadata_obj = MetaData()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create the product SQL table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "product_table_name = \"product\"\n",
    "product_table = Table(\n",
    "    product_table_name,\n",
    "    metadata_obj,\n",
    "    Column(\"asin\", String(255), primary_key=True),\n",
    "    Column(\"type\", String(255)),\n",
    "    Column(\"title\", String(255)),\n",
    "    Column(\"description\", String(1000)),\n",
    "    Column(\"review_rating\", Float),\n",
    "    Column(\"review_count\", Integer),\n",
    "    Column(\"price\", Float),\n",
    ")\n",
    "metadata_obj.create_all(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Define SQL Database\n",
    "\n",
    "We first define our SQLDatabase abstraction (a light wrapper around SQLAlchemy)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "from llama_index.core import SQLDatabase\n",
    "from llama_index.llms.openai import OpenAI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "llm = OpenAI(temperature=0.1, model=settings.OPENAI_MODEL_ID)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_database = SQLDatabase(engine, include_tables=[product_table_name])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add the data to the SQL database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>asin</th>\n",
       "      <th>type</th>\n",
       "      <th>title</th>\n",
       "      <th>description</th>\n",
       "      <th>price</th>\n",
       "      <th>review_rating</th>\n",
       "      <th>review_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B07WP4RXHY</td>\n",
       "      <td>product</td>\n",
       "      <td>YUEPIN U-Tube Clamp 304 Stainless Steel Hose P...</td>\n",
       "      <td>Product Description Specification: Material: 3...</td>\n",
       "      <td>9.99</td>\n",
       "      <td>4.7</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B07VRZTK2N</td>\n",
       "      <td>product</td>\n",
       "      <td>Apron for Women, Waterproof Adjustable Bib Coo...</td>\n",
       "      <td></td>\n",
       "      <td>11.99</td>\n",
       "      <td>4.0</td>\n",
       "      <td>152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B07V2F5SN1</td>\n",
       "      <td>product</td>\n",
       "      <td>DIY 5D Diamond Painting by Number Kit for Adul...</td>\n",
       "      <td>Product Description 5D DIY Diamond Painting is...</td>\n",
       "      <td>9.99</td>\n",
       "      <td>4.6</td>\n",
       "      <td>378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B00MNLQQ7K</td>\n",
       "      <td>product</td>\n",
       "      <td>Design Toscano QM2787100 Darby, the Forest Faw...</td>\n",
       "      <td></td>\n",
       "      <td>40.72</td>\n",
       "      <td>4.7</td>\n",
       "      <td>274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B089YD2KK5</td>\n",
       "      <td>product</td>\n",
       "      <td>Crocs Jibbitz 5-Pack Alien Shoe Charms | Jibbi...</td>\n",
       "      <td>From the brand Previous page Shop Crocs Collec...</td>\n",
       "      <td>9.99</td>\n",
       "      <td>4.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         asin     type                                              title  \\\n",
       "0  B07WP4RXHY  product  YUEPIN U-Tube Clamp 304 Stainless Steel Hose P...   \n",
       "1  B07VRZTK2N  product  Apron for Women, Waterproof Adjustable Bib Coo...   \n",
       "2  B07V2F5SN1  product  DIY 5D Diamond Painting by Number Kit for Adul...   \n",
       "3  B00MNLQQ7K  product  Design Toscano QM2787100 Darby, the Forest Faw...   \n",
       "4  B089YD2KK5  product  Crocs Jibbitz 5-Pack Alien Shoe Charms | Jibbi...   \n",
       "\n",
       "                                         description  price  review_rating  \\\n",
       "0  Product Description Specification: Material: 3...   9.99            4.7   \n",
       "1                                                     11.99            4.0   \n",
       "2  Product Description 5D DIY Diamond Painting is...   9.99            4.6   \n",
       "3                                                     40.72            4.7   \n",
       "4  From the brand Previous page Shop Crocs Collec...   9.99            4.7   \n",
       "\n",
       "   review_count  \n",
       "0            54  \n",
       "1           152  \n",
       "2           378  \n",
       "3           274  \n",
       "4             0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_json(settings.PROCESSED_DATASET_PATH, lines=True).drop(columns=['category'])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "for _, row in df.iterrows():\n",
    "    stmt = insert(product_table).values(**row.to_dict())\n",
    "    with engine.begin() as connection:\n",
    "        cursor = connection.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Query Database\n",
    "\n",
    "Let's query the database to see that everything worked fine."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ASIN: B07WP4RXHY\n",
      "Type: product\n",
      "Title: YUEPIN U-Tube Clamp 304 Stainless Steel Hose Pipe Cable Strap Clips With Rubber Cushioned (1-21/32\"(42mm)-10pcs)\n",
      "Description: Product Description Specification: Material: 304 Stainless Steel,100% New Rubber Color: Silver Shape...\n",
      "Rating: 4.7/5.0 (54 reviews)\n",
      "Price: $9.99\n",
      "--------------------------------------------------------------------------------\n",
      "ASIN: B07VRZTK2N\n",
      "Type: product\n",
      "Title: Apron for Women, Waterproof Adjustable Bib Cooking Aprons with Pocket-2 Side Coral Velvet Towels Stitched Durable Pinstripe Aprons for Dishwashing, Baking, Grill, Restaurant Even Garden Craft\n",
      "Description: ...\n",
      "Rating: 4.0/5.0 (152 reviews)\n",
      "Price: $11.99\n",
      "--------------------------------------------------------------------------------\n",
      "ASIN: B07V2F5SN1\n",
      "Type: product\n",
      "Title: DIY 5D Diamond Painting by Number Kit for Adult, Full Drill Crystal Rhinestone Embroidery Cross Stitch Diamond Embroidery Dotz Kit Home Wall Decor 15.8×11.8 Inch (Wolf)\n",
      "Description: Product Description 5D DIY Diamond Painting is the new popular product Can be used in the Bedroom, D...\n",
      "Rating: 4.6/5.0 (378 reviews)\n",
      "Price: $9.99\n",
      "--------------------------------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "stmt = select(\n",
    "    product_table.c.asin,\n",
    "    product_table.c.type,\n",
    "    product_table.c.title,\n",
    "    product_table.c.description,\n",
    "    product_table.c.review_rating,\n",
    "    product_table.c.review_count,\n",
    "    product_table.c.price,\n",
    ").select_from(product_table)\n",
    "\n",
    "with engine.connect() as connection:\n",
    "    results = connection.execute(stmt).fetchmany(3)\n",
    "    for result in results:\n",
    "        print(f\"ASIN: {result.asin}\")\n",
    "        print(f\"Type: {result.type}\")\n",
    "        print(f\"Title: {result.title}\")\n",
    "        print(f\"Description: {result.description[:100]}...\")  # Truncate long descriptions\n",
    "        print(f\"Rating: {result.review_rating:.1f}/5.0 ({result.review_count} reviews)\")\n",
    "        print(f\"Price: ${result.price:.2f}\")\n",
    "        print(\"-\" * 80)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Text-to-SQL Retriever\n",
    "\n",
    "Let's use the LlamaIndex SQL retriever to map text to SQL instructions and retrieve data from the SQLite table. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "from llama_index.core.retrievers import NLSQLRetriever\n",
    "from llama_index.core.response.notebook_utils import display_source_node\n",
    "\n",
    "nl_sql_retriever = NLSQLRetriever(\n",
    "    sql_database,\n",
    "    tables=[product_table_name],\n",
    "    return_raw=False,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All the content is in the `metadata`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def format_sql_results_to_df(results):\n",
    "    # Convert SQL results to dictionary format for DataFrame\n",
    "    data = []\n",
    "    for result in results:\n",
    "        data.append({\n",
    "            'type': result.metadata.get(\"type\"),\n",
    "            'title': result.metadata.get(\"title\"),\n",
    "            'price': result.metadata.get(\"price\"),\n",
    "            'review_rating': result.metadata.get(\"review_rating\")\n",
    "        })\n",
    "    \n",
    "    # Create DataFrame with specific columns\n",
    "    df = pd.DataFrame({\n",
    "        'type': [d['type'] for d in data],\n",
    "        'title': [d['title'] for d in data], \n",
    "        'price': [d['price'] for d in data],\n",
    "        'review_rating': [d['review_rating'] for d in data]\n",
    "    })\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Examples 1: Simple\n",
    "\n",
    "Let's start with a simple query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>title</th>\n",
       "      <th>price</th>\n",
       "      <th>review_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>product</td>\n",
       "      <td>Paper Clever Party Unicorn Diaper Raffle Ticke...</td>\n",
       "      <td>9.99</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>product</td>\n",
       "      <td>Goton Bling Case for Apple Watch Case Series 7...</td>\n",
       "      <td>13.99</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>product</td>\n",
       "      <td>Tender Leaf Toys - Hungry Wooden Tortoise Shap...</td>\n",
       "      <td>30.59</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      type                                              title  price  \\\n",
       "0  product  Paper Clever Party Unicorn Diaper Raffle Ticke...   9.99   \n",
       "1  product  Goton Bling Case for Apple Watch Case Series 7...  13.99   \n",
       "2  product  Tender Leaf Toys - Hungry Wooden Tortoise Shap...  30.59   \n",
       "\n",
       "   review_rating  \n",
       "0            5.0  \n",
       "1            5.0  \n",
       "2            5.0  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = nl_sql_retriever.retrieve(\n",
    "   \"books with a price lower than 100 and a rating bigger than 4 (also show the type)\"\n",
    ")\n",
    "format_sql_results_to_df(results[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Examples 2: Specific categories \n",
    "\n",
    "Now, we will complicate the query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>title</th>\n",
       "      <th>price</th>\n",
       "      <th>review_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [type, title, price, review_rating]\n",
       "Index: []"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = nl_sql_retriever.retrieve(\n",
    "   \"psychology and mindfulness with a rating bigger than 4\"\n",
    ")\n",
    "format_sql_results_to_df(results[:3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>title</th>\n",
       "      <th>price</th>\n",
       "      <th>review_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [type, title, price, review_rating]\n",
       "Index: []"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = nl_sql_retriever.retrieve(\n",
    "   \"Return the top items (along with their price) with the highest reviews rating on science\"\n",
    ")\n",
    "format_sql_results_to_df(results[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example 3: Titles or keywords\n",
    "\n",
    "Let's make it even more complex:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>title</th>\n",
       "      <th>price</th>\n",
       "      <th>review_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>Funko POP! Movies: Lord of The Rings - Lurtz C...</td>\n",
       "      <td>42.99</td>\n",
       "      <td>4.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   type                                              title  price  \\\n",
       "0  None  Funko POP! Movies: Lord of The Rings - Lurtz C...  42.99   \n",
       "\n",
       "   review_rating  \n",
       "0            4.8  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = nl_sql_retriever.retrieve(\n",
    "   \"Lord of the Rings\"\n",
    ")\n",
    "format_sql_results_to_df(results[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### More examples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "**Node ID:** 7f938eec-4fb4-4dc8-a4de-9bf55e64cab9<br>**Similarity:** None<br>**Text:** <br>**Metadata:** {'title': 'Choose: An Invitation to the Best Day Ever Adventure', 'price': 15.0, 'review_rating': 5.0}<br>"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "**Node ID:** c37f7809-d953-4df7-af56-bde09298ea3e<br>**Similarity:** None<br>**Text:** <br>**Metadata:** {'title': '33 Days to Morning Glory: A Do-It-Yourself Retreat In Preparation for Marian Consecration', 'price': 13.49, 'review_rating': 4.9}<br>"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "**Node ID:** c73007e5-400a-4666-b40c-7322fb799679<br>**Similarity:** None<br>**Text:** <br>**Metadata:** {'title': 'Mom Set Free - Bible Study Book: Good News for Moms Who are Tired of Trying to be Good Enough', 'price': 15.99, 'review_rating': 4.8}<br>"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "results = nl_sql_retriever.retrieve(\n",
    "   \"Return the top books (along with their price and rating) with the highest reviews rating and lowest price.\"\n",
    ")\n",
    "for n in results[:3]:\n",
    "    display_source_node(n, show_source_metadata=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "**Node ID:** d0be1de8-5fba-442f-8613-3c53385c5835<br>**Similarity:** None<br>**Text:** <br>**Metadata:** {'title': 'TRUE CABLE Cat6 Riser (CMR), 1000ft, White, 23AWG 4 Pair Solid Bare Copper, 550MHz, ETL Listed, Unshielded Twisted Pair (UTP), Bulk Ethernet Cable', 'price': 195.99, 'review_rating': 4.8}<br>"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "**Node ID:** cad05da4-e87e-4273-a78b-e7501619b40e<br>**Similarity:** None<br>**Text:** <br>**Metadata:** {'title': 'PneumaticPlus SAU400A-N04G - Dual Air System 1/2\" NPT - Air Filter, Regulator & Lubricator w/Additional Free Air Port 3/8\" NPT - Manual Drain, Poly Bowl, Pressure Gauge', 'price': 159.99, 'review_rating': 4.8}<br>"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "**Node ID:** 6b9a99e4-a321-4125-afcd-9e9188b6fe2c<br>**Similarity:** None<br>**Text:** <br>**Metadata:** {'title': 'MAYASAF Catalytic Converter w/Gasket [1.8L Models Only] for Toyota Corolla/Matrix, Pontiac Vibe 2003 04 05 06 07 08 (EPA Compliant)', 'price': 129.99, 'review_rating': 3.9}<br>"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "results = nl_sql_retriever.retrieve(\n",
    "   \"Return the top products (along with their price and review) about cats or dogs with a great price and review\"\n",
    ")\n",
    "for n in results[:3]:\n",
    "    display_source_node(n, show_source_metadata=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = nl_sql_retriever.retrieve(\n",
    "   \"I'm looking for a computer or laptop with a price bigger than 100 and a review bigger than 4\"\n",
    ")\n",
    "for n in results[:3]:\n",
    "    display_source_node(n, show_source_metadata=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Plug in the RetrieverQueryEngine\n",
    "\n",
    "We compose our SQL Retriever with our standard RetrieverQueryEngine to synthesize a response. The result is similar to LlamaIndex's `NLSQLTableQueryEngine` class, which does both retrieval and synthetization of the response."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "from llama_index.core.query_engine import RetrieverQueryEngine\n",
    "\n",
    "query_engine = RetrieverQueryEngine.from_args(nl_sql_retriever)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "response = query_engine.query(\n",
    "    \"Return the top 5 books (along with their review count and price) with the highest reviews rating.\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mom Set Free - Bible Study Book: Good News for Moms Who are Tired of Trying to be Good Enough - 781 reviews, $15.99  \n",
      "Millennial Kosher: recipes reinvented for the modern palate - 466 reviews, $36.99  \n",
      "Choose: An Invitation to the Best Day Ever Adventure - 62 reviews, $15.0  \n",
      "33 Days to Morning Glory: A Do-It-Yourself Retreat In Preparation for Marian Consecration - 0 reviews, $13.49  \n",
      "Narcotics Anonymous Step Working Guides - 0 reviews, $17.15\n"
     ]
    }
   ],
   "source": [
    "print(str(response))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Next Steps\n",
    "\n",
    "Go to the **INSTALL_AND_USAGE** document to learn how to start the MongoDB vector index, Superlinked RESTful API and Streamlit frontend."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}