I have a JSON field in my Database witch includes a price if there is one.
I get the values like this
select t.config::jsonb ->> 'price' as price from mytable t
It gives me the following results back (null,empty and a price) as a textformat:
I need to be able to make this field numeric so that i can sum this field later on.
Ive tried to solve it like this:
select ( case when t.config::jsonb ->> 'price' is null then '0.00' when t.config::jsonb ->> 'price' = '' then '0.00' else t.config::jsonb ->> 'price' end)::decimal as price from mytable t
This gives me a numeric(131089,0) back. I want the field to be like numeric(10,2). There must be a other easier way to do this?
coalesce can be very handy in this case.
null in case value is empty and
0.00 in case value is
null, so you might wanna chain both functions like this:
SELECT coalesce(nullif(t.config::jsonb ->> 'price',''),'0.00')::numeric(10,2) as price FROM mytable t;