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:
Is there any way to do this? I cannot change the DB data format...
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:
Is there a way to get a result of a custom SQL query to be understood as a JSONB field?
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...