Fetching rasa conversation from json

How to I see my data of chatbot and user conversation that is being stored in dB and it is being stored in json, so how do I read or see the actual data being stored @nik202

@ChrisRahme

You’re looking for the Tracker Store.

By default, the conversations are stored in memory. You may see files such as rasa.db and events.db in the root of your chatbot as PostgreSQL format.

If you want to change that, you will need to specify the desired tracker store in endpoints.yml. It can be SQL (dialects: MySQL, PostgreSQL, Oracle,and more), Redis, MongoDB, and more.

If you want JSON, you will need to write your own Custom Tracker Store.

@syednoormujassum Try this:

Run this SQL query command and below query should be treat as one, do try this and see the output:

Migrate TEXT data type to JSON

ALTER TABLE table_name

ALTER COLUMN column_name TYPE JSON USING column_name::JSON;

Migrate TEXT data type to JSONB

ALTER TABLE table_name

ALTER COLUMN column_name TYPE JSONB USING column_name::JSONB;

Note: Do check the table properties that It change to text to JSON or JSONB? and you can also export the data into excel or csv

Drawback:

You need to run this SQL query every time to convert Text β†’ JSON or JSONB and then you can write python code to fetch the data from SQL column for bot/user conversation (data) which is a metadata column i.e Key-value pair in JSON format.

Then you will need to write the SQL query in action.py to fetch the desired data which was stored in SQL Table/Schema and save as slot.

Note: This suggestion is used for Rasa open source and I don’t know about Rasa-X, for more Chris will be the right person to guide you about query fetching from the database. I hope this will help you.

thank you @ChrisRahme

@nik202 was able to fetch using workbench itself, thank you

@syednoormujassum query?share did you changed the data column to json or it was only text?

query was bit different and it was in json data type

@syednoormujassum Do share when you got time. it good you able to fetch the data.

sure

SELECT distinct(json_extract(column name, β€˜$.text’)) as name_you_like #FROM database.tablename ;

@syednoormujassum Thanks for sharing the solution, and grafana visualisation is not related to this post, for that you need to create new thread.

@nik202 the value storing under timestamp is a float value but how do we get the time format value?

@syednoormujassum the way you mentioned in the query syntax,I guess ? I not used that query for fetching my JSON database value.