Langchain SQL Agent - Difference in sql_db_query_checker and sql_db_query

Question

{ "10": { "quantity": "10", ..... } }

Invoking: sql_db_query_checker with `{'query': "SELECT JSON_UNQUOTE(JSON_EXTRACT(point_of_sales, '$.*.quantity')) AS quantity FROM stock WHERE store = 'STORE_NAME'

Whereas below is the actual query gets executed resulted [(None,)]

Invoking: sql_db_query with `{'query': "SELECT JSON_UNQUOTE(JSON_EXTRACT(point_of_sales, '$.[*].quantity')) AS quantity FROM stock WHERE store = 'STORE_NAME'

What is the reason actual executed query ('$..' vs '$.[].) generated differently? Please help to me fix.

few_shot_prompt = FewShotPromptTemplate(
    example_selector = example_selector,
    example_prompt=PromptTemplate.from_template(
        "User input: {input}\nSQL query: {query}"
    ),
    input_variables=["input", "dialect", "top_k"],
    prefix=system_prefix,
    suffix=""
)

full_prompt = ChatPromptTemplate.from_messages(
    [
        SystemMessagePromptTemplate(prompt=few_shot_prompt),
        ("human", "{input}"),
        MessagesPlaceholder("agent_scratchpad"),
    ]
)

agent_with_few_shots = create_sql_agent(
    llm=llm,
    db=db,
    prompt=full_prompt,
    verbose=True,
    agent_type="tool-calling",
    stream_runnable = False,
    max_iterations=5,
    agent_executor_kwargs = {"return_intermediate_steps": True},
    top_k = 2
)


agent_with_few_shots.invoke({
    "input": "list all the quantities in point of sale data of the STOCK of STORE_NAME"})`enter code here`

Answer 1

capture the log from response and parse it for your tool. then use that SQL to execute

response = agent_with_few_shots.invoke(...)

queries = []
for (log, output) in response["intermediate_steps"]:
    if log.tool == 'sql_db_query_checker':
        queries.append(log.tool_input)