When scanning tables, Metabase thumbs through JSON objects and promotes those elements to effective columns with names like cname-->keyname. It also assigns a data type to these effective columns based on a sampling of key values. If Metabase does not inspect all the keys, or if subsequent inserted/updated records include values for these keys with incompatible types, Metabase can fail to load the table data because its attempt to cast all values to a single data type fails. For example, if the first few records in a table have cname-->keyname=true, Metabase will assume that cname-->keyname is always a boolean. Then, when it tries to load cname-->keyname='ranch', the load will fail and Metabase will not be able to display the table data due to an underlying cast error.
Is there a way to correct Metabase's understanding of the true data type for these JSON keys?
Turns out I was wrong, there is a base Boolean type in JSON. It (Metabase and PostgreSQL) accepts either a true or false value in lowercase, unquoted. If you have unquoted true or false values like that then Metabase will interpret them as Boolean.
While waiting for your repro case post to approve, I ran my own test. I created a test table with a jsonb column and inserted one of 2 objects 100 times. The first test used these objects:
{"key": "a", "property": "true"}
{"key": "b", "property": "false"}
The second test used these objects:
{"key": "a", "property": true}
{"key": "b", "property": false}
After loading the table I rescanned the tables & values in Metabase and checked the identified type of propertyin Table Metadata.
The result of the tests was that this will be interpreted as a boolean:
{"key": "a", "property": true}
This will be interpreted as text:
{"key": "b", "property": "false"}
If this isn’t what you want then you will need to either transform the JSON to remove the boolean values or create a derived column that re-interprets the JSON value the way you want.
And as I said in the other thread, its up to you to make sure the data is consistent. If you have mixed types in a column then all bets are off. Use Transforms or an ETL process to ”quote” the values and thus force their interpretation as text. You can always cast text to other things using custom expressions in Metabase.