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 :
- 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.
- If not so, it won't update default, just the image_set value will be deleted.
- 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.