Question 113960 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Updating Values of JSONB with the help of current value

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

id profile_fields
101 {"1":"Chess" , "2":"08-02-2001"}
102 {"1":"Hockey" , "2":"1996-06-09"}

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

header 1 header 2
101 {"1":"Chess" , "2":"08-02-2001"}
102 {"1":"Hockey" , "2":"09-06-1996"}

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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','(dddd)-(dd)-(dd)','3-2-1'))
where profile_fields->>'2' ~ 'dddd-dd-dd';

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search