René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"need scalar queries to work - stuck - help please","articleBody":"@pamelafox \nI have been trying for a few days to adapt the system to work with my data - a single table of items - let's say they are cars\n\nI have modified following the guidance in customizing_data.md and the other data branch. I have looked through other branches, am just not having any luck - example queries, few shot, answer.txt, query.txt and searcher examples below.\n\nI am really not sure where the structured logic of things like queries and creating a ton of functions to handle the possible query types - and how much to rely on the prompts to do things. For instance, when I ask for a list of something I tend to get 3 full car descriptions. I realize the 3 is probably being set in the developer settings.\n\nGuidance here would be greatly appreciated! I realize this project is 5 months old and as an ex-microsoftee, I know you are moving ahead at full speed, but I have found this template extremely helpful in accelerating my client's embracing AI and Azure for all AI related workloads. (I fully understand this is not intended for production use, but it has many best practices built into the solution.\n\nSpecifically, I would like to know:\n\nwhat is the extent of the power of the prompts, what aspects of system behavior should I lean on them for (see below)\nhow much should I be modifying QueryRewriter and PostgresSearcher, how generic should those changes be (see below)\nexactly what components in the FrontEnd need to be modified to handle different response types - say lists, citations under each response in a field that toggles visibility like in a file browser.\nExamples of what I have been trying - all at the same time - app runs and deploys file rarely crashes on a query. \n\ntrying to do is get support for queries like:\n\nQ: how many 1989 Ford's do we have? I want the answer to be something like\n\nA: we have 12 Ford cars in inventory \n- follow up question would you like to see a list of all of the?\n\nQ: Which is the range of reliability ratings?\nA: The cars in inventory range from 3 to 4.5 safety ratings\n- follow up would you like to see a list of them sorted by rating?\n\nQ: of the different manufacturers, which is the most popular?\nA: the Subaru Outback is the most popular, using the formula of the sum of the count of the number of votes for each star rating (1-5) /the number of votes \nQ: what adjective word occurs the most in the comments section\nA: happy was mentioned in 20 out of 120 reviews\n\nI have tried modified the following:\n\nQueryRewriter Updates - a bunch like this\n\n                    \"select\": {\n                        \"type\": \"object\",\n                        \"description\": \"Specify scalar operations like COUNT, AVG, etc.\",\n                        \"properties\": {\n                            \"operation\": {\n                                \"type\": \"string\",\n                                \"enum\": [\"COUNT\", \"AVG\", \"SUM\", \"MIN\", \"MAX\"],\n                                \"description\": \"The scalar operation to perform\",\n                            },\n                            \"field\": {\n                                \"type\": \"string\",\n                                \"description\": \"The field to operate on, e.g. '*' for COUNT(*) or 'metrics-\u003e\u003e'Views'' for views\",\n                            },\n                        },\n                        \"required\": [\"operation\", \"field\"],\n                    },\nPostgresSearcher Updates:\n\nImplement scalar operations for numeric metrics\nAdd type coercion for mixed string/number metrics\nExample query: metrics-\u003e\u003e'Views'::numeric for consistent numeric handling\nQueryRewriter Updates\n\nAdd metric name patterns to query generation\nHandle numeric comparisons in natural language\nUpdate few-shot with entries like\n{\n        \"role\": \"user\",\n        \"content\": \"how many Ford cars do we have?\"\n    },\n    {\n        \"role\": \"assistant\",\n        \"tool_calls\": [{\n            \"function\": {\n                \"name\": \"search_database\",\n                \"arguments\": {\n                    \"return_scalar\": {\n                        \"type\": \"count\",\n                        \"expression\": \"*\"\n                    },\n                    \"filters\": {\n                        \"Brand\": \"ig\",\n                        \"category\": \"cars\"\n                    }\n                }\n            }\n        }]\n    }\nAnswers.txt\n\nAssistant helps users find relevant cars and trucks.\nRespond as if you are a knowledgeable content curator. Do NOT respond with tables.\nAnswer ONLY with the content details listed in the sources.\nIf there isn't enough information below, say you don't know.\nDo not generate answers that don't use the sources below.\n\nFor scalar query results i.e. how many, highest rating, (like COUNT, AVG, etc):\n- Look for the metrics.scalar_result field which contains the actual numeric value\n- Present the value clearly and directly in a complete sentence\n- Include the [scalar_result] citation\nquery.txt\n\nYou are a query generator that helps translate natural language questions into search queries or scalar operations.\n\nFor scalar operations (like counting, averaging, finding max/min), generate a scalar operation with:\n\nselect_expr: The expression to compute (e.g. MAX, COUNT, AVG)\ngroup_by: Optional list of columns to group by\norder_by: Optional list of columns to order by\nfilters: List of filters to apply\nExamples:\n\n\"How many Ford cars are there?\" -\u003e COUNT(*) with brand = 'ford' and category='car'\n\"What vehicle has the most likes?\" -\u003e MAX((metrics-\u003e\u003e'Likes')::numeric)\n\"Average price of Subarus?\" -\u003e AVG((metrics-\u003e\u003e'price')::numeric) with brand = 'subaru'\nFor regular search queries, generate a search query based on the conversation.\nIf the question is not in English, translate it to English before generating the query.\n\nIf you cannot generate a search query, return the original user question.\n\nBelow is a history of the conversation so far, and a new question asked by the user that needs to be answered by searching database rows.\nYou have access to an Azure PostgreSQL database with a posts table that has the following columns:\n\nText fields: model, brand, category, description\nArrays: reviews, stars, features\nJSON fields: ownership_records (containing key value pairs of owner_name, puchase_date, Purchase_location, sale_date, sale_location )\nFor questions asking about counts, averages, sums, or other aggregate metrics:\n\nUse the \"select\" operation with COUNT, AVG, SUM, etc.\nExample: \"how many Subaru wagons? are in stock\" -\u003e use COUNT(*) with brand=\"subaru\" and category= \"wagon\"\nExample: \"average rating of fords?\" -\u003e use AVG(metrics-\u003e\u003e'rating') with brand=\"ford\"\nYou can combine with filters like channel, date ranges, etc.\n","author":{"url":"https://github.com/nickknyc","@type":"Person","name":"nickknyc"},"datePublished":"2025-02-04T18:09:53.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":2},"url":"https://github.com/160/rag-postgres-openai-python/issues/160"}