Hi,
I am importing a Wordpress Ecommerce mysql database. For some reason a specific database field was created as string when in fact is a float. With a string field metabase calculate aggregation. Is there a way to change the field data type.
Thanks in advance!
Not at the moment.
We’re working on improving the type system, but for the time being we’re largely at the mercy of your schema.
Has there been any movement on this? For regular database columns, this isn’t an issue. But for the expansion of JSON objects into columns, this is a big issue. For example, if I have a table with a JSON column (called thingy), and each record has an object that looks like {hi: true}, then Metabase will assume that thingy→hi is a boolean. Then, if someone adds a record with {hi:4}, Metabase will no longer be able to display the table data because it runs into a casting error (4 cannot be converted to a bool). A general solution is to redefine the data type of thingy→hi to a type text or comparably castable data type. If this is not possible, is there another approach for handling JSON elements that may not consistently conform to a single data type?
10 year old necro post.
The original post predates cast settings in Table Metadata and the custom fields feature, so the question is no longer relevant. In addition, the OP had nothing to do with JSON, so I don’t understand why you are resurrecting an old post to discuss a completely different topic.
The up and down of JSON is that it is schemaless. If your input data puts data in different types willy-nilly, then you need to clean it up first before handing to Metabase. Casting is the least of your worries. This is true of all BI systems.
(Or use the new Data Studio feature to do the cleaning!)
Thanks - it looks like this is still an issue, and not something that has been addressed in subsequent versions (I am using a fresh Docker image that was downloaded today). I am able to find the data type listed in both the Table Metadata window (via Admin) and in the Data Studio view. In all cases, the data type is fixed and not editable, so the underlying SQL by which Metabase pulls the table data breaks.
To be clear, this is not a problem with the data–in this case, the data are intended to be treated as text object (e.g., the select is intended to be wrapped in a cast to text), but Metabase has (mistakenly) assumed that the data are intended to be booleans because it found some boolean values.
Said another way, it looks to me like Metabase assumes it cannot make an error in determining the data type of a JSON field. That assumption appears to be preventing it from reading the data properly. I’d like to help it by telling it the true data type of these JSON elements, but it is not clear from the docs or from this post that such a thing is possible.
What database is the JSON data stored in? And what sorts of values is Metabase thinking is a boolean? There is no boolean basic type in JSON, so it’d have to be inferring it from the field values, but that sort of thing seems error-prone so I’d be surprised if its actually doing it.
I have some JSON data stored in PostgreSQL and have Unfold JSON enabled for the table, which breaks out the JSON fields into their own columns. Those columns are interpreted as type “text” and can be casted to a limited number of other types. None of them have gotten interpreted as “boolean,” though the input data doesn’t lend itself to that interpretation (no fields with 0 or 1 or “true” and “false” for values).
Once I know the database type I can set up a test to try to reproduce it.