We have a table and it has a JSONB column. Within that column we have an ID field. That ID field is often what looks like a number, but not always. I.e. 1000000000 or thisisanid are both valid. This is because this field specifically is used for 3rd parties to store their IDs.
In metabase it appears its trying to force it to a bigint which is now causing our entire table to not load.
ERROR: invalid input syntax for type bigint: "txnId"
Is the error we get when loading a table for example. How do we fix this? This is totally blocking us from using Metabase fully since now we cant see any of these records that arent numbers
TonyC
April 12, 2024, 11:07am
2
What version are you running? Something is indicating that you have a nested JSON structure, which might hit this issue:
opened 12:09AM - 03 Oct 22 UTC
closed 08:10AM - 01 Aug 23 UTC
Type:Bug
Priority:P2
Administration/Metadata & Sync
.Backend
.Regression
**Describe the bug**
We have a number of JSON fields which can have different t… ypes, the two basic situations where this occurs are:
* they can have different values in our application - eg. something that can be a boolean or an object
* the value has changed over time (eg. an integer has become a float)
It looks like when extracting the data out of the JSON blobs it always try to cast the result as what it detected in the 'original schema'. Even if the actual DB field is set not to cast.
**Logs**
For our table (load_count_cycles), if I try and open it directly I get this page:
If I change the sort I can get this error instead:
Which relates to a value of 'dumped_at` in one of blobs, which has no value of casted:
In the original schema it was detected as an integer, which makes me think it is actually being casted based on the original detected value?
**To Reproduce**
Steps to reproduce the behavior:
We have this issue many of our tables, so presumably it will happen for any mixed type json values in a postgres database.
**Expected behavior**
I expect to be able to see my table.
If it is limited to a single type, I would also be expect to be able to manage this type. It seems to detect one 'at random' (two different DBs have detected different types for the same field).
**Information about your Metabase Installation:**
```json
{
"browser-info": {
"language": "en-NZ",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:105.0) Gecko/20100101 Firefox/105.0",
"vendor": ""
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.16.1+1",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.16.1",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.16.1+1",
"os.name": "Linux",
"os.version": "4.14.287-215.504.amzn2.x86_64",
"user.language": "en",
"user.timezone": "Australia/Sydney"
},
"metabase-info": {
"databases": [
"postgres",
"mysql"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "11.13"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.5.0"
}
},
"run-mode": "prod",
"version": {
"date": "2022-09-29",
"tag": "v0.44.4",
"branch": "release-x.44.x",
"hash": "382d728"
},
"settings": {
"report-timezone": "Australia/Sydney"
}
}
}
```
**Severity**
This is currently blocking our upgrade to 0.44, since many of tables do not work anymore.
:arrow_down: Please click the :+1: reaction instead of leaving a `+1` or `update?` comment
We are running 0.49.2
I see that says closed. Any idea why it'd still be failing or how to get around it besides disabling the JSON unfolding since that's a really important feature for us.
TonyC
April 13, 2024, 5:37am
4
Will need to look into that but one option would be to create a view at DB level which would flatten the json object ... It's also more efficient and gives you more control on what gets extracted