Convert to Double error and how to prevent it

Hello everyone,

Mysql version: 5.7.12
Metabase version : 0.45.1

My question is a bit related to an older question : here
The solution of @flamber was perfect to prevent error on global query, but now it is more tricky

I have some important table with JSON columns
Some data in these json are float, I would like to make some question and graph based on specific keys without doing pure sql. (basically for my colleagues)

The problem is that metabase is converting those "columns" from the json as DOUBLE, but it does not exist as conversion type on mysql based on my tests and my reading, so the request crash, If I change to decimal(10,5) for example it works.
First question : why double ?

Then if I change manually the request to omit the automatic conversion in double in sql I cannot create any graph because it is not considered as a number., so I need to convert manually to decimal in each request.

Second question : is there a way to change this automatic conversion ? or should I use a model or create view to make it simpler for the others users that do not know sql ?

Thanks I hope it is clear. Maybe I am missing something in the configuration of Database, I already changed the semantic type but I did not notice any changes on the conversion.

Thanks for the help

have a nice day !

Why don’t you make a Metabase model unnesting only the json columns you need so your users can use that model to make their own questions? Another option would be to do a view in the database for the same

Hi @Luiggi
It is what I was thinking but I am not sure it is what I need. What I understand about the models is that it is an sql query already filtered and tagged with metadata to be reused more easily in metabase, for questions for example.

But, here I dont want to pre-filter the table to remove lines.
it is a data table with ~25 millions lines.
I just want to make some field in JSON easily accessible and with a good type.

I don't think it is what model is used for. Because basically I want to access to all the lines in the table, the filters will come from the question created by my users.

thanks, for your help

unfortunately if the JSON is parsed as Double, it's a bit difficult to force Metabase to see it as another type

Yeah :confused:
it is part of my first interrogation. Why Double it is not a valid conversion type for mysql.

Anyway, should I try a view for my situation ?
Do you confirm the model is not what for what I want to achieve ?


A view or a model is the same, try with what’s most comfortable to you and your team

Hi @Luiggi
I have been able to do what I wanted with a model. I think it was not clear for me but now I got it and it works fine.
Thanks for the help. :pray: