Divide a value in JSON using postgreSQL

user_19602814 0
Viewed 28

Im relatively new and would like to redenominate some values in my current database. This means going into my jasonb column in my database, selecting a key value and dividing it by a 1000. I know how to select values but update after I have performed a calculation has failed me. My table name is property_calculation and has two columns as follows: * dynamic_fields is my jasonb column

ID dynamic_fields
1 {"totalBaseValue": 4198571.230720645844841865113039602874778211116790771484375,"surfaceAreaValue": 18.108285497586717127660449477843940258026123046875,"assessedAnnualValue": 1801819.534798908603936834409607936611000776616631213755681528709828853607177734375}
2 {"totalBaseValue": 7406547.28939837918763178237213651300407946109771728515625,"surfaceAreaValue": 31.94416993248973568597648409195244312286376953125,"assessedAnnualValue": 9121964.022681592442116216621222042691512210677018401838722638785839080810546875}

I would like to update the dynamic_fields.totalBaseValue by dividing it by 1000 and committing it back as the new value. I have tried the following with no success:

update property_calculation set dynamic_fields = (

     select jsonb_agg(case
              when jsonb_typeof(elem -> 'totalBaseValue') = 'number'
              then jsonb_set(elem, array['totalBaseValue'], to_jsonb((elem ->> 'totalBaseValue')::numeric / 1000))
              else elem
            end)
     from   jsonb_array_elements(dynamic_fields::jsonb) elem)::json;

I get the following error:

ERROR: cannot extract elements from an object

SQL state: 22023

My json column has no zero string or null values.

jsonb postgresql
1 Answers

Move the jsonb_typeof() check into the where clause:

update property_calculation
   set dynamic_fields = 
         jsonb_set(
           dynamic_fields, 
           '{totalBaseValue}', 
           to_jsonb((dynamic_fields->>'totalBaseValue')::numeric / 1000)
         )
 where jsonb_typeof(dynamic_fields->'totalBaseValue') = 'number';

db<>fiddle here

user_138083190
Related