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

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.

CodePudding user response：

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