A knowledge base is an advanced system that organizes information based on semantic meaning rather than simple keyword matching. It integrates embedding models, reranking models, and vector stores to enable context-aware data retrieval.

By performing semantic reasoning across multiple data points, a knowledge base delivers deeper insights and more accurate responses, making it a powerful tool for intelligent data access.

​
How Knowledge Bases Work

Before diving into the syntax, here is a quick walkthrough showing how knowledge bases work in MindsDB.

We start by creating a knowledge base and inserting data. Next we can run semantic search queries with metadata filtering.

1

Create a knowledge base

Use the CREATE KNOWLEDGE_BASE command to create a knowledge base, specifying all its components.

CREATE KNOWLEDGE_BASE my_kb
USING
    embedding_model = {
        "provider": "openai",
        "model_name" : "text-embedding-3-large",
        "api_key": "sk-abc123"
    },
    reranking_model = {
        "provider": "openai",
        "model_name": "gpt-4o",
        "api_key": "sk-abc123"
    },
    metadata_columns = ['product'],
    content_columns = ['notes'],
    id_column = 'order_id';
2

Insert data into the knowledge base

In this example, we use a simple dataset containing customer notes for product orders which will be inserted into the knowledge base.

+----------+-----------------------+------------------------+
| order_id | product               | notes                  |
+----------+-----------------------+------------------------+
| A1B      | Wireless Mouse        | Request color: black   |
| 3XZ      | Bluetooth Speaker     | Gift wrap requested    |
| Q7P      | Aluminum Laptop Stand | Prefer aluminum finish |
+----------+-----------------------+------------------------+

Use the INSERT INTO command to injest data into the knowledge base.

INSERT INTO my_kb
    SELECT order_id, product, notes
    FROM sample_data.orders;
3

Run semantic search on the knowledge base

Query the knowledge base using semantic search.

SELECT *
FROM my_kb
WHERE content = 'color'

This query returns:

+-----+----------------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+
| id  | chunk_id             | chunk_content           | metadata                                                                                                                                                                                            | distance           | relevance          |
+-----+----------------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+
| A1B | A1B_notes:1of1:0to20 | Request color: black    | {"chunk_index":0,"content_column":"notes","end_char":20,"original_doc_id":"A1B_notes","original_row_id":"A1B","product":"Wireless Mouse","source":"TextChunkingPreprocessor","start_char":0}        | 0.5743341242061104 | 0.5093188026135379 |
| Q7P | Q7P_notes:1of1:0to22 | Prefer aluminum finish  | {"chunk_index":0,"content_column":"notes","end_char":22,"original_doc_id":"Q7P_notes","original_row_id":"Q7P","product":"Aluminum Laptop Stand","source":"TextChunkingPreprocessor","start_char":0} | 0.7744703514692067 | 0.2502580835880018 |
| 3XZ | 3XZ_notes:1of1:0to19 | Gift wrap requested     | {"chunk_index":0,"content_column":"notes","end_char":19,"original_doc_id":"3XZ_notes","original_row_id":"3XZ","product":"Bluetooth Speaker","source":"TextChunkingPreprocessor","start_char":0}     | 0.8010851611432231 | 0.2500003885558766 |
+-----+----------------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+
4

Get the most relevant search results

Query the knowledge base using semantic search and define the relevance_threshold parameter to receive only the best matching data for your use case.

SELECT *
FROM my_kb
WHERE content = 'color'
AND relevance_threshold = 0.2502;

This query returns:

+-----+----------------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+
| id  | chunk_id             | chunk_content           | metadata                                                                                                                                                                                            | distance           | relevance          |
+-----+----------------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+
| A1B | A1B_notes:1of1:0to20 | Request color: black    | {"chunk_index":0,"content_column":"notes","end_char":20,"original_doc_id":"A1B_notes","original_row_id":"A1B","product":"Wireless Mouse","source":"TextChunkingPreprocessor","start_char":0}        | 0.5743341242061104 | 0.5093188026135379 |
| Q7P | Q7P_notes:1of1:0to22 | Prefer aluminum finish  | {"chunk_index":0,"content_column":"notes","end_char":22,"original_doc_id":"Q7P_notes","original_row_id":"Q7P","product":"Aluminum Laptop Stand","source":"TextChunkingPreprocessor","start_char":0} | 0.7744703514692067 | 0.2502580835880018 |
+-----+----------------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+
5

Filter results by metadata

Add metadata filtering to focus your search.

SELECT *
FROM my_kb
WHERE product = 'Wireless Mouse'
AND content = 'color'
AND relevance_threshold = 0.2502;

This query returns:

+-----+----------------------+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-------------------+
| id  | chunk_id             | chunk_content          | metadata                                                                                                                                                                                     | distance           | relevance         |
+-----+----------------------+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-------------------+
| A1B | A1B_notes:1of1:0to20 | Request color: black   | {"chunk_index":0,"content_column":"notes","end_char":20,"original_doc_id":"A1B_notes","original_row_id":"A1B","product":"Wireless Mouse","source":"TextChunkingPreprocessor","start_char":0} | 0.5743341242061104 | 0.504396172197583 |
+-----+----------------------+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-------------------+

The following sections explain the syntax and other features of knowledge bases.

​
CREATE KNOWLEDGE_BASE Syntax

Here is the syntax for creating a knowledge base:

CREATE KNOWLEDGE_BASE my_kb
USING
    embedding_model = {
        "provider": "openai",
        "model_name" : "text-embedding-3-large",
        "api_key": "sk-..."
    },
    reranking_model = {
        "provider": "openai",
        "model_name": "gpt-4o",
        "api_key": "sk-..."
    },
    storage = my_vector_store.storage_table,
    metadata_columns = ['date', 'creator', ...],
    content_columns = ['review', 'content', ...],
    id_column = 'id';

Upon execution, it registers my_kb and associates the specified models and storage.

my_kb is a unique identifier of the knowledge base within MindsDB.

As MindsDB stores objects, such as models or knowledge bases, inside projects, you can create a knowledge base inside a custom project.

CREATE PROJECT my_project;

CREATE KNOWLEDGE_BASE my_project.my_kb
USING
    ...

​
embedding_model

The embedding model is a required component of the knowledge base. It stores specifications of the embedding model to be used.

Users can define the embedding model choosing one of the following options.

Option 1. Use the embedding_model parameter to define the specification.

CREATE KNOWLEDGE_BASE my_kb
USING
    ...
    embedding_model = {

        "provider": "openai_azure",
        "model_name" : "text-embedding-3-large",
        "api_key": "sk-abc123",
        "base_url": "https://ai-6689.openai.azure.com/",
        "api_version": "2024-02-01"

    },
    ...

Option 2. Define the default embedding model in the MindsDB configuration file.

Note that if you define default_embedding_model in the configuration file, you do not need to provide the embedding_model parameter when creating a knowledge base. If provide both, then the values from the embedding_model parameter are used.

"default_embedding_model": {

   "provider": "openai_azure",
   "model_name" : "text-embedding-3-large",
   "api_key": "sk-abc123",
   "base_url": "https://ai-6689.openai.azure.com/",
   "api_version": "2024-02-01"

}

The embedding model specification includes:

  • provider It is a required parameter. It defines the model provider. Currently, the supported providers include OpenAI (openai) and OpenAI via Azure (azure_openai).

  • model_name It is a required parameter. It defines the embedding model name as specified by the provider. Users can choose one of the OpenAI embedding models.

  • api_key The API key is required to access the embedding model assigned to a knowledge base. Users can provide it either in this api_key parameter, or in the OPENAI_API_KEY environment variable for "provider": "openai" and AZURE_OPENAI_API_KEY environment variable for "provider": "azure_openai".

  • base_url It is an optional parameter, which defaults to https://api.openai.com/v1/. It is a required parameter when using the azure_openai provider. It is the root URL used to send API requests.

  • api_version It is an optional parameter. It is a required parameter when using the azure_openai provider. It defines the API version.

​
reranking_model

The reranking model is an optional component of the knowledge base. It stores specifications of the reranking model to be used.

Users can define the reranking model choosing one of the following options.

Option 1. Use the reranking_model parameter to define the specification.

CREATE KNOWLEDGE_BASE my_kb
USING
    ...
    reranking_model = {

        "provider": "openai_azure",
        "model_name" : "gpt-4o",
        "api_key": "sk-abc123",
        "base_url": "https://ai-6689.openai.azure.com/",
        "api_version": "2024-02-01",
        "method": "multi-class"

    },
    ...

Option 2. Define the default reranking model in the MindsDB configuration file.

Note that if you define default_llm in the configuration file, you do not need to provide the reranking_model parameter when creating a knowledge base. If provide both, then the values from the reranking_model parameter are used.

"default_llm": {

  "provider": "openai_azure",
  "model_name" : "gpt-4o",
  "api_key": "sk-abc123",
  "base_url": "https://ai-6689.openai.azure.com/",
  "api_version": "2024-02-01",
  "method": "multi-class"

}

The reranking model specification includes:

  • provider It is a required parameter. It defines the model provider. Currently, the supported providers include OpenAI (openai) and OpenAI via Azure (azure_openai).

  • model_name It is a required parameter. It defines the embedding model name as specified by the provider. Users can choose one of the OpenAI chat models.

  • api_key The API key is required to access the embedding model assigned to a knowledge base. Users can provide it either in this api_key parameter, or in the OPENAI_API_KEY environment variable for "provider": "openai" and AZURE_OPENAI_API_KEY environment variable for "provider": "azure_openai".

  • base_url It is an optional parameter, which defaults to https://api.openai.com/v1/. It is a required parameter when using the azure_openai provider. It is the root URL used to send API requests.

  • api_version It is an optional parameter. It is a required parameter when using the azure_openai provider. It defines the API version.

  • method It is an optional parameter. It defines the method used to calculate the relevance of the output rows. The available options include multi-class and binary. It defaults to multi-class.

Reranking Method

The multi-class reranking method classifies each document chunk (that meets any specified metadata filtering conditions) into one of four relevance classes:

  1. Not relevant with class weight of 0.25.
  2. Slightly relevant with class weight of 0.5.
  3. Moderately relevant with class weight of 0.75.
  4. Highly relevant with class weight of 1.

The overall relevance_score of a document is calculated as the sum of each chunk’s class weight multiplied by its class probability (from model logprob output).

The binary reranking method simplifies classification by determining whether a document is relevant or not, without intermediate relevance levels. With this method, the overall relevance_score of a document is calculated based on the model log probability.

​
storage

The vector store is a required component of the knowledge base. It stores data in the form of embeddings.

It is optional for users to provide the storage parameter. If not provided, the default ChromaDB is created when creating a knowledge base.

In order to provide the storage vector database, it is required to connect it to MindsDB beforehand.

Here is an example for PGVector.

CREATE DATABASE my_pgvector
WITH ENGINE = 'pgvector',
PARAMETERS = {
    "host": "127.0.0.1",
    "port": 5432,
    "database": "postgres",
    "user": "user",
    "password": "password",
    "distance": "cosine"
};

CREATE KNOWLEDGE_BASE my_kb
USING
    ...
    storage = my_pgvector.storage_table,
    ...

Note that you do not need to have the storage_table created as it is created when creating a knowledge base.

The available options include either PGVector or ChromaDB.

If the storage parameter is not provided, the system creates the default ChromaDB vector database called <kb_name>_chromadb with the default table called default_collection that stores the embedded data.

This default ChromaDB vector database is stored in MindsDB’s storage. In order to define a different storage directory, create your own ChromaDB referring to this documentation page.

​
metadata_columns

The data inserted into the knowledge base can be classified as metadata, which enables users to filter the search results using defined data fields.

Note that source data column(s) included in metadata_columns cannot be used in content_columns, and vice versa.

This parameter is an array of strings that lists column names from the source data to be used as metadata. If not provided, the metadata is not used.

Here is an example of usage. A user wants to store the following data in a knowledge base.

+----------+-------------------+------------------------+
| order_id | product           | notes                  |
+----------+-------------------+------------------------+
| A1B      | Wireless Mouse    | Request color: black   |
| 3XZ      | Bluetooth Speaker | Gift wrap requested    |
| Q7P      | Laptop Stand      | Prefer aluminum finish |
+----------+-------------------+------------------------+

Go to the Complete Example section below to find out how to access this sample data.

The product column can be used as metadata to enable metadata filtering.

CREATE KNOWLEDGE_BASE my_kb
USING
    ...
    metadata_columns = ['product'],
    ...

​
content_columns

The data inserted into the knowledge base can be classified as content, which is embedded by the embedding model and stored in the underlying vector store.

Note that source data column(s) included in content_columns cannot be used in metadata_columns, and vice versa.

This parameter is an array of strings that lists column names from the source data to be used as content and processed into embeddings. If not provided, the content column is expected by default when inserting data into the knowledge base.

Here is an example of usage. A user wants to store the following data in a knowledge base.

+----------+-------------------+------------------------+
| order_id | product           | notes                  |
+----------+-------------------+------------------------+
| A1B      | Wireless Mouse    | Request color: black   |
| 3XZ      | Bluetooth Speaker | Gift wrap requested    |
| Q7P      | Laptop Stand      | Prefer aluminum finish |
+----------+-------------------+------------------------+

Go to the Complete Example section below to find out how to access this sample data.

The notes column can be used as content.

CREATE KNOWLEDGE_BASE my_kb
USING
    ...
    content_columns = ['notes'],
    ...

​
id_column

The ID column uniquely identifies each source data row in the knowledge base.

It is an optional parameter. If provided, this parameter is a string that contains the source data ID column name. If not provided, it is generated from the hash of the content columns.

Here is an example of usage. A user wants to store the following data in a knowledge base.

+----------+-------------------+------------------------+
| order_id | product           | notes                  |
+----------+-------------------+------------------------+
| A1B      | Wireless Mouse    | Request color: black   |
| 3XZ      | Bluetooth Speaker | Gift wrap requested    |
| Q7P      | Laptop Stand      | Prefer aluminum finish |
+----------+-------------------+------------------------+

Go to the Complete Example section below to find out how to access this sample data.

The order_id column can be used as ID.

CREATE KNOWLEDGE_BASE my_kb
USING
    ...
    id_column = 'order_id',
    ...

Note that if the source data row is chunked into multiple chunks by the knowledge base (that is, to optimize the storage), then these rows in the knowledge base have the same ID value that identifies chunks from one source data row.

Available options for the ID column values

  • User-Defined ID Column:
    When users defined the id_column parameter, the values from the provided source data column are used to identify source data rows within the knowledge base.

  • User-Generated ID Column:
    When users do not have a column that uniquely idetifies each row in their source data, they can generate the ID column values when inserting data into the knowledge base using functions like HASH() or ROW_NUMBER().

INSERT INTO my_kb (
    SELECT ROW_NUMBER() OVER (ORDER BY order_id) AS id, *
    FROM sample_data.orders
);
  • Default ID Column:
    If the id_column parameter is not defined, its default values are build from the hash of the content columns and follow the format: <first 16 char of md5 hash of row content>.

​
Example

Here is a sample knowledge base that will be used for examples in the following content.

CREATE KNOWLEDGE_BASE my_kb
USING
    embedding_model = {
        "provider": "openai",
        "model_name" : "text-embedding-3-large",
        "api_key": "sk-abc123"
    },
    reranking_model = {
        "provider": "openai",
        "model_name": "gpt-4o",
        "api_key": "sk-abc123"
    },
    metadata_columns = ['product'],
    content_columns = ['notes'],
    id_column = 'order_id';

​
DESCRIBE KNOWLEDGE_BASE Syntax

Users can get details about the knowledge base using the DESCRIBE KNOWLEDGE_BASE command.

DESCRIBE KNOWLEDGE_BASE my_kb;

Here is the sample output:

+---------+---------+--------+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+--------------------+----------------+-------+----------+
| NAME    | PROJECT | MODEL  | STORAGE                                | PARAMS                                                                                                                                                                                                                                       | INSERT_STARTED_AT | INSERT_FINISHED_AT | PROCESSED_ROWS | ERROR | QUERY_ID |
+---------+---------+--------+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+--------------------+----------------+-------+----------+
| my_kb   | mindsdb | [NULL] | my_kb_chromadb.default_collection      | {"embedding_model": {"provider": "openai", "model_name": "text-embedding-ada-002", "api_key": "sk-xxx"}, "reranking_model": {"provider": "openai", "model_name": "gpt-4o", "api_key": "sk-xxx"}, "default_vector_storage": "my_kb_chromadb"} | [NULL]            | [NULL]             | [NULL]         | [NULL]| [NULL]   |
+---------+---------+--------+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+--------------------+----------------+-------+----------+

​
INSERT INTO Syntax

Here is the syntax for inserting data into a knowledge base:

INSERT INTO my_kb
SELECT order_id, product, notes
FROM sample_data.orders;

Upon execution, it inserts data into a knowledge base, using the embedding model to embed it into vectors before inserting into an underlying vector database.

Note that when inserting the same data into the knowledge base again, then no new rows are inserted, as the content already exists in the knowledge base.

​
Update Existing Data

In order to update existing data in the knowledge base, insert data with the column ID that you want to update and the updated content.

Here is an example of usage. A knowledge base stores the following data.

+----------+-------------------+------------------------+
| order_id | product           | notes                  |
+----------+-------------------+------------------------+
| A1B      | Wireless Mouse    | Request color: black   |
| 3XZ      | Bluetooth Speaker | Gift wrap requested    |
| Q7P      | Laptop Stand      | Prefer aluminum finish |
+----------+-------------------+------------------------+

A user updated Laptop Stand to Aluminum Laptop Stand.

+----------+-----------------------+------------------------+
| order_id | product               | notes                  |
+----------+-----------------------+------------------------+
| A1B      | Wireless Mouse        | Request color: black   |
| 3XZ      | Bluetooth Speaker     | Gift wrap requested    |
| Q7P      | Aluminum Laptop Stand | Prefer aluminum finish |
+----------+-----------------------+------------------------+

Go to the Complete Example section below to find out how to access this sample data.

Here is how to propagate this change into the knowledge base.

INSERT INTO my_kb
SELECT order_id, product, notes
FROM sample_data.orders
WHERE order_id = 'Q7P';

The knowledge base matches the ID value to the existing one and updates the data if required.

​
Optimize Data Insertion

In order to optimize the performance of data insertion into the knowledge base, users can set up partitions and threads to insert batches of data in parallel. This also enables tracking the progress of data insertion process including cancelling and resuming it if required.

Here is an example.

INSERT INTO my_kb
SELECT order_id, product, notes
FROM sample_data.orders
USING
    batch_size = 200,
    track_column = order_id,
    threads = 10,
    error = 'skip';

The parameters include the following:

  • batch_size defines the number of rows fetched per iteration to optimize data extraction from the source. It defaults to 1000.

  • threads defines threads for running partitions. Note that if the ML task queue is enabled, threads are used automatically. The available values for include a number of threads to be used, for example, threads = 10, or a boolean value that defines whether to enable threads, setting threads = true, or disable threads, setting threads = false.

  • track_column defines the column used for sorting data before partitioning.

  • error defines the error processing options. The available values include raise, used to raise errors as they come, or skip, used to subside errors. It defaults to raise if not provided.

After executing the INSERT INTO statement with the above parameters, users can view the data insertion progress by querying the information_schema.queries table.

SELECT * FROM information_schema.queries;

Users can cancel the data insertion process using the process ID from the information_schema.queries table.

SELECT query_cancel(1);

Note that canceling the query will not remove the already inserted data.

Users can resume the data insertion process using the process ID from the information_schema.queries table.

SELECT query_resume(1);

​
Chunking Data

Upon inserting data into the knowledge base, the data chunking is performed in order to optimize the storage and search of data.

Each chunk is identified by its chunk ID of the following format: <id>:<chunk_number>of<total_chunks>:<start_char_number>to<end_char_number>.

​
Underlying Vector Store

Each knowledge base has its underlying vector store that stores data inserted into the knowledge base in the form of embeddings.

Users can query the underlying vector store as follows.

  • KB with the default ChromaDB vector store:
SELECT id, content, metadata, embeddings
FROM <kb_name>_chromadb.storage_table;
SELECT id, content, metadata, embeddings
FROM <vector_store_connection_name>.<table_name>;

​
Example

Here a sample knowledge base created in the previous Example section is inserted into.

INSERT INTO my_kb
SELECT order_id, product, notes
FROM sample_data.orders;

When inserting into a knowledge base where the content_columns parameter was not specified, the column storing content must be aliased AS content as below.

CREATE KNOWLEDGE_BASE my_kb
USING
    ...
    id_column = 'order_id',
    ...
INSERT INTO my_kb
SELECT order_id, notes AS content
FROM sample_data.orders;

​
SELECT FROM KB Syntax

Knowledge bases provide an abstraction that enables users to see the stored data.

Note that here a sample knowledge base created and inserted into in the previous Example sections is searched.

SELECT *
FROM my_kb;

Here is the sample output:

+-----+----------------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+
| id  | chunk_id             | chunk_content           | metadata                                                                                                                                                                                            | distance           | relevance          |
+-----+----------------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+
| A1B | A1B_notes:1of1:0to20 | Request color: black    | {"chunk_index":0,"content_column":"notes","end_char":20,"original_doc_id":"A1B_notes","original_row_id":"A1B","product":"Wireless Mouse","source":"TextChunkingPreprocessor","start_char":0}        | 0.5743341242061104 | 0.5093188026135379 |
| Q7P | Q7P_notes:1of1:0to22 | Prefer aluminum finish  | {"chunk_index":0,"content_column":"notes","end_char":22,"original_doc_id":"Q7P_notes","original_row_id":"Q7P","product":"Aluminum Laptop Stand","source":"TextChunkingPreprocessor","start_char":0} | 0.7744703514692067 | 0.2502580835880018 |
| 3XZ | 3XZ_notes:1of1:0to19 | Gift wrap requested     | {"chunk_index":0,"content_column":"notes","end_char":19,"original_doc_id":"3XZ_notes","original_row_id":"3XZ","product":"Bluetooth Speaker","source":"TextChunkingPreprocessor","start_char":0}     | 0.8010851611432231 | 0.2500003885558766 |
+-----+----------------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+

​
Data Stored in Knowledge Base

The following columns are stored in the knowledge base.

id It stores values from the column defined in the id_column parameter when creating the knowledge base. These are the source data IDs.

chunk_id Knowledge bases chunk the inserted data in order to fit the defined chunk size. If the chunking is performed, the following chunk ID format is used: <id>:<chunk_number>of<total_chunks>:<start_char_number>to<end_char_number>.

chunk_content It stores values from the column(s) defined in the content_columns parameter when creating the knowledge base.

metadata It stores the general metadata and the metadata defined in the metadata_columns parameter when creating the knowledge base.

distance It stores the calculated distance between the chunk’s content and the search phrase.

relevance It stores the calculated relevance of the chunk as compared to the search phrase.

Note that the calculation method of relevance_threshold differs as follows:

  • When the ranking model is provided, the default relevance_threshold is 0, unless defined otherwise in the WHERE clause.
  • When the reranking model is not provided and the relevance_threshold is not defined in the query, then no relevance filtering is applied and the output includes all rows matched based on the similarity and metadata search.
  • When the reranking model is not provided but the relevance_threshold is defined in the query, then the relevance is calculated based on the distance column (1/(1+ distance)) and the relevance_threshold value is compared with this relevance value to filter the output.

Users can query a knowledge base using semantic search by providing the search phrase (called content) to be searched for.

SELECT *
FROM my_kb
WHERE content = 'color'

Here is the output:

+-----+----------------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+
| id  | chunk_id             | chunk_content           | metadata                                                                                                                                                                                            | distance           | relevance          |
+-----+----------------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+
| A1B | A1B_notes:1of1:0to20 | Request color: black    | {"chunk_index":0,"content_column":"notes","end_char":20,"original_doc_id":"A1B_notes","original_row_id":"A1B","product":"Wireless Mouse","source":"TextChunkingPreprocessor","start_char":0}        | 0.5743341242061104 | 0.5093188026135379 |
| Q7P | Q7P_notes:1of1:0to22 | Prefer aluminum finish  | {"chunk_index":0,"content_column":"notes","end_char":22,"original_doc_id":"Q7P_notes","original_row_id":"Q7P","product":"Aluminum Laptop Stand","source":"TextChunkingPreprocessor","start_char":0} | 0.7744703514692067 | 0.2502580835880018 |
| 3XZ | 3XZ_notes:1of1:0to19 | Gift wrap requested     | {"chunk_index":0,"content_column":"notes","end_char":19,"original_doc_id":"3XZ_notes","original_row_id":"3XZ","product":"Bluetooth Speaker","source":"TextChunkingPreprocessor","start_char":0}     | 0.8010851611432231 | 0.2500003885558766 |
+-----+----------------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+

Notice that the output is in this case the same as the one without specifying content in the WHERE clause. This is caused by the default relevance_threshold set to 0, which is its lowest value.

Users can limit the relevance_threshold in order to get only the most relevant results.

SELECT *
FROM my_kb
WHERE content = 'color'
AND relevance_threshold = 0.5;

Here is the output:

+-----+----------------------+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+
| id  | chunk_id             | chunk_content          | metadata                                                                                                                                                                                     | distance           | relevance          |
+-----+----------------------+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+
| A1B | A1B_notes:1of1:0to20 | Request color: black   | {"chunk_index":0,"content_column":"notes","end_char":20,"original_doc_id":"A1B_notes","original_row_id":"A1B","product":"Wireless Mouse","source":"TextChunkingPreprocessor","start_char":0} | 0.5743341242061104 | 0.5103766499957533 |
+-----+----------------------+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+

By providing the relevance_threshold filter, the output is limited to only data with relevance score of the provided value or higher. The available value of relevance_threshold are between 0 and 1, and its default value is 0.

Users can limit the number of rows returned.

SELECT *
FROM my_kb
WHERE content = 'color'
LIMIT 2;

Here is the output:

+-----+----------------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+
| id  | chunk_id             | chunk_content           | metadata                                                                                                                                                                                            | distance           | relevance          |
+-----+----------------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+
| A1B | A1B_notes:1of1:0to20 | Request color: black    | {"chunk_index":0,"content_column":"notes","end_char":20,"original_doc_id":"A1B_notes","original_row_id":"A1B","product":"Wireless Mouse","source":"TextChunkingPreprocessor","start_char":0}        | 0.5743341242061104 | 0.5093188026135379 |
| Q7P | Q7P_notes:1of1:0to22 | Prefer aluminum finish  | {"chunk_index":0,"content_column":"notes","end_char":22,"original_doc_id":"Q7P_notes","original_row_id":"Q7P","product":"Aluminum Laptop Stand","source":"TextChunkingPreprocessor","start_char":0} | 0.7744703514692067 | 0.2502580835880018 |
+-----+----------------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+

​
Metadata Filtering

Besides semantic search features, knowledge bases enable users to filter the result set by the defined metadata.

SELECT *
FROM my_kb
WHERE product = 'Wireless Mouse';

Here is the output:

+-----+----------------------+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+
| id  | chunk_id             | chunk_content          | metadata                                                                                                                                                                                     | relevance | distance |
+-----+----------------------+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+
| A1B | A1B_notes:1of1:0to20 | Request color: black   | {"chunk_index":0,"content_column":"notes","end_char":20,"original_doc_id":"A1B_notes","original_row_id":"A1B","product":"Wireless Mouse","source":"TextChunkingPreprocessor","start_char":0} | [NULL]    | [NULL]   |
+-----+----------------------+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+

Note that when searching by metadata alone, the relevance column values are not calculated.

Users can do both, filter by metadata and search by content.

SELECT *
FROM my_kb
WHERE product = 'Wireless Mouse'
AND content = 'color'
AND relevance_threshold = 0.5;

Here is the output:

+-----+----------------------+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-------------------+
| id  | chunk_id             | chunk_content          | metadata                                                                                                                                                                                     | distance           | relevance         |
+-----+----------------------+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-------------------+
| A1B | A1B_notes:1of1:0to20 | Request color: black   | {"chunk_index":0,"content_column":"notes","end_char":20,"original_doc_id":"A1B_notes","original_row_id":"A1B","product":"Wireless Mouse","source":"TextChunkingPreprocessor","start_char":0} | 0.5743341242061104 | 0.504396172197583 |
+-----+----------------------+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-------------------+

​
JOIN Syntax

Knowledge bases can be used in the standard SQL JOIN statements.

SELECT t.order_id, t.product, t.notes, kb.chunk_content, kb.relevance
FROM local_postgres.orders AS t
JOIN my_kb AS kb
ON t.order_id = kb.id
WHERE t.order_id = 'A1B'
AND kb.content = 'color'
AND kb.product = 'Wireless Mouse';

Here is the output:

+----------+------------------+------------------------+------------------------+--------------------+
| order_id | product          | notes                  | chunk_content          | relevance          |
+----------+------------------+------------------------+------------------------+--------------------+
| A1B      | Wireless Mouse   | Request color: black   | Request color: black   | 0.5106591666649376 |
+----------+------------------+------------------------+------------------------+--------------------+

​
DELETE FROM Syntax

Here is the syntax for deleting from a knowledge base:

DELETE FROM my_kb
WHERE id = 'A1B';

Upon execution, it identifies matching records based on the user-defined condition and removes all associated data (metadata, content, chunks, embeddings) for matching records from the KB’s storage.

​
DROP KNOWLEDGE_BASE Syntax

Here is the syntax for deleting a knowledge base:

DROP KNOWLEDGE_BASE my_kb;

Upon execution, it removes the knowledge base with its content.

​
Example

Here is the data that will be inserted into the knowledge base.

+----------+-------------------+------------------------+
| order_id | product           | notes                  |
+----------+-------------------+------------------------+
| A1B      | Wireless Mouse    | Request color: black   |
| 3XZ      | Bluetooth Speaker | Gift wrap requested    |
| Q7P      | Laptop Stand      | Prefer aluminum finish |
+----------+-------------------+------------------------+

You can access this sample data as below:

CREATE DATABASE sample_data
WITH ENGINE = 'postgres',
PARAMETERS = {
    "user": "demo_user",
    "password": "demo_password",
    "host": "samples.mindsdb.com",
    "port": "5432",
    "database": "demo",
    "schema": "demo_data"
};

SELECT * FROM sample_data.orders;

Here is how to create a knowledge base specifically for the data.

CREATE KNOWLEDGE_BASE my_kb
USING
    embedding_model = {
        "provider": "openai",
        "model_name" : "text-embedding-3-large",
        "api_key": "sk-abc123"
    },
    reranking_model = {
        "provider": "openai",
        "model_name": "gpt-4o",
        "api_key": "sk-abc123"
    },
    metadata_columns = ['product'],
    content_columns = ['notes'],
    id_column = 'order_id';

Here is how to insert the data.

INSERT INTO my_kb
SELECT order_id, product, notes
FROM sample_data.orders;

Here is how to query the knowledge base.

SELECT *
FROM my_kb
WHERE product = 'Wireless Mouse'
AND content = 'color'
AND relevance_threshold = 0.5;