Get Metadata from NLSQLRetriever.retrieve

[sambitlnt]

When I am running NLSQLRetriever.retrieve the result is returned in below format. Is there a way to just get the Metadata?

Node ID: 7c0e4c94-c9a6-4917-aa3f-e3b3f4cbcd5c
Similarity: None
Text:
Metadata: {'city_name': 'Tokyo', 'population': 13960000}

Node ID: 3c1d1caa-cec2-451e-8fd1-adc944e1d050
Similarity: None
Text:
Metadata: {'city_name': 'Seoul', 'population': 9776000}

[dosubot[bot]]

Yes, there is a way to get just the metadata when running NLSQLRetriever.retrieve. You can set the return_raw parameter to True when initializing the NLSQLRetriever. This will ensure that only the SQL query and metadata are returned, without executing the SQL query.

Here is an example of how to initialize NLSQLRetriever with return_raw set to True:

from llama_index.core.retrievers import NLSQLRetriever
from llama_index.core import SQLDatabase

# Assuming you have an instance of SQLDatabase
sql_database = SQLDatabase(...)

# Initialize NLSQLRetriever with return_raw set to True
nl_sql_retriever = NLSQLRetriever(
    sql_database=sql_database, 
    tables=["city_stats"], 
    return_raw=True
)

# Now you can use nl_sql_retriever to retrieve just the metadata
results = nl_sql_retriever.retrieve(
    "Return the top 5 cities (along with their populations) with the highest population."
)

# NOTE: all the content is in the metadata
for n in results:
    display_source_node(n, show_source_metadata=True)

In this setup, the metadata will contain the information you are interested in ^[1].

To continue talking to Dosu, mention @dosu.

[sambitlnt]

This just displays the Metadata with a host of other info. I want just the metadata. Nothing else. I want the metadata in a variable. How do I achieve that?

[dosubot[bot]]

To retrieve just the metadata and store it in a variable when using NLSQLRetriever.retrieve, you can use the retrieve_with_metadata method. Here is an example of how you can do this:

# Assuming you have an instance of NLSQLRetriever named `retriever`
query = "Return the top 5 cities (along with their populations) with the highest population."
retrieved_nodes, metadata = retriever.retrieve_with_metadata(query)

# Store the metadata in a variable
metadata_variable = metadata

The retrieve_with_metadata method returns a tuple where the second element is the metadata. You can store this metadata in a variable as shown above. The metadata will include details such as the SQL query used and other relevant information ^[1][2][3].

To continue talking to Dosu, mention @dosu.