3

I am trying to update the jsonb column media, with two keys i.e default** is of type jsonb and image_set is an array of jsonb.

Is there is solution for single select update statement to update both keys. test_media table

id |                                                                         media                                                                         | name  
----+-------------------------------------------------------------------------------------------------------------------------------------------------------+-------
  2 | {"default": {"w1": "fff", "w2": "aaa", "w3": "ddd"}, "image_set": [{"w1": "fff", "w2": "aaa", "w3": "ddd"}, {"w1": "bbb", "w2": "rrr", "w3": "vvv"}]} | pooja

Updating image-set

Update test_media 
  set media = media #- ('{image_set,'||(select pos-1 from test_media, jsonb_array_elements(media->'image_set') with ordinality arr(value, pos) where name='pooja' and value->>'w1'='fff') 
                       || '}')::text[] 
                       || jsonb_set(media, '{default}', '{"w1": "bbb", "w2": "rrr", "w3": "vvv"}' )
where name='pooja';

Here, based on delete, I want to update the default and image_set together depends on different condition.default jsonb value is from image_set array. I tried using case statement but it is not working fine. Different conditions of delete are :

  1. When the jsonb value, i want to delete is in default as well as in image_set, it should delete that value from image set and update the default with other value from image set.
  2. If not so, it won't update default, just the image_set value will be deleted.
  3. If the value of array i.e image_set is 1 , then the media='{}' updated to null json.

Tried two things updating separately, default and image_set.

Update test_media 
   set media =  ( CASE 
                   WHEN jsonb_array_length(media->'image_set')::int > 1 
                      THEN (Select media #- ('{image_set,'||(select pos-1 from test_media , jsonb_array_elements(media->'image_set') with ordinality arr(value, pos) where name='pooja' and value->>'w1'='fff') || '}')::text[]) 
                   ELSE media = '{}' 
                   END IF
                ) 
where name='pooja';

Here, i got the error:CASE types boolean and jsonb cannot be matched Secondly,

update test_media  
   set media = jsonb_set(media, '{default}', (select  from (select CASE WHEN media->'default'->>'w1'='fff' AND jsonb_array_length(media->'image_set')::int >0 THEN (select media->'image_set'->0 from test_media where name='pooja' ) WHEN  media->'default'->>'w1'='fff' AND jsonb_array_length(media->'image_set')::int = 0 THEN (select media - 'default' from test_media where name = 'pooja') END) As Sub), True) 
where name='pooja';

I would be thankful if i get support for case statement using select update. Hope for a positive response. Thanks.

updating multiple jsonb column

update-multiple-values-in-a-jsonb-data

D.Ojha
  • 121
  • 1
  • 1
  • 6

0 Answers0