I have a database having columns id and profile fields where profile fields is of the type jsonb

In profile fields the key 2 stands for Date of Birth .

Unfortunately many fields have values in format yyyy/mm/dd .

I would like to change all the Date of birth values in dd/mm/yyyy format.

The Expected results is like this

I tried the update the update statement but i am stuck how can i apply in multiple values ? What will go inside where statement. And how to access the key 2 profile_fields->"2" is not in Update statement.

Thank you.

CodePudding user response：

You can perform a regexp_replace to mutate the datetime format when invalid ones are matched:

update tbl t set profile_fields = (select jsonb_object_agg(t1.key, regexp_replace(
     case when regexp_match(t1.value::text, '\d{4}\-\d{2}-\d{2}') is null 
         then t1.value::text 
         else regexp_replace(t1.value::text, '(\d{4})\-(\d{2})\-(\d{2})', '\3-\2-\1') end, '"', '', 'g')) 
   from jsonb_each(t.profile_fields) t1) 

See fiddle.

CodePudding user response：

Here it is using the || operator and regexp_replace to reshape the date format.

update the_table
set profile_fields = profile_fields || jsonb_build_object
 ('2', regexp_replace(profile_fields->>'2','(\d\d\d\d)-(\d\d)-(\d\d)','\3-\2-\1'))
where profile_fields->>'2' ~ '\d\d\d\d-\d\d-\d\d';

DB Fiddle
Please note that this approach - using the || operator - only works for the first-level keys or for "flat" JSON.