PgVector >> Return all the document when we query with ID within JSON document

[anjay-gupta]

I’m exploring ways to enhance the search functionality in a RAG (Retrieval-Augmented Generation) application, specifically to improve its ability to answer product-related questions.

We are working with a large JSON object that contains various properties, including nested objects and multiple IDs such as product ID, product status, shipping address, etc. However, when searching with a specific ID, the search returns all documents except the product ID I requested.

Could you advise on how to proceed in a scenario where we have JSON objects as metadata, and we are using embeddings for the entire JSON object while performing a similarity search?

`// DDL for embedding and vector store //
CREATE TABLE public.langchain_pg_embedding (
collection_id uuid NULL,
embedding public.vector NULL,
"document" varchar NULL,
cmetadata jsonb NULL,
custom_id varchar NULL,
"uuid" uuid NOT NULL,
CONSTRAINT langchain_pg_embedding_pkey PRIMARY KEY (uuid)
);
CREATE INDEX langchain_pg_embedding_hnsw_idx ON public.langchain_pg_embedding USING hnsw (embedding vector_l2_ops) WITH (ef_construction='200', m='16');

-- Permissions

ALTER TABLE public.langchain_pg_embedding OWNER TO postgres;
GRANT ALL ON TABLE public.langchain_pg_embedding TO postgres;
`
// Query generated the in chat when we specify the Product ID
SELECT langchain_pg_embedding.collection_id AS langchain_pg_embedding_collection_id, langchain_pg_embedding.embedding AS langchain_pg_embedding_embedding,
langchain_pg_embedding.document AS langchain_pg_embedding_document,
langchain_pg_embedding.cmetadata AS langchain_pg_embedding_cmetadata,
langchain_pg_embedding.custom_id AS langchain_pg_embedding_custom_id,
langchain_pg_embedding.uuid AS langchain_pg_embedding_uuid,
langchain_pg_embedding.embedding <=> '[-0.02035035379230976,-0.008137090131640434,-0.14020822942256927,-0.03820028156042099,0.06551223248243332, ...]' AS distance
FROM langchain_pg_embedding JOIN langchain_pg_collection ON langchain_pg_embedding.collection_id = langchain_pg_collection.uuid
WHERE langchain_pg_embedding.collection_id = '1d3628b0-6de2-4a9e-b64f-1000101001'::uuid::UUID
ORDER BY distance ASC
LIMIT 10

``

[dosubot[bot]]

Hey there, @anjay-gupta! 👋 I'm here to help you with any bugs, questions, or contributions. Let's tackle this challenge together!

To address the issue of retrieving specific documents based on a product ID within a JSON object while using embeddings for similarity search, you can enhance your query by focusing on the JSON metadata. Here's a step-by-step approach:

1. Ensure JSON Metadata is Indexed: Make sure that the cmetadata column, which contains your JSON objects, is properly indexed to allow efficient querying. You can create a GIN index on the cmetadata column to improve performance:

   CREATE INDEX cmetadata_gin_idx ON public.langchain_pg_embedding USING gin (cmetadata);

2. Query JSON Metadata: Modify your query to filter based on the JSON metadata. You can use PostgreSQL's JSONB operators to search for specific keys and values within the cmetadata column. For example, if you want to filter by a specific product ID within the JSON, you can use the @> operator:

   SELECT 
       langchain_pg_embedding.collection_id, 
       langchain_pg_embedding.embedding, 
       langchain_pg_embedding.document, 
       langchain_pg_embedding.cmetadata, 
       langchain_pg_embedding.custom_id, 
       langchain_pg_embedding.uuid, 
       langchain_pg_embedding.embedding <=> '[-0.02035035379230976,-0.008137090131640434,-0.14020822942256927,-0.03820028156042099,0.06551223248243332, ...]' AS distance 
   FROM 
       langchain_pg_embedding 
   WHERE 
       langchain_pg_embedding.cmetadata @> '{"product_id": "your_product_id"}'
   ORDER BY 
       distance ASC 
   LIMIT 10;

   Replace "your_product_id" with the actual product ID you are searching for.

3. Combine with Similarity Search: Ensure that your query combines both the JSON metadata filtering and the similarity search. The above query does this by filtering first on the JSON metadata and then ordering by the similarity distance.

By following these steps, you should be able to retrieve documents that match the specific product ID while still leveraging the power of embeddings for similarity search.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[anjay-gupta]

How do we know the we have to search in product_id , since input is plan text and user can type anything and how to we include it in component as below , In above i shared the raw sql which is generated from pgvector component.

question - since i have created the embedding for JSON as text and text contains the ID , but still the below search does not return as similarity search

def search_documents(self) -> list[Data]:
vector_store = self.build_vector_store()

    if self.search_query and isinstance(self.search_query, str) and self.search_query.strip():
        docs = vector_store.similarity_search(
            query=self.search_query,
            k=self.number_of_results,   
        
        )
        
        # docs = vector_store.similarity_search_by_vector(
        #    query=self.search_query,
        #    k=self.number_of_results,   
           

        data = docs_to_data(docs)
        self.status = data
        return data
    return []

[dosubot[bot]]

To address the issue of determining which field to search in, such as product_id, when the input is plain text, you can consider the following approach:

1. Natural Language Processing (NLP) for Intent Recognition: Use NLP techniques to analyze the user's query and identify if it contains specific identifiers like product_id. This can be done using named entity recognition (NER) or keyword extraction to detect if the query includes terms that match known fields in your JSON metadata.

2. Preprocessing the Query: Before performing the similarity search, preprocess the query to extract potential identifiers. For example, if the query contains a recognizable pattern or keyword that matches product_id, you can adjust your search logic to prioritize this field.

3. Hybrid Search Approach: Implement a hybrid search that combines both metadata filtering and similarity search. If the query is identified to contain a specific ID, you can first filter the documents using JSON metadata (as shown in the previous SQL example) and then apply the similarity search on the filtered results.

4. Component Integration: In your component, you can integrate this logic by adding a preprocessing step before calling the similarity_search method. Here's a conceptual example:

   def search_documents(self) -> list[Data]:
       vector_store = self.build_vector_store()
   
       if self.search_query and isinstance(self.search_query, str) and self.search_query.strip():
           # Preprocess the query to check for specific identifiers
           product_id = extract_product_id(self.search_query)  # Implement this function
   
           if product_id:
               # Perform metadata filtering if a product_id is found
               docs = vector_store.filter_by_metadata(product_id=product_id)
           else:
               # Perform regular similarity search
               docs = vector_store.similarity_search(
                   query=self.search_query,
                   k=self.number_of_results,
               )
   
           data = docs_to_data(docs)
           self.status = data
           return data
       return []

By implementing these steps, you can enhance your search functionality to better handle queries that may contain specific identifiers like product_id.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[anjay-gupta]

In the given example , below are the vector table structure and where document -> represent the josn text and having all the property including id , name , multiple address[] etc and also storing in cmetadata -> jasonb

CREATE TABLE public.langchain_pg_embedding (
collection_id uuid NULL,
embedding public.vector NULL,
"document" varchar NULL,
cmetadata jsonb NULL,
custom_id varchar NULL,
"uuid" uuid NOT NULL,
CONSTRAINT langchain_pg_embedding_pkey PRIMARY KEY (uuid)
);

User ask - Please share the product details for product - P0001
Response - unable to find the matching details
User ask - P001
Response - Unfortunately, I don't have enough information to provide a meaningful answer to this question. The text provided appears to be a JSON object representing a product , but it does not contain any relevant information about what "P0001" refers to.

Below are the PG vector component query

if self.search_query and isinstance(self.search_query, str) and self.search_query.strip():
docs = vector_store.similarity_search(
query=self.search_query,
k=self.number_of_results,

        )

Ask is - if we provide the text including the exact ID which is present at text document and json b as metadata , still it is not able to find , Please help where it needs to improve or the best approach would be. Please share the complete example if you have.

I can not add the filter i guess because user may ask question about find based on product location - like give me the product which as the NY city etc , so search should be very closer to city - just an example

Looking forward to some good suggestion and pin pointed answers

[dosubot[bot]]

To improve the search functionality when dealing with JSON text and metadata, consider the following approach:

1. Enhance Query Preprocessing: Implement a preprocessing step to identify specific fields like product IDs or locations in the user's query. Use NLP techniques to extract these identifiers and adjust the search logic accordingly.

2. Hybrid Search Strategy: Combine metadata filtering with similarity search. If a specific identifier like a product ID is detected, first filter the documents using JSON metadata. Then, apply similarity search on the filtered results to ensure relevance.

3. Indexing JSON Metadata: Ensure that the JSON metadata (cmetadata) is properly indexed. This can be done using a GIN index to improve query performance when filtering based on JSON fields.

4. Example Implementation:

   • Preprocess the query to extract potential identifiers.
   • Use metadata filtering if an identifier is found.
   • Perform similarity search on the filtered results.

Here's a conceptual example:

def search_documents(self) -> list[Data]:
    vector_store = self.build_vector_store()

    if self.search_query and isinstance(self.search_query, str) and self.search_query.strip():
        # Preprocess the query to check for specific identifiers
        product_id = extract_product_id(self.search_query)  # Implement this function
        location = extract_location(self.search_query)  # Implement this function

        if product_id:
            # Perform metadata filtering if a product_id is found
            docs = vector_store.filter_by_metadata(product_id=product_id)
        elif location:
            # Perform metadata filtering if a location is found
            docs = vector_store.filter_by_metadata(location=location)
        else:
            # Perform regular similarity search
            docs = vector_store.similarity_search(
                query=self.search_query,
                k=self.number_of_results,
            )

        data = docs_to_data(docs)
        self.status = data
        return data
    return []

5. Considerations for Location-Based Queries: For queries based on locations, ensure that your metadata includes location information and that your preprocessing step can identify and extract location names from the query.

By implementing these strategies, you can enhance the search functionality to better handle queries with specific identifiers and improve the overall accuracy of the search results.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}