Using a UUID from JSON Field

Get Help
1

I have a table which uses a metadata column of type jsonb. I was pleased to see that metabase parses that field's keys and values automatically and that you can manipulate the datatype.

I want to use metadata.some_id, one of the keys from the jsonb object, as a foreign key of event_session.id. So I did this in the admin panel:

43%20PM
43%20PM2142×402 69.8 KB

The problem is that event_session.id is (and needs to remain) a uuid and metabase will only parse metadata.some_id as text, making it impossible to actually use it as a foreign key because of a type mismatch.

I suppose can write a custom sql query and turn it into a model and use that model in my queries - is this the intended solution for a problem like this?

Unless I'm missing something, I see a couple possible solutions:
-- Allow me to change the datatype of values parsed from json objects to be uuid
-- Support some form of type casting in the notebook editor, maybe as a part of the custom expressions

2

Hi @wj4
How do you define the format of a JSON attribute to be UUID? You can't, from what I know, so it's just detected as a string.
There's currently no manual casting option:
https://github.com/metabase/metabase/issues/18974 - upvote by clicking :+1: on the first post
You would have to create a View in your database where you ETL your data with correct casting.

1 Like
3

I just mean in postgres, we'd just cast it like so: (metadata ->> 'some_id')::uuid. I can do that to create a model, but it would be nice to have native support because there are restrictions in what can be done with models and saved questions. Views are a bit annoying for the application I have in mind, but we will of course go ahead with that in the meantime if metabase can't yet provide these capabilities.

I will go upvote that for now.