Sum a JSONB text column

Newbie question, I am trying to create a question that is simply the sum of all the order subtotals in an 'orders' table (Postgresql DB). For various reasons the subtotal is in a JSONB field and it is text e.g. "22.84", it is not numeric.

So when trying formulate the question, the field does not come up in the list of fields that can be summed. So I tried a trick found elsewhere in the discussions here to create a custom column that is

[Odata → Subtotal] * 1

But when this is run it generates this error:

ERROR: operator does not exist: text * integer Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 4531

So I tried to cast this using Postgresql syntax and it was not accepted by the editor:

image

Is there any way to do this? I cannot change the DB data format...

Hi @mark2
You cannot use database function in Custom Expressions:
https://www.metabase.com/docs/latest/users-guide/expressions.html

There's currently no casting expression:
https://github.com/metabase/metabase/issues/18974 - upvote by clicking :+1: on the first post

You would have to do the casting in SQL.

Upvote done. I want to use the Question builder so I thought I could workaround this by creating an SQL query to add the casting for the few fields I have this problem with, e.g. something like:

select *, cast(odata->>'subtotal' as float) as subtotal_f from ctgprod.orders

This works and creates a FLOAT field from our text SUBTOTAL that I can aggregate on.

But when I use this SQL in the Question builder, I don't have access to all the JSONB fields in ODATA, the editor treats ODATA as a single field when I (for example) try to build a filter. The editor shows just the basic table columns, but ODATA is a JSONB column and I need to filter and work with stuff inside it:

image

Is there a way to get a result of a custom SQL query to be understood as a JSONB field?

@mark2 Yeah, you would have to explode the JSON object into separate columns, when you are doing things in SQL, because Metabase just receives the results of the SQL but doesn't modify it.

Ouch, there are dozens of fields in that JSONB data that I would like to build questions upon. This is the second time I have 'kicked the tires' of Metabase and found limitations severe enough it discouraged me from implementing for our BI reporting. (The first was lack of support for JSONB at all).

I don't know if maybe I can get our application support team to implement numeric fields where we need them (e.g. have both a text SUBTOTAL and a numeric SUBTOTAL_F). Not sure they are going to go for that...