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

Update Jsonb Data in PostgreSQL

I have table with Jsonb field on it and need to update data of jsonb fields.

Data stored like below:

[{"DOB": "04-May-2006", "Code": "2009585", "Class" : "I - Noon"}]

I need to Replace Class Key from ‘Noon’ to ‘NS’.

I have also check out existing answer but that all are work for data with single object like below

{"DOB": "04-May-2006", "Code": "2009585", "Class" : "I - Noon"}

I need to update in with multiple object []

here is fiddle for the same to try

https://dbfiddle.uk/zyDBkmB7

Tags:

3

Answers


  1. 0

    Use jsonb_set

    UPDATE content
SET "UserDetails" = jsonb_set("UserDetails", '{0, Class}', '"I - NS"')
WHERE id = 0;

    See: https://dbfiddle.uk/JJu4oxx8

    You may need this:

    WHERE "UserDetails" -> 0 ->> 'Class' = 'I - Noon';
    Login or Signup to reply.
  2. 0

    Use a subquery to extract all elements from the array, change the content and create a new fresh array:

    UPDATE content
SET "UserDetails" = 
  (
   SELECT jsonb_agg(jsonb_set(d, '{Class}', '"I - NS"'))
   FROM   jsonb_array_elements("UserDetails") d
   )
WHERE id = 0;
    Login or Signup to reply.
  3. 0

    You can use jsonb_set and REPLACE to update your object :

    UPDATE content
SET UserDetails = jsonb_set(UserDetails::jsonb,
                            '{0,Class}',
                             (REPLACE ((UserDetails->0->'Class')::varchar, 'Noon', 'NS'))::jsonb
                           )
WHERE id = 0;

    If you have more than one element in your array then use jsonb_array_elements to generate row for each object, and jsonb_agg to groupe those updated objects :

    UPDATE content
SET "UserDetails" = 
  (
   SELECT jsonb_agg(jsonb_set(userDetail, '{Class}', 
                            (REPLACE ((userDetail->'Class')::varchar, 'Noon', 'NS'))::jsonb)
                   )
   FROM   jsonb_array_elements("UserDetails") userDetail
   )
WHERE id = 1;

    Demo here

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

Back To Top
Search