Canner
Support of jsonb columns
Is your feature request related to a problem? Please describe. I am trying to run Text-To-SQL prompts for my tables that contain jsonb type columns. When i load them, WrenAI gets the columns but it shows Type = UNKNOWN. And it doesnt recognize NOTHING.
Additional context If i prompt "Show me all the data of the table 'table'" or "Show me all data that has a 'column' column", it will print out all the columns except the jsonb/UNKNOWN ones. It wont even recognize the column names.
Outputs: Lets say i have a table called threads and columns: id, created_at, metadata.
Example 1
Prompt: Show me all the data for the top 10 threads
Output: Here is the data for the top 10 threads, ranked by their creation time (most recent first):
Thread ID: 2 Created At: 2025-06-25 14:20:00 UTC
Thread ID: 6 Created At: 2025-06-25 06:52:10 UTC
...
Example 2
Prompt: Show me all the data that has a metadata column
Step-by-Step Explanation
- Understanding the Schema:
The public_threads table contains the following columns: id: A unique identifier for each thread. created_at: The timestamp when the thread was created.
Question Breakdown:
Part 1: You want to know the distribution of threads by their creation date. Specifically, you want the count of threads created each day, ordered by the most recent day. Part 2: You want all the data for the top 10 threads based on the most recent creation date. Part 3: You are asking for data related to a "metadata" column, but the public_threads table does not have a column named "metadata." This part of the request cannot be fulfilled unless clarified. Steps to Analyze:
For Part 1, group the threads by the created_at date (ignoring the time component) and count the number of threads for each day. Then, order the results by the most recent day. For Part 2, retrieve all columns for the top 10 threads based on the most recent created_at timestamp. For Part 3, since there is no "metadata" column in the schema, this part of the request is not applicable. Clarification Needed:
If "metadata" refers to a specific column or data stored elsewhere, please provide more details so we can assist further.
Long story short, i converted all the jsonb columns to json so they will be recognized by WrenAI, but WrenAI still finds a very hard time to generate SQL Queries for the JSON columns and the keys/arrays inside of them.